CODEWITHSUNDEEP
excelvba
Shayna
Shayna

Reputation: 25

How do you specify a worksheet instead of ActiveSheet?

I recorded a macro and it created a With loop on the "ActiveSheet". I want to specify which worksheet so I can create a button with that macro contained within it and it will refer to the correct worksheet. It should be simple syntax in vba how to specify a specific worksheet instead of "With ActiveSheet" - I'm just unfamiliar with vba syntax.

Upvotes: 2

Views: 12945

Answers (1)

Stupid_Intern
Stupid_Intern

Reputation: 3460

First declare a variable like this

Dim wk As Worksheet

Then Set it to any worksheet you want using any of the following syntax's. These are just a few ways there are other ways too.

Set wk = Sheet1   'Sheet1 is the sheet Number 
Set wk = Worksheets("Sheet1")   'Sheet1 is the sheet Name.
Set wk = Worksheets(1)   '1 is the Worksheet Index Number

Then you can do things with that variable like this

wk.Name = "NewName of Worksheet"    
wk.Range("A1") = "Assigning something to range A1 of that worksheet"

Upvotes: 9

Related Questions