Subscript Out of Range Error

[gvillepa]

Was hoping you could help this VB beginner.

Keep getting '9' Subscript Out of Range error. Debug brings me to this:

lastrow = Sheets("DB").Range("A356000").End(xlUp).Row + 1

The Range is dynamic and I am think my line code is off since I am trying to set some sort of range. Help please! Thanks,

Sub MyMacro()   
 
Dim lastrow As Long       
dTime = Now + TimeValue("00:00:12")    

Application.OnTime dTime, "MyMacro"

lastrow = Sheets("DB").Range("A356000").End(xlUp).Row + 1

Worksheets("1 Minute Refresh").Range("A2:R101").CopyActiveSheet.Paste Destination:=Worksheets("DB").Range("A" & lastrow)

End Sub

 

[Fluff]

Try

LastRow = Sheets("DB").Range([COLOR=#0000ff]"A" & Rows.Count[/COLOR]).End(xlUp).Row + 1

 

[mikerickson]

Is the sheet name DB spelt correctly? No leading or trailing spaces in the actual sheet name?
Is the workbook that holds it the active one when the code is run?

 

[dmt32]

Hi,
try

lastrow = ThisWorkbook.Worksheets("DB").Cells(Rows.Count, "A").End(xlUp).Row + 1

Assumes that sheet name DB exists.

Dave

 

[gvillepa]

Thanks Fluff. This Worked. I Tried doing just Range ("A") before but overlooked the Rows.Count.

Try

LastRow = Sheets("DB").Range([COLOR=#0000ff]"A" & Rows.Count[/COLOR]).End(xlUp).Row + 1

I appreciate the problem solved!

 

[Fluff]

Glad we could help & thanks for the feedback