How to copy sheets to another workbook using vba?

by

I would like to slightly rewrite keytarhero’s response:

Sub CopyWorkbook()

Dim sh as Worksheet,  wb as workbook

Set wb = workbooks("Target workbook")
For Each sh in workbooks("source workbook").Worksheets
   sh.Copy After:=wb.Sheets(wb.sheets.count) 
Next sh

End Sub

Edit: You can also build an array of sheet names and copy that at once.

Workbooks("source workbook").Worksheets(Array("sheet1","sheet2")).Copy _
         After:=wb.Sheets(wb.sheets.count)

Note: copying a sheet from an XLS? to an XLS will result into an error. The opposite works fine (XLS to XLSX)

More Related Contents:

  1. Excel Number Separation [closed]
  2. excel vba compare & copy worksheets from 2 excel files
  3. VBA Stuck after certain loop
  4. How to avoid using Select in Excel VBA
  5. Is the . in .Range necessary when defined by .Cells?
  6. How to return a result from a VBA function
  7. Cycle through sub-folders and files in a user-specified root directory [duplicate]
  8. Convert Text to Date?
  9. Loop through each cell in a range of cells when given a Range object
  10. Checking for #N/A in Excel cell from VBA code
  11. Non-web SQL Injection
  12. Error: “Could Not Find Installable ISAM”
  13. Autofilter with column formatted as date
  14. Excel vba refresh wait
  15. How to delete multiple rows without a loop in Excel VBA
  16. Excel vba to create every possible combination of a Range
  17. Excel VBA: getting row of clicked button [duplicate]
  18. Excel VBA Filter Change event handler
  19. How to copy only values in excel vba from a range?
  20. How to suppress Update Links warning?
  21. How do we use restful APIs from Excel macros (vba)?
  22. Run a Macro from Another Workbook
  23. Cannot VBA write data to cells in Excel 2007/2010 within a function
  24. Sorting a dictionary by key in VBA
  25. Handle “No cells were found” error when filtered range is empty
  26. Excel: the Incredible Shrinking and Expanding Controls [closed]
  27. How to send email to multiple recipients with addresses stored in Excel?
  28. Generate Word Documents (in Excel VBA) from a series of Document Templates
  29. Using Excel VBA Macro To Capture + Save Screenshot of Specific Area In Same File
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment