Limit macros to one workbook - Error 1004. Select method of Range class failed

[Yodelayheewho]

Hello,
I want all sheets to be able to use the macros, but need to limit the macros to one workbook. If I open a new workbook, I don't want the macros to come with it.

One of the macros I'm using is to scroll to the next empty row. This code does NOT limit the macro to the one workbook.

Private Sub imgScrollBottom_Click()
'Lands at the next empty row
Range("D" & Rows.Count).End(xlUp).Offset(1).Select
End Sub

So, I've tried editing the code, per below, to limit it to ThisWorkbook, but I get an error.

Sub imgScrollBottom_Click()
'Lands at the next empty row
Dim wb As Workbook
Dim ws As Integer

Set wb = Workbooks("Systems Order Entry Master.xlsm")
For ws = 1 To 17 'number of worksheets
ThisWorkbook.Sheets(ws).Range("D" & Rows.Count).End(xlUp).Offset(1).Select  'Error 1004. Select method of Range class failed
Next
End Sub

Thank you!

 

[Debaser]

You cannot select a range unless it's workbook and worksheet are active. Either use Application.Goto or activate the workbook/worksheet first in the code.

 

[Yodelayheewho]

I got it working...

Sub imgScrollBottom_Click()
'Lands at the next empty row
Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks("Systems Order Entry Master.xlsm") 'Assigns workbook to object
Set ws = wb.Sheets("Master") 'Creates object for worksheet
wb.Activate 'Activates workbook
ws.Activate 'Activates worksheet
For sh = 1 To 17 'number of worksheets
ThisWorkbook.Sheets("Master").Range("D" & Rows.Count).End(xlUp).Offset(1).Select 'Error 1004. Select method of Range class failed
Next
End Sub

I'm not sure this is the best way to write it. Thank you so much Debaser!

Marc L. I saw you posted, then it's gone??

 

[Yodelayheewho]

I think I should have asked this first. These macros that I want limited to one workbook have been added to the Quick Access Toolbar.
I opened a blank worksheet and realized the macros do NOT appear, but the Quick Access Toolbar items do. I need these to be limited to one workbook and not 'follow' me in other workbooks.

 

[Yodelayheewho]

Hold the phone. I figured this out. I hope this helps someone else.