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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Using this would always select sheet 2 no matter what it's named:
Code:
Sheets(2).Select
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top