How to declare an Active Worksheet in a variable?

excel vba worksheet
unikitty94 picture unikitty94 · May 26, 2015 · Viewed 69.3k times · Source

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?

Answer

Sobigen picture Sobigen · May 26, 2015

You need to use ActiveSheet instead of ActiveWorksheet

Related questions

How to add a named sheet at the end of all Excel sheets?

I am trying to add an Excel sheet named "Temp" at the end of all existing sheets, but this code is not working: Private Sub CreateSheet() Dim ws As Worksheet ws.Name = "Tempo" Set ws = Sheets.Add(After:=Sheets(Sheets.…

Read More
Excel VBA For Each Worksheet Loop

I am working on code to basically go through each sheet in my Workbook, and then update column widths. Below is the code I wrote; I don't receive any errors, but it also doesn't actually do anything. Any help is …

Read More
Is there a macro to conditionally copy rows to another worksheet?

Is there a macro or a way to conditionally copy rows from one worksheet to another in Excel 2003? I'm pulling a list of data from SharePoint via a web query into a blank worksheet in Excel, and then I want …

Read More