PDA

View Full Version : VBA for Slicer & Table Formatting



noofus
12-28-2018, 10:49 AM
I am new to VBA and could use help change the following recorded macro to apply to a copied sheet (active sheet).

I have a worksheet with one table and two slicers. When I copy the worksheet, I'd like to be able to change the format style of the table and slicers by clicking a button on the copied sheet. The code works perfectly on the original sheet, but when I copy it, the table and slicer names change (adding a number after the name), which causes the macro to return an error.

Please help me modify this code to work on any copied worksheet.



Sub RedSheet()
Range("Cost_Details[[#Headers],[Item]]").Select ActiveSheet.ListObjects("Cost_Details").TableStyle = "TableStyleLight10"
ActiveSheet.Shapes.Range(Array("Scope")).Select
ActiveWorkbook.SlicerCaches("Slicer_Scope").Slicers("Scope").Style = _
"SlicerStyleDark2"
ActiveSheet.Shapes.Range(Array("BREAKOUT")).Select
ActiveWorkbook.SlicerCaches("Slicer_BREAKOUT").Slicers("BREAKOUT").Style = _
"SlicerStyleDark2"
End Sub