VBA to Copy Excel Worksheet to Another Workbook without Formulas

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.


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

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.

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

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.

Apply VBA to Copy Single Excel Sheet to Another Workbook without 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.

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

  • 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

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

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.

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

  • After that, select Run from the Macro window.

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

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

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

Read More: How to Copy Worksheet to Another Workbook Using VBA


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

Copy Multiple Sheets without Formulas to New Workbook with VBA

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.

Copy Multiple Sheets without Formulas to New Workbook with VBA

Read More: VBA to Copy Worksheet to Another Workbook and Rename


Download Practice Book

Download the practice book here.


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

4 Comments
  1. 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

    • 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

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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo