Solved

Name workbook

Posted on 2015-02-12
3
61 Views
Last Modified: 2015-02-12
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
Comment
Question by:Marcell999
3 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40605473
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40605476
You need to add a:
ThisWorkbook.SaveAs "your new filename and path"

Open in new window

line to the end.
0
 

Author Closing Comment

by:Marcell999
ID: 40605484
Thanks Phillip!!! You are great!!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now