Running recorded macro on different workbook

WallyExcel

New Member
Joined
Sep 3, 2014
Messages
14
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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
Using this would always select sheet 2 no matter what it's named:
Code:
Sheets(2).Select
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

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

Code:
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

New Member
Joined
Sep 3, 2014
Messages
14
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

Legend
Joined
May 15, 2003
Messages
29,536
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,448
Messages
5,642,218
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top