Solved: Copy Worksheets from one Workbook to Another

[asystole0]

Hi All

First Post on here, hope someone can help!

Im creating a project which has lots of separate spreadsheets, rolling these into 1 isn't feasible because of the amount. So on that i need to copy all the contents of all the tabs from a closed document into the current one which is open.

I found this at microsoft website but this copies out of my document not into.


Sub Mover3()
Dim BkName As String
Dim NumSht As Integer
Dim BegSht As Integer

'Starts with second sheet - replace with index number of starting sheet.
BegSht = 2
'Moves two sheets - replace with number of sheets to move.
NumSht = 2
BkName = ActiveWorkbook.Name

For x = 1 To NumSht
'Moves second sheet in source to front of designated workbook.
Workbooks(BkName).Sheets(BegSht).Move _
Before:=Workbooks("Test.xls").Sheets(1)
'In each loop, the next sheet in line becomes indexed as number 2.
'Replace Test.xls with the full name of the target workbook you want.
Next
End Sub





There may be an easier way to do this so any feedback would be really appreciated.

[mdmackillop]

Welcome to VBAX
This assumes both books are in the same folder. Change file name to suit

Sub DoCopy()
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim Sht As Worksheet

Set wbTarget = ThisWorkbook
Set wbSource = Workbooks.Open(wbTarget.Path & "\Closed.xls")
For Each Sht In wbSource.Sheets
Sht.Copy before:=wbTarget.Sheets(1)
Next
wbSource.Close
End Sub