I'm trying to keep a variable to the currently open worksheet in VBA. So far I have
Dim Active As WorksheetSet 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?
Best Answer
You need to use ActiveSheet instead of ActiveWorksheet
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 Worksheetws = Thisworkbook.Worksheets(1)ORws = 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 Worksheetws = Thisworkbook.ActivesheetORws = ActivesheetFor me I like to directly speak to the worksheet I want to adress using Sheet("Worksheet").
So if you want to take an information form a particular sheet you can use this:
Dim ExampleWorksheet as WorksheetDim Example as IntegerExample = Sheets("ExampleWorksheet").Cells(x,y)So you get rid of messing around with an active or not active worksheets.
Dim bradouts As Workbook
Dim bradsht As Worksheet
If Right(myfile, 4) = ".xls" Then
Set bradouts = Workbooks.Open(myfolder & myfile)End If
Set bradsht = bradouts.ActiveSheet <------
It shows error where i pointed arrow, can someone explain....i posted it here because its not allowing me to ask question directly.