0

I am new to VBA and need some help in this.I have an excel workbook with multiple sheets with VBA codes and macros,I would like to replicate the same set of sheet and only change the button values,for eg.,if the previous button (ABC) in the sheet has code like this:

Sub ABC()
    Application.ScreenUpdating = False
    Sheets("Trend").Select
    ActiveSheet.Unprotect
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Responsible Station"). _
        CurrentPage = "ABC"

    Call ResetTrendColors

    Dim btnt As Shape
    Set btnt = ActiveSheet.Shapes(2)
        With btnt.ThreeD
            .Visible = True
        End With
            btnt.Fill.ForeColor.RGB = RGB(205, 48, 57)
            btnt.TextFrame.Characters.Font.Color = RGB(252, 196, 37)

    ActiveSheet.Protect AllowUsingPivotTables:=True

    Sheets("Dashboard").Select
    ActiveSheet.Unprotect
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Responsible Station"). _
        CurrentPage = "ABC"
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Responsible Station"). _
        CurrentPage = "ABC"
    ActiveSheet.PivotTables("PivotTable6").PivotFields("Responsible Station"). _
        CurrentPage = "ABC"
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Responsible Station"). _
        CurrentPage = "ABC"

    Call ResetDashboardColors

    Dim btnd As Shape
    Set btnd = ActiveSheet.Shapes(3)
        With btnd.ThreeD
            .Visible = True
        End With
            btnd.Fill.ForeColor.RGB = RGB(205, 48, 57)
            btnd.TextFrame.Characters.Font.Color = RGB(252, 196, 37)

    ActiveSheet.Protect AllowUsingPivotTables:=True
    Application.ScreenUpdating = True

End Sub

I was able to copy the sheet to another sheet in the same workbook,but when i try to edit the buttons from ABC to say 'XYZ',it throws an error:

Run-time error 1004:Application-defined or object-defined error

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Carmel Jacob
  • 7
  • 5
  • 3
    which row is throwing the error ? –  Jan 30 '19 at 09:17
  • 1
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) • Avoid using `Select` and `ActiveSheet` instead specify your worksheet directly with `Sheets("Dashboard").Unprotect` etc. – Pᴇʜ Jan 30 '19 at 09:23
  • @reportgunner ActiveSheet.PivotTables("PivotTable3").PivotFields("Responsible Station"). _ CurrentPage = "ABC" – Carmel Jacob Jan 30 '19 at 09:36

0 Answers0