Vba help

QuestionEdit

I have two tabs (M1) and (M2), M2 has all my data on it, the M1 is a table that is linked to Access. M2 has Vlookup formulas that go down to row 200 (because data will be imputed and deleted weekly i will never know how many rows down the data will actually go) i need to write a macro that will copy down to the last row that has actual data in it (excluding the formulas) then past the copied information into M1. If I can get the macro to run by itself say every thursday that would also be very helpful.

Any help you can provide will be much appreciated

AnswerEdit

May I know if you are proficient in VBA? For the setup, a macro can be written to copy the vlookup formula to the last row that corresponds to the last row of data in M1.

Windows Task Scheduler can be used to automate the process every thursday. Send me your file and I'll take a look at it.

QuestionEdit

I am new to macro's and was wondering if you can help me? My macro seems simply but not sure how to solve it. All I would like to do is whenever i double click on a row a form would pop up with all the field headings across row 1...for example...if i click on row 20 in my table a form would pop up with all the column headings (assuming that A1:Z1 are the column headings) It would make it easier for me to enter the data in that sort of format.

Is this possible?

AnswerEdit

Manish,

What you need to do is select row 2. then in Excel 2003 and earlier go to Windows and select Freeze Panes.

this will keep row1 always visible while you scroll. To turn it off, just repeat the action of going to the windows menu and selecting Freeze Panes.

in Excel 2007, this is found in the view Tab. Just look toward the right of the tab (just past half way (in the windows section)) and you should see the Freeze Panes button/Dropdown.

Trying to make a form that would line up with your data would be extremely difficult - I assume that is what you want the form to do - to display column headers above the data. I think Freeze Frames is a far superior solution for this and is what the developers ofintended.

If you selected say C3 before you did Freeze Panes, then rows 2 and columns A:B would be frozen and you would scroll under them. So the area frozen is to the left and above the current selection.