Changing a specific text in the codes to reference to the name of the previous sheet

[masuzo]

Hi, I have this code below:

Sub InsertPivotTable()


'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
[COLOR=#000080]
[/COLOR]
[COLOR=#000080]'Insert a New Blank Worksheet[/COLOR]
[COLOR=#000080]On Error Resume Next[/COLOR]
[COLOR=#000080]Application.DisplayAlerts = False[/COLOR]
[COLOR=#000080]Worksheets("PivotTable").Delete[/COLOR]
[COLOR=#000080]Sheets.Add Before:=ActiveSheet
'How can i change the below "301106" to refer to the current active sheet name before the codes below???[/COLOR]
ActiveSheet.Name = "[COLOR=#ff0000]301106[/COLOR] PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("[COLOR=#ff0000]301106[/COLOR] PivotTable")
Set DSheet = Worksheets("[COLOR=#ff0000]301106[/COLOR]")


'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Range("A4:G9999")


'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="[COLOR=#ff0000]301106[/COLOR] PivotTable")


'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="[COLOR=#ff0000]301106[/COLOR] PivotTable")


'Insert Row Fields
Cells(3, 1).Select
    With ActiveSheet.PivotTables("[COLOR=#ff0000]301106[/COLOR] PivotTable").PivotFields("GL code: [COLOR=#ff0000]301106[/COLOR] ")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("[COLOR=#ff0000]301106[/COLOR] PivotTable").PivotFields("Company")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("[COLOR=#ff0000]301106[/COLOR] PivotTable").PivotFields("Description")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("[COLOR=#ff0000]301106[/COLOR] PivotTable").PivotFields("Transaction Number")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("[COLOR=#ff0000]301106 [/COLOR]PivotTable").PivotFields("Transaction Type")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("[COLOR=#ff0000]301106[/COLOR] PivotTable").AddDataField ActiveSheet.PivotTables( _
        "[COLOR=#ff0000]301106[/COLOR] PivotTable").PivotFields("Local Amount (SGD)"), "Count of Local Amount (SGD)" _
        , xlCount
    With ActiveSheet.PivotTables("[COLOR=#ff0000]301106[/COLOR] PivotTable").PivotFields( _
        "Count of Local Amount (SGD)")
        .Caption = "Sum of Local Amount (SGD)"
        .Function = xlSum
    End With
    Range("B5:F17").Select
    Range("F17").Activate
    Selection.Style = "Comma"
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Range("A1").Select


'Format Pivot Table
ActiveSheet.PivotTables("[COLOR=#ff0000]301106[/COLOR] PivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("[COLOR=#ff0000]301106[/COLOR] PivotTable").TableStyle2 = "PivotStyleMedium9"


End Sub

In this code above, I would like to change "301106" to refer to the name of the worksheet where I take my data from, which is the previous worksheet in the code highlighted in blue. I have many data worksheet from codes "301106" to "301111" and I want to create a pivot table for each separate worksheet. For example, if I run the macro using a different worksheet named "301107", the entire code will change "301106" (those codes highlighted) to "301107" and so on.

Sorry if its still unclear as I am new here.

Appreciate any help.

 

[AlphaFrog]

Try something like this...

[color=darkblue]Sub[/color] InsertPivotTable()
    
    [color=green]'Declare Variables[/color]
    [color=darkblue]Dim[/color] PSheet    [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] DSheet    [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] PCache    [color=darkblue]As[/color] PivotCache
    [color=darkblue]Dim[/color] PTable    [color=darkblue]As[/color] PivotTable
    [color=darkblue]Dim[/color] PRange    [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] LastRow   [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LastCol   [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=green]'Insert a New Blank Worksheet[/color]
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    Application.DisplayAlerts = [color=darkblue]False[/color]
    Worksheets("PivotTable").Delete
    Application.DisplayAlerts = [color=darkblue]True[/color]
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    
    [color=darkblue]Set[/color] DSheet = ActiveSheet
    [color=darkblue]Set[/color] PSheet = Sheets.Add(Before:=ActiveSheet)
    PSheet.Name = DSheet.Name & " PivotTable"
    
    [color=green]'Define Data Range[/color]
    LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    [color=darkblue]Set[/color] PRange = DSheet.Range("A4:G9999")
    
    [color=green]'Define Pivot Cache[/color]
    [color=darkblue]Set[/color] PCache = ActiveWorkbook.PivotCaches.Create _
                 (SourceType:=xlDatabase, SourceData:=PRange). _
                 CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
                                  TableName:=PSheet.Name)
    
    [color=green]'Insert Blank Pivot Table[/color]
    [color=darkblue]Set[/color] PTable = PCache.CreatePivotTable _
                 (TableDestination:=PSheet.Cells(1, 1), TableName:=PSheet.Name)
    
    [color=green]'Insert Row Fields[/color]
    Cells(3, 1).Select
    
    [color=darkblue]With[/color] PSheet.PivotTables(PSheet.Name)
    
        [color=darkblue]With[/color] .PivotFields("GL code: " & DSheet.Name & " ")
            .Orientation = xlPageField
            .Position = 1
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]With[/color] .PivotFields("Company")
            .Orientation = xlColumnField
            .Position = 1
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]With[/color] .PivotFields("Description")
            .Orientation = xlRowField
            .Position = 1
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]With[/color] .PivotFields("Transaction Number")
            .Orientation = xlRowField
            .Position = 2
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]With[/color] .PivotFields("Transaction Type")
            .Orientation = xlRowField
            .Position = 3
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        .AddDataField ActiveSheet.PivotTables( _
            PSheet.Name).PivotFields("Local Amount (SGD)"), "Count of Local Amount (SGD)", xlCount
        [color=darkblue]With[/color] .PivotFields( _
             "Count of Local Amount (SGD)")
            .Caption = "Sum of Local Amount (SGD)"
            .Function = xlSum
        [color=darkblue]End[/color] [color=darkblue]With[/color]
                
        Range("B5:F17").Style = "Comma"
        Columns("B:F").EntireColumn.AutoFit
    
        [color=green]'Format Pivot Table[/color]
        .ShowTableStyleRowStripes = [color=darkblue]True[/color]
        .TableStyle2 = "PivotStyleMedium9"
        
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[RoryA]

Welcome to the forum.

Please take a minute to read the forum rules, especially related to cross-posting and then update your question to include links to your cross posts in other forums. Thanks.

 

[masuzo]

Sorry about disregarding the rules...

Hi AlphaFrog, I tried your code and there seems to be error : "Run-time error 13 Type Mismatch"

The debug highlighted the part on defining the pivot cache, and hovering it will show that PCache = Nothing

Hope you can provide me with some help on this..


Below is the hyperlink to another post i asked the same question with.

https://www.excelforum.com/excel-programming-vba-macros/1215689-changing-a-specific-text-in-the-codes-to-reference-to-the-name-of-the-previous-sheet.html

 

[masuzo]

I found a better way to change my code to filter all the codes from a single main pivot table.

Code is below in case anyone is interested.

The user can select the "GL Account" based on the code he wants and get the result based on that account only, which is what I needed.

Not sure to mark thread as solved as this is another solution to a different problem stated above...

[COLOR=#333333]Sub InsertPivotTableFromMain()[/COLOR][COLOR=#333333]

      
    'Declare Variables
    Dim PSheet    As Worksheet
    Dim DSheet    As Worksheet
    Dim PCache    As PivotCache
    Dim PTable    As PivotTable
    Dim PRange    As Range
    Dim LastRow   As Long
    Dim LastCol   As Long




'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True




Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Data")
    
    'Define Data Range
    LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = DSheet.Range("A1:AE9999")
    
    'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="OverallPivotTable")


'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="OverallPivotTable")


'Insert Row Fields
    Cells(3, 1).Select
    
'Fields for pivot
 ActiveSheet.PivotTables("OverallPivotTable").AddDataField ActiveSheet.PivotTables( _
        "OverallPivotTable").PivotFields("Local Amount (SGD)"), "Count of Local Amount (SGD)" _
        , xlCount
    With ActiveSheet.PivotTables("OverallPivotTable").PivotFields( _
        "Count of Local Amount (SGD)")
        .Caption = "Sum of Local Amount (SGD)"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("OverallPivotTable").PivotFields("Company")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("OverallPivotTable").PivotFields("Description")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("OverallPivotTable").PivotFields("Transaction Number")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("OverallPivotTable").PivotFields("Transaction Type")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("OverallPivotTable").PivotFields("GL Account")
        .Orientation = xlPageField
        .Position = 1
    End With
                
        Range("B5:H9999").Style = "Comma"
        Columns("B:H").EntireColumn.AutoFit


        
End Sub



[/COLOR]