Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move some sheets to a new workbook, without macros, and withoutbreaking link btwn Pivot Tables and Pivot Charts
Hi,
I have a macro containing workbook Main.xlm (Excel 2007) which creates some Pivot Tables and Pivot Charts, and I would like to save them in another workbook, let's call it Report.xlsx, without including: 1. the VBA macros 2. some other sheets which are not part of the Pivot Charts All this must be done without breaking the link among Pivot Tables and Pivot Charts, i.e. when I open Report.xlsx, I want to still be able to modify the Pivot Charts, filter, change rows, fields, etc. In Excel 2000 I just had to Move the sheets containing Pivot Tables and Pivot Charts from Main.xls to Report.xls. With Excel 2007, if I try this, the Pivot Charts become plain charts, which is really not making the internal customer happy. Can you help me? I tried the following: Code: Sub CreateReport(NonPivotSheets() As String, wbkName As String) 'Create a workbook containing all sheets except the ones in NonPivotSheets, to workbook wbkName. Dim Path As String, ReportFullName As String, MainFullName As String, MainName As String, wbk As Workbook 'Save the path and name of the Main workbook MainFullName = ThisWorkbook.FullName MainName = ThisWorkbook.Name Path = ThisWorkbook.Path ReportFullName = Path & "\" & wbkName 'Create the Report Application.DisplayAlerts = False ' I'm deleting the sheets which I don't want to move the Report, from the Main workbook: this is ' not a problem, because I'm only saving the changes in the Report workbook ActiveWorkbook.Sheets(NonPivotSheets).Delete 'I save the changes as the Report workbook: NOTE the file format option, to force 'saving in a format without macros ActiveWorkbook.SaveAs FileName:=ReportFullName, FileFormat:=xlOpenXMLWorkbook Application.DisplayAlerts = True 'I set a reference to the Report workbook Set wbk = ActiveWorkbook 'I reopen the Main workbook, that was closed by the SaveAs method before Workbooks.Open MainWbkFullName Workbooks(MainName).Activate 'I need to close the Report workbook, because otherwise Excel prompts the user to save the Report workbook in a format including macros, which I don't want wbk.Close SaveChanges:=True 'HERE is the problem: I would like to conclude execution by reopening the Report workbook and 'displaying it, but for some reasons it doesn't work, and I don't get any error message! Workbooks.Open ReportFullName End Sub So, the problem is in the last line: no matter what I do, I can't get VBA to reopen the Report workbook. Can you help me? I'm reallyn stuck this time. Thanks, Best Regards Sergio Rossi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot tables across sheets in a workbook | Excel Discussion (Misc queries) | |||
Pivot Tables to Charts | Charts and Charting in Excel | |||
Pivot Tables & Pivot Charts | Charts and Charting in Excel | |||
Pivot charts & tables | Charts and Charting in Excel | |||
Pivot Charts / Tables | Excel Worksheet Functions |