Name workbook

Hi,

How can I create a name for the new workbook using the below code. See attached file. See code


Sub Button1_Click()

  Dim Pivot_trans As Boolean
  Dim ResultSheet
 Dim PivotSheet
     With Sheets.Add
         Sheets("Pivot (3)").PivotTables(1).TableRange2.Copy .Range("A1")
         .Move
        Set PivotSheet = ActiveSheet
         With ActiveSheet.PivotTables(1)
             Pivot_trans = .ColumnGrand
             .ColumnGrand = True
             .Parent.Cells.SpecialCells(xlCellTypeLastCell).ShowDetail = True
            Set ResultSheet = ActiveSheet
             .ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, Selection, xlPivotTableVersion14)
             .ColumnGrand = Pivot_trans
             .Parent.Activate
         End With
            ResultSheet.Columns("H:K").Delete
             ResultSheet.Columns("D").Delete
           Application.DisplayAlerts = False
            PivotSheet.Delete
            Application.DisplayAlerts = True
     
     ResultSheet.Name = "hello"
 
     
     End With


 End Sub




------------------
Regards,

Marcell
Tracker-1-.xlsm
Marcell999Asked:
Who is Participating?
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
You would have to save the resulting spreadsheet - you cannot name it otherwise.

Try

Sub Button1_Click()
 
  Dim Pivot_trans As Boolean
  Dim ResultSheet
Dim PivotSheet
Dim ResultWorkbook
     With Sheets.Add
         Sheets("Pivot (3)").PivotTables(1).TableRange2.Copy .Range("A1")
         .Move
        Set PivotSheet = ActiveSheet
       Set ResultWorkbook = ActiveWorkbook
         With ActiveSheet.PivotTables(1)
             Pivot_trans = .ColumnGrand
             .ColumnGrand = True
             .Parent.Cells.SpecialCells(xlCellTypeLastCell).ShowDetail = True
            Set ResultSheet = ActiveSheet
             .ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, Selection, xlPivotTableVersion14)
             .ColumnGrand = Pivot_trans
             .Parent.Activate
         End With
            ResultSheet.Columns("H:K").Delete
             ResultSheet.Columns("D").Delete
           Application.DisplayAlerts = False
            PivotSheet.Delete
            Application.DisplayAlerts = True
   
     ResultSheet.Name = "hello"
    ResultWorkbook.SaveAs "hellothere"
     End With
 
 
End Sub
 
 Alternatively, use ResultWorkbook.Save if you want to bring up a dialog box.
0
 
Rory ArchibaldCommented:
You need to add a:
ThisWorkbook.SaveAs "your new filename and path"

Open in new window

line to the end.
0
 
Marcell999Author Commented:
Thanks Phillip!!! You are great!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.