• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

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
0
Marcell999
Asked:
Marcell999
1 Solution
 
Phillip BurtonCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now