Copy specific worksheets from another book to a new book and values only

djvj

New Member
Joined
Apr 6, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hello, is there a way to copy contents from one workbook to a new one, but only keep values on some sheets and formulas and values on others?

I was hoping to do this from a different workbook. Think of it as a helper one with an export button. This button will open a workbook I tell it to and copy the specific sheets to a new one as stated above.

I'd appreciate any help on this.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
I am starting with this as I'd rather stick with an array:
VBA Code:
Sub SaveValues()
    Dim wb As Workbook
    DateString = VBA.format(Date, "yyyy-mm-dd")
    Sheets(Array("DATA", "INFO", "CONTACTS", "PARSE", "COMMANDS", "COUNTS")).Copy
    Set wb = ActiveWorkbook
    With wb
        '.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .SaveAs ThisWorkbook.Path & "\" & DateString & "_AAR_Submission", 52
    End With
End Sub

So it works great but couple things it doesn't support.
1 - Want it to work from it's own workbook and not the source or destination workbooks
2 - Want it to copy some sheets fully as it's doing now, but a couple values/formatting and no formulas
 
Upvote 0
I figured out how to do what I wanted, but I noticed when pasting formulas, it's referencing the old sheet in the formula. This causes #NAME errors in each cell.

How can I fix this?

VBA Code:
Sub CopySheetsGOOD()
    Dim NewWB As Workbook
    Dim CurrWB As Workbook
    Dim myWorksheets As Scripting.Dictionary
    Set myWorksheets = New Scripting.Dictionary
    Dim key As Variant
    
    Application.ScreenUpdating = False
    DateString = VBA.Format(Date, "yyyy-mm-dd")
    
    'Get the File
    userSelectedWorkbook = "C:\Users\admin\Documents\testbook.xlsm"
    
    DestDir = "C:\Users\sp1150\Documents\Commscope\"

    Set CurrWB = Workbooks.Open(Filename:=userSelectedWorkbook) ' open workbook

    ' Define sheets here. Set to 1 to copy formulas or 0 for values only
    myWorksheets.Add "TestSheet1", "0"
    myWorksheets.Add "TestSheet2", "1"
    myWorksheets.Add "TestSheet1", "0"
    
    Set NewWB = Workbooks.Add 'Create new workbook
    
    For Each key In myWorksheets.Keys
        CurrWB.Sheets(key).Copy After:=NewWB.Sheets(Sheets.Count)   'Copy current worksheet to new workbook after the last sheet
        If myWorksheets(key) = 0 Then
            ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value   ' copy values only
        End If
    Next
    
    Application.DisplayAlerts = False   ' suppress warning when deleting sheet
    NewWB.Sheets("Sheet1").Delete   ' remove the default "Sheet1"
    Application.DisplayAlerts = True
    CurrWB.Close SaveChanges:=False
    Application.ScreenUpdating = True

    ' Clean up
    Set CurrWB = Nothing
    Set NewWB = Nothing
 End Sub
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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
Back
Top