How To Make Macro Not Sheet Specific

Question

If possible I would like the macro below to run the same way, regardless of what the name of the sheet is. For instance on this file it's "ZPPV Final for March 2015". But that March, may change to April or May, etc. Can I have this macro run regardless of what month is on that sheets tab? Please see below:

Sub PPV()
'
' PPV Macro
'

'
    Sheets("ZPPV Final for March 2015").Select
    Range("Z3").Select
    ActiveCell.FormulaR1C1 = "Week"
    Range("Z4").Select
    ActiveCell.FormulaR1C1 = "=WEEKNUM(RC[-9])"
    Range("Z4").Select
    Selection.AutoFill Destination:=Range("Z4:Z8858")
    Range("Z4:Z8858").Select
    Range("Z3").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "***ZPPV Final for March 2015***!R3C2:R8858C26", Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable9" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Plant")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Week")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("           PPV"), "Sum of            PPV", xlSum
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Plant").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Plant").CurrentPage = _
        "1027"
    Sheets("**ZPPV Final for March 2015").**Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable9").PivotCache. _
        CreatePivotTable TableDestination:="Sheet2!R1C6", TableName:="PivotTable10" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet2").Select
    Cells(1, 6).Select
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Plant")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Week")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
        "PivotTable10").PivotFields("           PPV"), "Sum of            PPV", xlSum
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Vendor Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    Range("F4").Select
    ActiveSheet.PivotTables("PivotTable10").PivotFields("Vendor Name").ShowDetail _
        = False
    ActiveSheet.PivotTables("PivotTable10").PivotFields("Plant").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable10").PivotFields("Plant").CurrentPage = _
        "1027"
    Sheets("ZPPV Final for March 2015").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable10").PivotCache. _
        CreatePivotTable TableDestination:="Sheet2!R1C10", TableName:= _
        "PivotTable11", DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet2").Select
    Cells(1, 10).Select
    With ActiveSheet.PivotTables("PivotTable11").PivotFields("Plant")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable11").AddDataField ActiveSheet.PivotTables( _
        "PivotTable11").PivotFields("           PPV"), "Sum of            PPV", xlSum
    With ActiveSheet.PivotTables("PivotTable11").PivotFields("Material No.")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable11").PivotFields("Plant").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable11").PivotFields("Plant").CurrentPage = _
        "1027"
    Range("A3").Select
    ActiveSheet.PivotTables("PivotTable9").CompactLayoutRowHeader = "Week"
    Range("F3").Select
    ActiveSheet.PivotTables("PivotTable10").CompactLayoutRowHeader = "Vendor"
    Range("J3").Select
    ActiveSheet.PivotTables("PivotTable11").CompactLayoutRowHeader = _
        "Material Number"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "PPV By Week"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "PPV By Vendor"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "PPV By Material #"
    Range("J2").Select
    Selection.Font.Bold = True
    Range("F2").Select
    Selection.Font.Bold = True
    Range("A2").Select
    Selection.Font.Bold = True
    Range("D3").Select
End Sub

Add a `ByVal ws As Worksheet` parameter, and then change all `ActiveSheet` instances to refer to `ws` instead, and use `ws.Range` instead of just `Range`. Then call that procedure from another procedure whose job will be to determine what worksheest to give it. — Mathieu Guindon, Apr 07 '15 at 20:35
I would recommend reading this article: http://www.techrepublic.com/blog/10-things/10-ways-to-reference-excel-workbooks-and-sheets-using-vba/ — Gareth, Apr 07 '15 at 20:36
Using "Active" objects in your code is not good. Try to refer to specific objects only. It makes your code easier to maintain and debug. — Mr. Mascaro, Apr 07 '15 at 20:36
[Abandon *Select/Activesheet* and work in your objects directly](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — L42, Apr 07 '15 at 22:26

score 1 · Answer 1 · answered Apr 07 '15 at 22:11

1

Here's a quick example

Dim sh as worksheet
Dim ws as worksheet
set ws=sheets("Sheet2")
set sh=activesheet
sh.Range("Z3") = "Week"

with ws
'do something
end with

answered Apr 07 '15 at 22:11

Davesexcel

6,896
2
27
42

score 1 · Answer 2 · answered Apr 07 '15 at 22:44

If there is only one worksheet in your workbook, then use the index.

Dim ws As Worksheet ' Declare a worksheet type variable
Set ws = Thisworksbook.Sheets(1) ' assign the worksheet object

If there are other worksheets but the same worksheet is used all throughout (meaning, the sheet name is just edited for the new month) then use the worksheet codename.
```
Dim ws As Worksheet ' Declare a worksheet type variable
Set ws = Sheet1 ' use Codename to assign worksheet object to variable
```

enter image description here

You can only edit the Codename in the properties window as shown above.
The user can change the sheet name outside but not the Codename.

If a new sheet is created for every month, use a loop.

Dim ws As Worksheet, i As Integer
Dim MoYr As String

MoYr = Format(Date, "mmmm yyyy") ' Returns April 2015 if run now
With ThisWorkbook
    For i = 1 To .Sheets.Count
        If InStr(.Sheets(i).Name, MoYr) <> 0 Then
            Set ws = .Sheets(i): Exit For
        End If
    Next
End With
If ws Is Nothing Then Exit Sub ' Just in case nothing is found

Let me try this out. I think I understand what you are saying. Thx in advance. — user3067028, Apr 08 '15 at 17:12

How To Make Macro Not Sheet Specific

2 Answers2