Working with ranges

[DrewBlaha]

Greetings all.
New here, trying to write a macro, something I don't do much but would like to learn.
All I want to do right now is insert a column and add today's date, then copy all of the rows that are being used in the spreadsheet and append them to the end of another spreadsheet.
I recorded a macro as I went through the steps, but now I'm having problems changing the absolute references to the calculated references I need.
So - here is the relevant section of macro I recorded:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Value = Date
Selection.AutoFill Destination:=Range("A2:A9"), Type:=xlFillCopy

Selection.End(xlDown).Select
Rows("2:9").Select
Selection.Copy
Sheets("Cumulative").Select
Selection.End(xlDown).Select
ActiveSheet.Paste


In the example I was working with, the last used row was 9. Now, what's the best way to get my macro to go to the last row, not row 9?

Thank you very much for your help.

 

[bertie]

Hi and welcome to the forum.

Here is the way I approached your problem.
I have assumed your data is in sheet1, edit where highlighted if different.
I find the last row, insert a new column, and populate the new column with the date.

  [color=green]'insert column and dates[/color]
  [color=darkblue]With[/color] Sheets("Sheet1")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    .Columns("A").Insert shift:=xlToRight
    .Range("A2:A" & lr).Value = [color=darkblue]Date[/color]
  [color=darkblue]End[/color] [color=darkblue]With[/color]

I then find the last row on the Cumulative sheet. Adding 1 gives the row to paste into.

  [color=green]'get the last row on the cumulative sheet[/color]
  [color=darkblue]With[/color] Sheets("Cumulative")
    lrCumulative = .Range("A" & .Rows.Count).End(xlUp).Row + 1
  End [color=darkblue]With[/color]

Then I copy and paste everything.

  [color=green]'copy and paste[/color]
  Sheets("Sheet1").Rows("2:" & lr).Copy _
    Destination:=Sheets("Cumulative").Range("A" & lrCumulative)

Putting it all together:
Make a copy of your workbook.
Open it and press Alt+F11 to open the VBA editor.
In the Project Window on the left hand side double click the ThisWorkbook module.
Edit where highlighted if necessary.
Copy and paste the code below.

[color=darkblue]Sub[/color] test()
  [color=darkblue]Dim[/color] lr [color=darkblue]As[/color] [color=darkblue]Long[/color]      [color=green]'last row sheet1[/color]
  [color=darkblue]Dim[/color] lrCumulative [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
  [color=green]'insert column and dates[/color]
  [color=darkblue]With[/color] Sheets("[COLOR="Red"]Sheet1[/COLOR]")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    .Columns("A").Insert shift:=xlToRight
    .Range("A2:A" & lr).Value = [color=darkblue]Date[/color]
  [color=darkblue]End[/color] [color=darkblue]With[/color]
  
  [color=green]'get the last row on the cumulative sheet[/color]
  [color=darkblue]With[/color] Sheets("Cumulative")
    lrCumulative = .Range("A" & .Rows.Count).End(xlUp).Row + 1
  [color=darkblue]End[/color] [color=darkblue]With[/color]
  
  [color=green]'copy and paste[/color]
  Sheets([COLOR="Red"]"Sheet1"[/COLOR]).Rows("2:" & lr).Copy _
    Destination:=Sheets("Cumulative").Range("A" & lrCumulative)

  Application.CutCopyMode = [color=darkblue]False[/color]
End [color=darkblue]Sub[/color]

The code can be run from Excel via the Tools => Macro => Macros menu.

 

[DrewBlaha]

Well, thank you very much!
I got back to work today after a long weekend, and the perfect answer was waiting for me.
Works fine - now I just need to study and figure out why.
There are several parameters there I had not seen before.
Is there a good reference book that you would recommend for this?

 

[bertie]

You can use the VBA help files..
Place the cursor inside the parameter you want to reference and press F1.

This forum has loads of tutorials for Excel and VBA, see this link:
http://www.mrexcel.com/articles.shtml

Anything I don't have to hand I simply search Google, i.e.,
VBA Find last cell
VBA Copy


Hope this helps.
Bertie