Twollaston
Board Regular
- Joined
- May 24, 2019
- Messages
- 241
Hey everyone,
I have a macro that creates a data table on one sheet and a pivot table on another sheet, and the problem I'm having is that i can only run the macro once and then I have to change the sheet names to do it again. I'm trying to make it so that if the sheet name is taken, then it will add a 1,2,3,4,5 etc... at the end, depending on how many times it's run.
If anyone can help me ammend my code that would be greatly appreciated.
I was hoping to do this for both sheet "Data" and "Summary"
I have a macro that creates a data table on one sheet and a pivot table on another sheet, and the problem I'm having is that i can only run the macro once and then I have to change the sheet names to do it again. I'm trying to make it so that if the sheet name is taken, then it will add a 1,2,3,4,5 etc... at the end, depending on how many times it's run.
If anyone can help me ammend my code that would be greatly appreciated.
I was hoping to do this for both sheet "Data" and "Summary"
Code:
Sub PivotTableCreation()
'Copies Query To New Sheet
ActiveSheet.Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets.Add.Name = "Data"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, transpose:=False
' Creates Pivot Table With Dynamic Range
Rows("1:4").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
'Dynamic Range Table
ActiveSheet.ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name _
= "NewTable"
'Table Style
ActiveSheet.ListObjects("NewTable").TableStyle = "TableStyleMedium17"
'New Sheet For Pivot Table
Sheets.Add.Name = "Summary"
'Pivot Table Creation
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="NewTable", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
:="Summary!R3C1", TableName:="InsertNameHere", DefaultVersion:=xlPivotTableVersion14
End Sub
Last edited: