excel - how to copy all sheets from one workbook to another

i have a workbook called "test". I want to copy all of the sheets in a workbook called "estimate" to test. i dont know how to create the list of sheets on "estimate" that can be copied to "test". Is there a way to do that in VBA?

0

2 Answers

Consider:

Sub dural() Dim b1 As Workbook, b2 As Workbook Dim sh As Worksheet Workbooks.Open Filename:="C:\TestFolder\test.xls" Set b1 = ActiveWorkbook Workbooks.Open Filename:="C:\TestFolder\estimate.xls" Set b2 = ActiveWorkbook For Each sh In b2.Sheets sh.Copy after:=b1.Sheets(b1.Sheets.Count) Next sh
End Sub

0

This will list them

Sub list()
Dim wbEstimate As Workbook
Set wbEstimate = ThisWorkbook
Dim wsList As Worksheet
Dim numSheets As Integer
numSheets = wbEstimate.Sheets.Count
Worksheets.Add(After:=Worksheets(numSheets)).Name = "list"
Set wsList = Worksheets("list")
Dim i As Integer
i = 1
For Each Sheet In wbEstimate.Worksheets wsList.Cells(i, 1) = Sheet.Name i = i + 1
Next
End Sub

And this should copy them if they are both open

Sub list()
Dim wbEstimate As Workbook
Set wbEstimate = ThisWorkbook
Dim wbTest As Workbook
Set wbTest = Workbooks("test.xlsx")
For Each Sheet In wbEstimate.Sheets Sheet.Copy after:=wbTest.Sheets(wbTest.Sheets.Count)
Next
End Sub

And if you need to open the test one before you copy sheets, do it like this -

Sub list()
Dim wbEstimate As Workbook
Set wbEstimate = ThisWorkbook
Dim wbTest As Workbook
Set wbTest = Workbooks.Open("C:\Users\path\to\test.xlsx")
For Each Sheet In wbEstimate.Sheets Sheet.Copy after:=wbTest.Sheets(wbTest.Sheets.Count)
Next
End Sub

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy