Cell referencing in Macros

[Bern Notice]

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?

 

[JLGWhiz]

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.

 

[Mike H]

Hi,

Find the last row in column A with this

lastrow = Sheets("Sheet1").Cells(Cells.Rows.Count, "A").End(xlUp).Row


and either of these to paste into the cell below that.

Range("A" & lastrow + 1).PasteSpecial
Cells(lastrow + 1, 1).PasteSpecial
Cells(lastrow + 1, "A").PasteSpecial


Mike

 

[Bern Notice]

Thanks Mike,
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

 

[Bern Notice]

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]

I made some modifications to your code to get rid of the
select. It could be streamlined even more but you might
not recognize it, so I just did the bare essentials. One
thing about using the lastRow variable is that if you change
sheets the last row has to be declared again. An alternative
method is to declare different variables for each sheet:
Example:

Dim lr1 As Long, lr2 As Long
lr1 = Sheets("Medical 2009").Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Sheets("Quickbooks Import").Cells(Rows.Count, 1).End(xlUp).Row

Now you can use the appropriate variable with its corresponding sheet
and it will give you the correct row number, until you add or delete
a row further down in you code, at which point you have invalidated
the last row variable. It will still be holding the original row
number. You need to play with this concept for a while until you
understand how it works. The modified code is below and I did
not test it, so you should.


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

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

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.