Referencing Worksheet Using a Variable (New to VBA

**thenewkidd** · 05-03-2021, 07:48 PM

I am quite new to VBA, while I can do simple "Macro Recording".
I am trying to branch out and shorten unnecessary code.

Background of what I am trying to do.

I have a "database" for every month of the Year (so every tab is named JAN - DEC).

To prevent having to write the same VBA 12 times referencing sheets("JAN").select all the way to sheets("DEC").select 48 times.
(exaggeration but you get the gist) I want to be able to create a variable that references the sheet so that if I need to make changes, I am only changing one thing at the top of the code instead of looking at a long string of code and make changes to every line that has "sheets("JAN").select"

Dim xstr as worksheet
Set xstr = Worksheet("JAN")

It seems when I do
xstr.Select I get an "Debug" request at the line I have this code at.

I'm sure this is something super easy. Just missing how to get variables to work for me to make coding a lot shorter and easier to update in one spot.

Thanks in advance.

**maniacb** · 05-03-2021, 08:02 PM

Try

Please Login or Register  to view this content.

**mgs73** · 05-03-2021, 08:20 PM

Difficult to diagnose without more info - what does the debug message say? Can you upload the spreadsheet?

**thenewkidd** · 05-03-2021, 08:36 PM

Originally Posted by maniacb

Try

Please Login or Register  to view this content.

did that and it is still giving a debug error.

Originally Posted by mgs73

Difficult to diagnose without more info - what does the debug message say? Can you upload the spreadsheet?

The debut says "Run time error 91 Object or With block variable not set"
But it is set.. so I'm confused
I will try and upload. I'm new to VBA so there are a lot of things wrong with the code. I started with "Record" and now I am chopping it down to things I don't need.

**thenewkidd** · 05-03-2021, 08:43 PM

Workbook is too large to post, it keeps failing.

But here is the start of the VBA

Dim xstr As Worksheet
Dim ii As Integer
Set Worksheet = Sheets("JAN")

i = 1

---> [ERROR is right here]
xstr.Select
Columns("A:H").Select
Selection.Copy
Sheets("FILTER").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I4").Select
Application.CutCopyMode = False

**thenewkidd** · 05-03-2021, 08:52 PM

I see what I did,
Thank you for your help. I feel dumb.

Old
Dim xstr As Worksheet
Dim ii As Integer
Set Worksheet= Sheets("JAN")

New
Dim xstr As Worksheet
Dim ii As Integer
Set xstr = Sheets("JAN")

Referencing Worksheet Using a Variable (New to VBA

LinkBack

Thread Tools

Rate This Thread

Display

Referencing Worksheet Using a Variable (New to VBA

Re: Referencing Worksheet Using a Variable (New to VBA

Re: Referencing Worksheet Using a Variable (New to VBA

Re: Referencing Worksheet Using a Variable (New to VBA

Re: Referencing Worksheet Using a Variable (New to VBA

Re: Referencing Worksheet Using a Variable (New to VBA

Thread Information

Users Browsing this Thread

Similar Threads

Use of variable in a worksheet cell to refer to variable tabs on a second worksheet

[SOLVED] Variable cell referencing a worksheet

referencing variable help

referencing a defined worksheet variable in a formula in vba

Simple formula with variable names referencing another worksheet

Variable referencing?

Referencing a variable in place of worksheet name

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions