Duplicating tabs to new workbook but retaining data

[stroffso]

Hi,

I am wanting to copy 7 tabs to a new workbook on the click of a button. I have the below code which works mostly ok there are just a few issues.

1. The source data has filtered pivot tables in it and when it exports it exports all the pivot data rather then just what was filtered down
2. There are some hidden rows and columns in the source data but when I export it they dont go across as hidden
3. Some pivot tables are converting back to non pivot areas and as a result throwing off Legends on the charts that are there
4. Some merged cells are unmerging

I am guessing the main issue is the coding which says pasted values. Is there a way around this, also would i better doing the tabs one by one as each tab has a different set up, as in one might have charts and another might be just pivots.

Sub Newsletter()
    Dim FP As String
    Dim NewName As String
    Dim nm As Name
    Dim ws As Worksheet

    FP = "D:\Users\R45454\Desktop\Lance\Reporting\"
  Call Refershall
    'If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
   ' "New sheets will be pasted as values, named ranges removed" _
   ' , vbYesNo, "NewCopy") = vbNo Then Exit Sub
     Application.DisplayAlerts = False
    With Application
        .ScreenUpdating = False
         
       
        On Error GoTo ErrCatcher
        Sheets(Array("Report1", "Report3", "Report4", "Report5", "Report3=6", "Report7", "Report2")).Copy
        On Error GoTo 0
         
         '       Paste sheets as values
         '       Remove External Links, Hperlinks and hard-code formulas
         '       Make sure A1 is selected on all sheets
        For Each ws In ActiveWorkbook.Worksheets
            ws.Cells.Copy
           ws.[A1].PasteSpecial Paste:=xlValues
           
            ws.Cells.Hyperlinks.Delete
            Application.CutCopyMode = False
            Cells(1, 1).Select
            ws.Activate
        Next ws
        Cells(1, 1).Select
         
         '       Remove named ranges
      '  For Each nm In ActiveWorkbook.Names
         '   nm.Delete
       ' Next nm
         
         '       Input box to name new file
        NewName = InputBox("Please Specify the name of your new workbook", "Newsletter Export")
         
    
        ActiveWorkbook.SaveAs Filename:=FP & NewName & ".xlsx"
        ActiveWorkbook.Close SaveChanges:=False
         
        .ScreenUpdating = True
    End With
    MsgBox "Newsletter has exported successfully"
    Exit Sub
     
ErrCatcher:
    MsgBox "Specified sheets do not exist within this workbook"
     Application.DisplayAlerts = True
End Sub

The file needs to export to an XLSX file as someone else will need to edit it afterwards. All help greatly appreciated

 

[ajetrumpet]

I might be able to say something useful here stroff....

2. There are some hidden rows and columns in the source data but when I export it they dont go across as hidden

I would guess this happens because hidden parts of a worksheet are not part of the actual underlying *structure* or *architecture* of the worksheet. Similar to the concept of the low-level struct called the *table definition (tbldef)*, which serves as the foundation for the table object in MS Access.

4. Some merged cells are unmerging

that doesn't sound right at all. I would assume it should be either *all cells keep their merge* or *all cells become unmerged*. Are there any differences between the sets of merged cells that are in the source data that you make a distinguishment about? Those differences might be the cause of this problem, if it is indeed a problem.

I am guessing the main issue is the coding which says pasted values.

I'm not sure about that stroff. I ran a test on a file of mine, and the merged cells were pasted as still merged:

Is there a way around this, also would i better doing the tabs one by one as each tab has a different set up, as in one might have charts and another might be just pivots.

I would really doubt that it would make a difference whether you ran the code as a batch process like you're doing or a one-by-one operation. Because each iteration is a separate process in it's own right and pretty much devoid of anything it could possibly *remember* from a previous iteration. I apologize too, I can't say anything about the use of pivot tables and charts. I've never used a pivot *anything*, and probably never will. Being a source coder, I've always found other solutions than those. But many people love them....