CODEWITHSUNDEEP
excelvbaworksheet
unikitty94
unikitty94

Reputation: 81

How to declare an Active Worksheet in a variable?

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?

Upvotes: 6

Views: 92797

Answers (4)

user17980741
user17980741

Reputation: 1

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.

Upvotes: 0

Mentos
Mentos

Reputation: 1202

For 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 Worksheet
Dim Example as Integer

Example = Sheets("ExampleWorksheet").Cells(x,y)

So you get rid of messing around with an active or not active worksheets.

Upvotes: 0

iShaymus
iShaymus

Reputation: 532

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

Upvotes: 4

Sobigen
Sobigen

Reputation: 2169

You need to use ActiveSheet instead of ActiveWorksheet

Upvotes: 11

Related Questions