0

I am trying to achieve the goal of automating a support template. The function I am working on is creating a button or drop down selection button that will auto pivot from the excel table to the desired pivot tables. The code below is creating a pivot table on a sheet that already exists. I am trying to figure out how my macro can be changed to where there is no sheets made (other than the one with the excel table on it) before running to macro and once it's ran it will create the new sheet and pivot table automatically. This is were I am stuck at. I tried record these steps but if I made changes to the code it would break. 

 Sub SelfPivot()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng1 As Range


    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Data insert")
    Set rng1 = ws.Cells.Find("*", ws.[a1], xlFormulas, , , xlPrevious)
    Sheets("Data insert").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Data insert!R1C1:R" & CStr(rng1.Row) & "C" & CStr(rng1.Column), Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Reclass_PushBack!R3C1", TableName:="ReclassPORequesters", DefaultVersion _
        :=xlPivotTableVersion10
    Sheets("Reclass_PushBack").Select
    Cells(3, 1).Select
    End Sub
FinanceGuy
  • 15
  • 10
  • Can you also add your attempted code, so we can see what changes you made? – Mistella Jun 25 '18 at 20:58
  • I ended up deleting it for I made too many changes that I found it somewhat not fixable. Does not having it hinder my chances of someone answer my questions? – FinanceGuy Jun 25 '18 at 21:04
  • Probably not. Although, I would appreciate a better understanding of how familiar you are to adding a new Worksheet to a Workbook. Is the problem only that you had trouble adding the pivotTable to the new worksheet instead of a pre-existing one, or are you having trouble with adding the new sheet as well? – Mistella Jun 25 '18 at 21:06
  • I am a novice VBA user so I am coming into this task somewhat blind. I know how to manually add sheet on excel but not how to write my own marco to do that task. – FinanceGuy Jun 25 '18 at 21:12
  • I am having a problem with creating the sheets with a pivot table on it. – FinanceGuy Jun 25 '18 at 21:13

0 Answers0