VBA to Copy Excel Worksheet to Another Workbook without Formulas

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.


Download Practice Book

Download the practice book here.


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

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.
  • Secondly, 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 paste 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: VBA Copy Sheet in Excel (13 Quick Ways)


Similar Readings:


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

In the previous example, we demonstrated to copy a single sheet without formulas. Here, we will try to copy multiple sheets to another workbook. For that purpose, we are using three sheets in our workbook. Sheet 1 contains Dataset Overview, Sheet 2 has Single Sheet example, and Sheet 3 stores Multiple Sheets example 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.

  • Lastly, you will see the copied sheets in another workbook without formulas.

Copy Multiple Sheets without Formulas to New Workbook with VBA

Read More: Excel VBA to Copy Multiple Sheets to New Workbook 


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

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo