Relative Reference and Multiple tabs from listing

[Bouska]

Hello,

Every month I create a YTD statement and since this is done monthly I need to copy the formulas from on month to the next while hard-coding the prior-month. I have already created a macro that works exactly as I need it to, but what I need to be able to do now is add a portion to the macro to run on specific tabs within my workbook. Can someone please tell me what I need to add to the below code in order to have it work off the listing I have already created on my input tab? FYI "John Smith" is a sample of what a name would be in my input tab listing.

Sub NewUpdate()
'
' NewUpdate Macro
'

'

ActiveCell.Select
Sheets("John Smith").Select
ActiveCell.Range("A1:T1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(-2, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("EELookup").Select
ActiveCell.Select
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

 

[brucef2112]

If you want to do this one sheet at a time. ie the process is, you select the 'User Name' sheet so it is active, and then run your macro. and repeat on the next sheet,
change:Sheets("John Smith").Select
To: ActiveSheet.select

And you can remove the first line:
ActiveCell.Select
as it isn't really doing anything useful...

 

[Bouska]

That Does not work. I am running a relative references macro and also I don't want to run the macro by clicking on every tab. I want it to run for the specific tabs I need the macro to run for. Also every tab in this workbook should not have the macro run on it only the peoples tabs that I designate need it run. Do you have any other fix ideas?

 

[brucef2112]

.....and also I don't want to run the macro by clicking on every tab. I want it to run for the specific tabs I need the macro to run for. Also every tab in this workbook should not have the macro run on it only the peoples tabs that I designate need it run.....

My original suggestion was based on you selecting the person's tab you want to run the macro on and then run that macro. Then move to the next person's tab you want and run it from there. etc until you have run it on all of the peoples tabs that you desire. But I see your original post made an obscure reference to an input tab but offered no details about it. Is this tab the holy grail that your macro should look at to find the people you want the macro to run? If so, some more details about it would be a wonderful thing. If this isn't it, and you don't want to click on the persons tab, how will the macro know what people tabs you want it to work on? What would designate a persons tab to have your macro run on it or not?

..... I am running a relative references macro .....

if you expect the same thing to happen on each person's tab then this would be a good thing

The lacking details of your info tab aside,
First, your code makes huge assumptions about the range to work with.
It starts with copying 20 columns on a row from wherever the ActiveCell happens to be and pastes it two rows further down (with formulas).
However if B2 is the active cell, ActiveCell.Range("A1:T1").Select will make the selection you are copying B2:U2)

I could be wrong, but I don't think this is what you intended. If your intent was to always copy the range A1:T1 regardless of what cell is current, then this is the beginning of many problems with your code. Expecting that A1 is always the active cell is fraught with problems and will ensure your code goes south real quick from that point on.

Second, your code goes back to the original source of the copy and does a paste values. Again based on the "activecell" address.
You then go to the ActiveCell on your EELookup tab and change some formating of the cell. However again, relying on Active cell to be the actual location where the code should make changes to, ActiveCell.Select is not necessarily going to paste starting at cell A1.

Want to test this? goto your person's tab and click on cell D3. Now go to your EELookup tab and click in cell C7. Now go run your macro.


If your intent is to copy the range A1:T1. Then paste it two rows down to A3:T3 with its formulas. Then paste the values of A1:T1 back. This will do it regardless of what cell happens to be active.
I have no details about your EELookup tab so I can't even begin to know what code to write for whatever you are changing a cell's format to yellow etc..

Sub NewUpdate()Dim aRange As Range
Dim aPersonsSheet As Worksheet
Dim aEELookupSheet As Worksheet
    
    Set aPersonsSheet = Sheets("John Smith")
    Set aRange = aPersonsSheet.Range("A1:T1")
    ' The next two lines copies the A1:T1 range two rows down, then pastes the 'values' back to the original row.
    aRange.Copy Destination:=aRange.Offset(2, 0)
    aRange.Value = aRange.Value
    
    ' I have no idea what you are doing on this sheet so Ive got nothing to work with other than to assume
    ' *whatever* the active cell is, this is the one you want to change the cell formating on.
    Set aEELookupSheet = Sheets("EELookup")
    aEELookupSheet.Activate
    
    Set aRange = ActiveCell ' NOTE: Relying on the ActiveCell for address reference is a terible thing to do.
    With aRange.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With


End Sub

This just fixes the code you had to do what it should do regardless of what cell has focus on the person's tab.

To answer your original question you need to tell us more about the info tab and the EELookup tab.