Range throwing up an error in VBA

[grabrail]

Hi
I am working on some VBA code and have come across an error when trying to select a number of cells in a column to determine how many entries there are.

The code I am using is borrowed from another spreadsheet I have that I inherited, but is working no problem.

The Code

Worksheets("DPU Report Template").Select
Worksheets("DPU Report Template").Range("A37").Select
Range(Selection, Selection.End(xlDown)).Select

When I run this I get the error:

Method Range of Object Worksheet Failed, with the 3rd line highlighted

One difference between this sheet and the one that is working is, on the one that is working, the button that is pressed to run this code is on the same worksheet as the selected area, on the one that is not working, the button is on a different worksheet

 

[Rick Rothstein]

My guess is that the Range call is referencing the sheet the button is on and not the "DPU Report Template" sheet. Try adding the Worksheets("DPU Report Template"). reference to the front of that Range call.

 

[Michael M]

Your code worked for me.
A bit shorter though

Sub MM1()
Worksheets("DPU Report Template").Select
Range(("A37"), Range("A37").End(xlDown)).Select
End Sub

 

[grabrail]

My guess is that the Range call is referencing the sheet the button is on and not the "DPU Report Template" sheet. Try adding the Worksheets("DPU Report Template"). reference to the front of that Range call.

Thanks, this did what I needed