Running recorded macro on different workbook

[WallyExcel]

My brain is hurting from trying to figure this out, so any help would be greatly appreciated : )

I already have a Macro saved to my Personal file in order to use it on any workbook -- the macro is intended to add information to 'Sheet2' of a workbook, as well as paste data from 'Sheet1', as well as use formulas on 'Sheet2' to pull data from 'Sheet1'. The issue that I'm running into is that I need to do this for several workbooks where 'Sheet1' may already be named ('Oct2014' or 'Aug2014' for example), and so the recorded macro is only working on the initial workbook that I set it up under.

Below is a sample (I know a lot of people say they are VBA newbs - I truly am one, so I will struggle but definitely try to understand any VBA lingo you may have to offer):

Sub Test5()
'
' Test5 Macro
'


'
Sheets.Add After:=Sheets(Sheets.Count)
ActiveCell.FormulaR1C1 = "A"
Range("A2").Select
ActiveCell.FormulaR1C1 = "B"
Range("A3").Select
ActiveCell.FormulaR1C1 = "C"
Range("B1").Select
Sheets("TEST").Select
ActiveWindow.SmallScroll Down:=-3
Range("G11:I12").Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=(TEST!R[8]C[5]*TEST!R[9]C[5])"
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:D3"), Type:=xlFillDefault
Range("B3:D3").Select
Range("E4").Select
End Sub


This is a VERY small example of what I'm trying to do (basically copying several rows and multiplying values to pull onto sheet2). I sincerely apologize if this is been an exhausted topic of discussion on these boards, but I haven't been able to find anything that hasn't gone completely over my head. Thank you in advance!

 

[My Aswer Is This]

Using this would always select sheet 2 no matter what it's named:

Sheets(2).Select

 

[Smitty]

Welcome to the Board!

You can also get rid of all that Selecting, as it's generally unnecessary:

Sub Test5()
 Sheets.Add After:=Sheets(Sheets.Count)
 ' This can be different if the ActiveCell isn't what you anticipate it to be - better to use an actual cell reference
 ActiveCell.Value = "A"
 Range("A2").Value = "B"
 Range("A3").Value = "C"
 Sheets("TEST").Range("G11:I12").Copy
 With Sheets("Sheet1")
    .Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
       Application.CutCopyMode = False
    With .Range("B3")
        .FormulaR1C1 = "=(TEST!R[8]C[5]*TEST!R[9]C[5])"
        .AutoFill Destination:=Range("B3:D3"), Type:=xlFillDefault
    End With
  End With
 End Sub

 

[WallyExcel]

Thank you for the responses! Smitty, I noticed that the code you provided still references 'TEST' which is the name of Sheet1 in this particular workbook -- would this still function on a separate workbook with a different name for Sheet1?

 

[Smitty]

Nope, you'd need to reference the sheet differently if the name is different. If it will always be in the same spot then I'd try the Sheets(2) method that was mentioned. Your best bet is probably to try to keep the sheet name consistent, or put a variable in a cell somewhere that you can reference.