Forum Discussion
VBA coding to auto a sheet using Pivot Tables
wsNew.Name = "MG" + Worksheets("Pivot Table").Range("A5")
You are trying to assign the name of the new sheet to be the same value each loop
- Yes, I see the issue.. is there a way to take this out of the loop? I still want the new worksheet created to be named after what is in cell A5.
- So I really don't know exactly what you are doing and have to ask if maybe you should be using a completely different approach like a Pivot Table or Power Query or even just using some of the new functions with dynamic arrays like FILTER()
mtarler So what I am trying to do is pull information from the pivot table that I have created. The information in cells A5, B5, C5,D5 and so on all change every time you make a different selection on the slicers. What I want this code to do is essentially " copy and paste" the information from the pivot table to the other sheet I showed to specific cells but if I use a simple copy and paste code it does not update the other sheet when I make different selections on the sliders. Basically I want to take the pivot table information and have it autofill the other sheet within specific cells.
Phildyche I really don't understand what you are trying to do. I thought you wanted to create multiple new sheets but maybe not. Here is the "loop"
For x = 1 To rng.Rows.Count Debug.Print ShName templatews.Copy Before:=templatews Set wsNew = Sheets(templatews.Index - 1) wsNew.Name = "MG" + Worksheets("Pivot Table").Range("A5") wsNew.Range("D21").Value = Worksheets("Pivot Table").Range("C5") Next xso you are looping based on the number of rows and doing:
1) printing ShName to the debug window (not sure why)
2) create a copy of the template sheet
3) assign wsNew to the newly created sheet
4) re-name that new sheet to "MG ..." (this is the problem because after you create the 1st sheet with that name you can't name the 2nd new sheet the same name)
5) assign cell D21 the value found on the Pivot Table sheet in cell C5 (not sure why)
IF you only want ONE new sheet then move items 1-4 (lines 2-5 in the code block above) to be above the FOR statement. I have no idea what item 5 is doing so can't speak to that except that because the cells are 'fixed' it won't matter how many lines you have it will keep copying the same value over.
