Thanks JL,
The lastrow function has helped. The bulk of my macro now works. I am
having problems inserting the data from one sheet to the bottom of the list
on the other sheet. I have some total formulas that I need to shift down so
they remain at the bottom of my list. I'm having problems getting past the
error that requires the paste range to be the same size as the copied cells.
If I do it by hand, I can highlight the rows all the way across (use the
mouse and click on the row numbers to highlight all the way across) and then
use the mouse again to highlight the lastRow+1 all the way across and then
paste and it shifts my total formulas down.

Below is the bottom part of my macro. It's stopping at about the 5th line
down. Any suggestions? Thx

Range("A1:I" & lastRow).Select
Selection.Copy
Sheets("Medical 2009").Select
lastRow = Sheets("Medical 2009").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A227:IV227").Select
Selection.Insert Shift:=xlDown
Range("A232").Select
Selection.End(xlDown).Select
Range("A422").Select
Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=+'Quickbooks Import'!R[lastrow]C"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:E2"), Type:=xlFillDefault
Range("D2:E2").Select
Range("D2:E2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D2").Select
Application.CutCopyMode = False
Sheets("Quickbooks Import").Select
Cells.Select
Range("A1:I" & lastRow + 5).Activate
Selection.ClearContents
Range("A1").Select
Sheets("Medical 2009").Select

"JLGWhiz" wrote:

You can set the last row in a range to a variable:

lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

The above line of code finds the last cell with data
in column A of the active sheet. To use it in a range
variable you would:

Set myRange = Range("A2:A" & lastRow)

If you want to then put something into the next empty
cell you would:

Range("A" & lastRow + 1) = 'Something

Once you have defined the range as myRange you can:

myRange.Select

But I personally try to stay away from using Select
since you and write code without having to select a
range or a cell to do something.


"Bern Notice" wrote:

When writing macros that will highlight and move data around, how do you
highlight the data when it will have different numbers of rows each time you
run the macro. Is there a way to simulate the typing <end<down arrow to go
to the bottom of the range (no matter how big or small it is)?

Also, how do change the reference cell each time you run the macro. For
example, you want to continue to add data to the end of the list which keeps
growing each time you run the macro. I am having problems because my macro
wants to insert the copied data in the same cell reference each time instead
of going to the bottom of the list.

Does this have to be done in visual basic or can you create the macro with
the recorder?