Apply one macro to another sheet/workbook regardless of sheet or workbook name

Question

I have an issue with macros returning errors, and I was wondering if you guys could shed some light on this issue.

The macro I have looks like the following:

Sub pivot1_macro()
'
' pivot1_macro Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "7-14 week!R1C2:R199C35", Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="Sheet5!R3C1", TableName:="PivotTable3", DefaultVersion _
    :=xlPivotTableVersion15
Sheets("Sheet5").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("PartMOD")
    .Orientation = xlColumnField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("EnteredShift")
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Defect")
    .Orientation = xlRowField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
    "PivotTable3").PivotFields("Defect Quantity"), "Sum of Defect Quantity", xlSum
Columns("D:D").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Range("B3").Select
Range("B3").Select
End Sub

I'd like to apply this macro to any active worksheet, but I'm not quite sure how to make set 'SourceData' to current active worksheet.

Quick note, it's [best to avoid `.Activate`/`.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), as it can cause unexpected results. — BruceWayne, Jul 25 '16 at 14:20

score 0 · Answer 1 · answered Jul 25 '16 at 14:32

0

You can use '&' to concatenate strings, so wouldn't

SourceData := ActiveSheet.Name & "!R1C2:R199C35"

work for you to get the current sheet's name?

answered Jul 25 '16 at 14:32

Rabbitman14

331
1
3
13

Apply one macro to another sheet/workbook regardless of sheet or workbook name

1 Answers1