Duplicating tabs to new workbook but retaining data

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
126
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
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:

cells_pasted_as_merged-jpg.3810


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....
 

Attachments

  • cells_pasted_as_merged.jpg
    cells_pasted_as_merged.jpg
    82.7 KB · Views: 16

Watch MrExcel Video

Forum statistics

Threads
1,127,972
Messages
5,627,933
Members
416,282
Latest member
fchagas97

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top