Menu
I'm trying to keep a variable to the currently open worksheet in VBA. So far I have
Dim Active As Worksheet
Set Active = ActiveWorksheet
For some reason it gives me an error in the last line, saying Object required. My code opens a new worksheet with the data in the Active sheet, so I need a variable to hold my place in the currently active worksheet. Any idea why this isn't working correctly?
899
By keyboard: First, press F6 to activate the sheet tabs. Next, use the left or right arrow keys to select the sheet you want, then you can use Ctrl+Space to select that sheet.
When we declare a variable, we need to assign a data type. We can also assign objects as data types. To assign a value to declared object variables, we need to use the word “SET.” The word “Set” refers to a new object in VBA. For example, the particular range of the particular worksheet.
First, we have declared the variable as “Worksheet.” Next, we have set the reference to the variable as “Sales” using the worksheet object. Now, the variable “Ws” holds the reference of the worksheet “Sales.” Now, using the “Ws” variable, we have renamed the worksheet “Sales Sheet.”
You need to use ActiveSheet instead of ActiveWorksheet
134
I prefer not to use ActiveSheet in VBA because if further down the track you start writing code that uses multiple worksheets it can become confusing when trying to determine which is which. I prefer the following
dim ws as Worksheet
ws = Thisworkbook.Worksheets(1)
OR
ws = Thisworkbook.Worksheets("worksheet name")
The only time I use Activesheet now is when I need some code to work on any worksheet regardless of what it contains, which is very rare. In this case as has been stated above use:
dim ws as Worksheet
ws = Thisworkbook.Activesheet
OR
ws = Activesheet
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
