1

I am compiling data from multiple worksheets to place on one consolidated sheet. The first sheet is easy because I can paste the entire sheet however after that it becomes tricky as I only need from A5-H### as I no longer need the header. I then need to paste this information at the bottom of the previous paste. I am getting a Range of Object "_Global" failed for the 'Range(lastRow).Select'. The issue is when I look at it in the debugger it is coming up with the correct row number. What am I doing wrong? 

Sheets(1).Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Consolidation Sheet").Select
Range("A1").Select
ActiveSheet.Paste
Sheets(2).Select
Range("A5:A925").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Consolidation Sheet").Select
Range("A5").Select
lastRow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row + 1
Range(lastRow).Select
ActiveSheet.Paste
Greg Herr
  • 37
  • 1
  • 2
  • 8

1 Answers1

3

You need your Range statement in the following form (for example):

Range("A1").Select

So since you've got the row number (lastRow), you just need to add the column reference too:

Range("E" & lastRow).Select

Extra info

Avoid using Select by referring directly to the worksheets/ ranges you want to deal with

Sheets(1).Cells.Copy Destination:=Sheets("Consolidation Sheet").Range("A1")

With Sheets(2)
    .Range(.Range("A5:A925"),.Range("A5:A925").End(xlToRight)).Copy
End With

With Sheets("Consolidation Sheet")
    .Cells(.Rows.Count, "E").End(xlUp).Offset(1,0).PasteSpecial
End With
Community
  • 1
  • 1
CallumDA
  • 12,025
  • 6
  • 30
  • 52
  • 1
    Thank you so much. I had originally tried Range(lastrow, "A") and was clearly so close but also so far away from success. – Greg Herr Mar 28 '17 at 16:35