PDA

View Full Version : Macro Excel



BoutFam
12-11-2005, 08:43 AM
I am trying to create a macro to go to the next blank cell in a column and copy the cell above. I have entry level knowledge of macro and none of Visual Basic. I just have a spreadsheet that I want to insert (using a macro) a copy of the cell above for certain cells in a row ie the ones that have functions and formulas. It is a spreadsheet I have linked to a mail merge document for creating invoices, and when I want to add an invoice I need the formulas etc to be entered into certain cells. Hope this makes sense. I can record the macro to do this but it is for specific cells and i want it to be always the next blank one.
Thanks

Alpha
12-11-2005, 09:10 AM
After you click record macro a toolbar appears with two buttons; stop and relative references. If you click the relative references button all references to cells from then on will be relative and not absolute.

Parry
12-11-2005, 09:17 AM
Welcome to the board. The END statement enables you to navigate up,down or accross from a particular starting point and will end at the next cell that has data in it (or the first,last row etc).

If your wanting to find the last used row in column A, then you can use Range("A65536").End(Xlup).Row. This is the same as you selecting cell A65536 then using the keyboard shortcut CTRL+UP_ARROW.

The following is an example to copy the last used cell in a row then copy it to the blank cell underneath


Sub Example()
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
Cells(LastRow, 1).Copy Cells(LastRow + 1, 1)
End Sub

BoutFam
12-11-2005, 09:17 AM
Thank you so much. I did it and it worked !!!!! :thumbs:

BoutFam
12-11-2005, 09:39 AM
Thanks I will try that as well :)