Beginner- Macro Question

[ksundaram]

Hey, I am trying to create a macro that will automatically do a particular set of changes to the same workbook, that has multiple worksheets, every three months. I do not know much/really anything about VB unfortunately, so I just recorded the macro through excel.

It recorded fine and it has all the changes that I want it to make for my workbook, but I have a problem in that the my worksheet names also change every 3 months i.e., one will say 'December 2010 vs. Sep 2010' and in three months it'll change to 'March 2011 vs Dec. 2010.'

When I try and run this same macro (which i created when the worksheet was called 'Dec 2010 vs. Sep 2010') with the changed tab names I get an error because I guess it can't find that particular worksheet, even though all the changed was the name.

Is there anyway to code my macro so that it just goes to the generic Worksheet 2 instead of looking for 'Dec 2010 vs. Sep 2010'?

 

[Jonmo1]

Welcome to the board...

There are 3 ways to refer to a Sheet.

1. Sheets("Sheet1").Range(..) - This refers to the name as it appears on the Sheet's TAB

2. Sheets(2).Range(..) - This refers to the Index Number of the sheet, numbered as they appear in the Excel Window from Left to Right

3. Sheet1.Range(..) - This is called the CodeName.
When you see the list of sheets in the VBA window, they are listed like
Sheet1(Sheet1)
Sheet2(Sheet2)

The part in (parens) is the TAB Name, the other part is the CODEName

The codename will not change when a sheet is renamed.

So if you start with

Sheet1(Sheet1)
But rename the sheet tab to say "Whatever" it will then be
Sheet1(Whatever)


Hope that helps.

 

[ksundaram]

Thank you so much for your quick reply! I ended up changing it to from Sheets("TEXT").Select to Sheets(3).Select and it appers everything seems to be working properly. Do you see any issues with this change that occur later on?

Thank you again!

 

[ksundaram]

Actually it appears there is an issue with the code I used...if I were on a worksheet other than Worksheet 1 when I run that macro then it automatically starts doing the formatting changes from whatever worksheet i'm currently on instead of going to the 1st page.

If I'm understanding your method correctly, instead of calling it Sheets(3) I should rename it Sheet3(Sheet3)?

 

[RSXchin]

at the very beginning of your code, you should make sure you tell it which sheet to start on. and there is no need to rename your sheet. reference the 3rd sheet as sheets(3) and the first as sheets(1)

 

[ksundaram]

at the very beginning of your code, you should make sure you tell it which sheet to start on. and there is no need to rename your sheet. reference the 3rd sheet as sheets(3) and the first as sheets(1)

How would I indicate in the code which sheet to start on? Sorry, I don't know really know anything about VB.

 

[RSXchin]

would you post the first few lines of your code please?

 

[ksundaram]

Sub BSAR431500_AR_441500_AP()
'
' BSAR431500_AR_441500_AP Macro
'
'
Range("C21").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Copy
Range("C22").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[RSXchin]

if you're starting on the first sheet EVERY TIME, use this

Sub BSAR431500_AR_441500_AP()
'
' BSAR431500_AR_441500_AP Macro
'
'
sheets(1).select
    Range("C21").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Copy
    Range("C22").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

that should work for you.

 

[ksundaram]

Great, thank you, I'll give it a try in a little bit