Multiple macros specific to a worksheet in one workbook

[saadalrahman]

Hello,

I am a complete newbie to vba and learning it on my own. I am trying to automate a very tedious process of monthly reporting using excel. The code below is what I have got using the Record Macro function and tweaking it quite a bit to suit my needs. I need to run a similar macro on multiple worksheets in one excel workbook. I want to know how I should proceed in creating the macro for the next worksheet. Do I have to define the worksheet name in each Sub so as not to create any conflicts with named ranges in other macros for other sheets? Where exactly would I need to define the worksheet in each? Really appreciate any help.


Sub PortfolioByCurrency()
'
'
'
Dim oldRange As Range
Range("A1").End(xlDown).Select
Set oldRange = Range(ActiveCell, ActiveCell.Offset(0, 5))
oldRange.Select
Selection.AutoFill Destination:=Range(oldRange, oldRange.Offset(1, 0)), Type:=xlFillDefault
oldRange.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

 

[Joe4]

Welcome to the Board!

Currently, your macro does not contain any sheet references. So, then if you run it, it will run against whatever the Active sheet is, meaning you could run this macro against any sheet you want.

 

[saadalrahman]

Welcome to the Board!

Currently, your macro does not contain any sheet references. So, then if you run it, it will run against whatever the Active sheet is, meaning you could run this macro against any sheet you want.

Thanks for the reply Joe4. I am actually trying to figure out how and where to put the sheet references so that the macro will only run for a one particular sheet only. Would I have to change all the ActiveCell references to Sheetname etc? Any help would be much appreciated.

Thanks

 

[Joe4]

If, at the beginning of your code you select which sheet to run it against, it should only run against that sheet if you do not have any explicit sheet references under that.
You would do that like this:

Sheets("Sheet1").Activate

or

Sheets("Sheet1").Select