VBA to Copy Excel Worksheet to Another Workbook without Formulas

Last updated: Nov 9, 2023

Get FREE Advanced Excel Exercises with Solutions!

In Excel, we often need to copy a worksheet to another workbook without formulas with VBA. We can use both the context menu and VBA for this. Today, we will discuss how to copy a worksheet to another workbook using VBA. We will use our VBA codes to copy single sheets and multiple worksheets.

Table of Contents hide

Copy Excel Worksheet to Another Workbook without Formulas Using VBA: 2 Examples

1. Apply VBA to Copy Single Excel Sheet to Another Workbook without Formulas

2. Copy Multiple Sheets without Formulas to New Workbook with VBA

Conclusion

1. Apply VBA to Copy Single Excel Sheet to Another Workbook without Formulas

In this example, we will apply VBA to copy a single sheet to another workbook without formulas. To explain easily, we will use a dataset that contains the sales amount of two months of some sellers and increase the percentage. In this dataset, we have used the SUBTOTAL Function to calculate the Total sales after each month.

Here, if you select Cell D11 in the dataset, you will see the formula with SUBTOTAL function in the formula bar. The same goes for Cell C11. We will try to copy this worksheet to another workbook without these formulas.

Let’s follow the steps below to learn this example.

STEPS:

In the first place, go to the Developer tab.
Select the Visual Basic option.

After that, the Visual Basic window will open.
Now, go to Insert and select Module.
Then, type the VBA code in the Module window.

Sub CopyToAnotherBook()
    ActiveSheet.Copy
    Cells.Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Here, the VBA code first copies the entire active sheet and then pastes the values of the sheet to another Excel sheet.

Click the Save icon or press Ctrl + S to save the code.
Next, press the F5 key to run the code.
To run the code from the ribbon, go to the Developer tab and select Macros.

After that, select Run from the Macro window.

Finally, the desired will be copied in another workbook and there will be no formulas in Cells C11 & D11.

2. Copy Multiple Sheets without Formulas to New Workbook with VBA

In the previous example, we demonstrated how to copy a single sheet without formulas. Here, we will try to copy multiple sheets to a new workbook. For that purpose, we are using three sheets in our workbook. Sheet 1 contains “Dataset Overview”, Sheet 2 has a Single Sheet example, and Sheet 3 stores Multiple Sheets of examples and some formulas.

Pay attention to the steps below to understand this example.

STEPS:

In the beginning, go to the Developer tab and select Visual Basic.

Then, go to Insert and select Module.
Next, type the VBA code in the Module window.

Sub MultipleSheets()
Dim newwkb As Workbook 
Dim wksht As Worksheet 
Set newwkb = Workbooks.Add(xlWBATWorksheet) 
ThisWorkbook.Worksheets.Copy After:=newwkb.Worksheets(1) 
Application.DisplayAlerts = False 
newwkb.Worksheets(1).Delete 
Application.DisplayAlerts = True 
For Each wksht In newwkb.Worksheets
wksht.UsedRange.Value = wksht.UsedRange.Value Next 
End Sub

Here, the VBA code first copies the multiple sheets from our workbook to another workbook. Then, paste the values using a For Each loop in all sheets.

Press Ctrl + S to save the code or click the Save icon.
Now, select Macros in the Developer tab.

After that, hit Run from the Macro window.

You will see the copied sheets in another workbook without formulas.

Download Practice Book

Download the practice book here.

Copy Worksheet to Another Workbook.xlsm

Conclusion

We have discussed two important examples of copying an Excel worksheet to another workbook in this article. We hope these examples will help you to solve your problems of copying any sheet without formulas. Furthermore, we have added the practice book at the beginning of the article. You can download it and practice to become an expert. Last of all, if you have any queries or suggestions, feel free to ask in the comment section.

'use can enter name and select folder for file myFile = Application.GetSaveAsFilename _ (InitialFileName:=strPathFile, _ FileFilter:="Excel Files (*.xlsx), *.xlsx", _ Title:="Select Folder and FileName to save")

'save worksheet as Excel file if a folder was selected If myFile <> False Then wsA.Copy With ActiveSheet.UsedRange .Value = .Value End With Application.ActiveWorkbook.SaveAs Filename:=myFile, FileFormat:=51, CreateBackup:=False Application.ActiveWorkbook.Close False 'confirmation message with file info MsgBox "Excel file has been created: " & vbCrLf & myFile End If

Sub Excel_file_without_formula() Dim wsA As Worksheet Dim wbA As Workbook Dim strTime As String Dim strName As String Dim strPath As String Dim strFile As String Dim strPathFile As String Dim myFile As Variant On Error GoTo errHandler Set wbA = ActiveWorkbook Set wsA = ActiveSheet strTime = Format(Now(), "yyyymmdd_hhmm") 'get active workbook folder, if saved strPath = wbA.Path If strPath = "" Then strPath = Application.DefaultFilePath End If strPath = strPath & "\" 'replace spaces and periods in sheet name strName = Replace(wsA.Name, " ", "") strName = Replace(strName, ".", "_") 'create default name for saving file strFile = strName & "_" & "As of_" & strTime & ".xlsx" strPathFile = strPath & strFile 'use can enter name and select folder for file myFile = Application.GetSaveAsFilename _ (InitialFileName:=strPathFile, _ FileFilter:="Excel Files (*.xlsx), *.xlsx", _ Title:="Select Folder and FileName to save") 'save worksheet as Excel file if a folder was selected If myFile <> False Then wsA.Copy With ActiveSheet.UsedRange .Value = .Value End With Application.ActiveWorkbook.SaveAs Filename:=myFile, FileFormat:=51, CreateBackup:=False Application.ActiveWorkbook.Close False 'confirmation message with file info MsgBox "Excel file has been created: " & vbCrLf & myFile End If exitHandler: Exit Sub errHandler: MsgBox “Could not create Excel file” Resume exitHandler End Sub

Sub CopySpecificSheetsAsValues() Dim nwk As Workbook Dim wsht As Worksheet Dim sheetNames() As Variant Dim sheetName As Variant sheetNames = Array("File 1", "File 3", "File 4") Set nwk = Workbooks.Add For Each wsht In ThisWorkbook.Worksheets If IsInArray(wsht.Name, sheetNames) Then wsht.Copy After:=nwk.Worksheets(1) End If Next wsht Application.DisplayAlerts = False nwk.Worksheets(1).Delete Application.DisplayAlerts = True For Each wsht In nwk.Worksheets wsht.UsedRange.Value = wsht.UsedRange.Value Next wsht nwk.SaveAs "C:\Users\User1\Downloads" nwk.Close SaveChanges:=False End Sub Function IsInArray _ (valueToFind As Variant, arr As Variant) As Boolean Dim element As Variant On Error GoTo ErrorHandler For Each element In arr If element = valueToFind Then IsInArray = True Exit Function End If Next element ErrorHandler: IsInArray = False End Function

4 Comments

Amjad Mar 3, 2023 at 5:48 PM

I have below code saving worskheet to PDF, I need same code but to save as excel file without formula, can you pls help:

Sub PDFActiveSheet()

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), “yyyymmdd\_hhmm”)

‘get active workbook folder, if saved
strPath = wbA.Path
If strPath = “” Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & “\”

‘replace spaces and periods in sheet name
strName = Replace(wsA.Name, ” “, “”)
strName = Replace(strName, “.”, “_”)

‘create default name for savng file
strFile = strName & “_” & “As of_” & strTime & “.pdf”
strPathFile = strPath & strFile

‘use can enter name and
‘ select folder for file
myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
FileFilter:=”PDF Files (*.pdf), *.pdf”, _
Title:=”Select Folder and FileName to save”)

‘export to PDF if a folder was selected
If myFile “False” Then
wsA.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
openAfterPublish:=False
‘confirmation message with file info
MsgBox “PDF file has been created: ” _
& vbCrLf _
& myFile
End If

exitHandler:
Exit Sub
errHandler:
MsgBox “Could not create PDF file”
Resume exitHandler
End Sub
- Reply
  Arin Islam Mar 9, 2023 at 11:59 AM
  Hello Amjad,
  To use the same code but save it as an Excel file without formulas you need to have some minor changes in the code.
  • To create a name for saving the new Excel file use the following code. Here, we changed the “.pdf” part to “.xlsx” as you want to save it as an Excel file instead of a PDF.
```
'create default name for saving file
strFile = strName & "_" & "As of_" & strTime & ".xlsx"
strPathFile = strPath & strFile
```
  • Then, to select a folder for the file use the following code. Here, we changed the FileFilter to “Excel Files (*.xlsx), *.xlsx” for the Excel file.
```
'use can enter name and select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
    FileFilter:="Excel Files (*.xlsx), *.xlsx", _
    Title:="Select Folder and FileName to save")
```
  • Finally, save the worksheet as an Excel file using the following code. Here, we used the ActiveSheet.UsedRange.Value property which will copy only the values in the used range.
```
'save worksheet as Excel file if a folder was selected
If myFile <> False Then
    wsA.Copy
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
    Application.ActiveWorkbook.SaveAs Filename:=myFile, FileFormat:=51, CreateBackup:=False
    Application.ActiveWorkbook.Close False
    'confirmation message with file info
    MsgBox "Excel file has been created: " & vbCrLf & myFile
End If
```
  • After employing these changes, your code may look like the following one.
```
Sub Excel_file_without_formula()
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant

On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd_hhmm")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
    strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for saving file
strFile = strName & "_" & "As of_" & strTime & ".xlsx"
strPathFile = strPath & strFile

'use can enter name and select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
    FileFilter:="Excel Files (*.xlsx), *.xlsx", _
    Title:="Select Folder and FileName to save")
'save worksheet as Excel file if a folder was selected
If myFile <> False Then
    wsA.Copy
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
    Application.ActiveWorkbook.SaveAs Filename:=myFile, FileFormat:=51, CreateBackup:=False
    Application.ActiveWorkbook.Close False
    'confirmation message with file info
    MsgBox "Excel file has been created: " & vbCrLf & myFile
End If

exitHandler:
Exit Sub
errHandler:
MsgBox “Could not create Excel file”
Resume exitHandler
End Sub
```
  We hope this will solve your problem. Please let us know if you face any further problems.
  
  Regards,
  Arin Islam,
  ExcelDemy
Narasimhan S Jun 1, 2023 at 7:26 PM

Sub MultipleSheets()
Dim newwkb As Workbook
Dim wksht As Worksheet
Set newwkb = Workbooks.Add(xlWBATWorksheet)
ThisWorkbook.Worksheets.Copy After:=newwkb.Worksheets(1)
Application.DisplayAlerts = False
newwkb.Worksheets(1).Delete
Application.DisplayAlerts = True
For Each wksht In newwkb.Worksheets
wksht.UsedRange.Value = wksht.UsedRange.Value Next
End Sub

The above code works fine. But i want only specific sheets by name to be copied as values ( not all )
- Reply
  Afrina Nafisa Jun 4, 2023 at 3:15 PM
  Hello, NARASIMHAN S
  
  Please find the below code to get only specific sheets by name.
```
Sub CopySpecificSheetsAsValues()
   Dim nwk As Workbook
   Dim wsht As Worksheet
   Dim sheetNames() As Variant
   Dim sheetName As Variant
   sheetNames = Array("File 1", "File 3", "File 4")
   Set nwk = Workbooks.Add
For Each wsht In ThisWorkbook.Worksheets
If IsInArray(wsht.Name, sheetNames) Then
wsht.Copy After:=nwk.Worksheets(1)
       End If
   Next wsht
   Application.DisplayAlerts = False
   nwk.Worksheets(1).Delete
   Application.DisplayAlerts = True
   For Each wsht In nwk.Worksheets
       wsht.UsedRange.Value = wsht.UsedRange.Value
   Next wsht
   nwk.SaveAs "C:\Users\User1\Downloads"
   nwk.Close SaveChanges:=False
End Sub

Function IsInArray _
(valueToFind As Variant, arr As Variant) As Boolean

   Dim element As Variant
   On Error GoTo ErrorHandler
   For Each element In arr
       If element = valueToFind Then
           IsInArray = True
           Exit Function
       End If
   Next element
ErrorHandler:
   IsInArray = False
End Function
```
  Note: Change the Path Id and the sheet name according to your convenience.

VBA to Copy Excel Worksheet to Another Workbook without Formulas

Copy Excel Worksheet to Another Workbook without Formulas Using VBA: 2 Examples

1. Apply VBA to Copy Single Excel Sheet to Another Workbook without Formulas

2. Copy Multiple Sheets without Formulas to New Workbook with VBA

Conclusion

Related Articles

What is ExcelDemy?

Leave a reply Cancel reply