Copy sheets from closed workbook

[BarneyLTD]

Hi all, back again with another ask.

I have tried the below code (with a slight mod to try and pull the whole sheet rather than the range) to try and call up a workbook (name changes so need to be able to select from file explorer), copy sheets 1-4 and paste in the original workbook that the macro was run from, at the end. this can be referenced if needed and is called List Creator v2.xlsm - can anyone help out? I can't get it to work.

Sub CopySheet()Dim FileFilter As String
Dim FileName As String
Dim SrcWkb As Workbook
FileFilter = "All Excel Files,*.xla;*.xls;*.xlt,All Files, *.*"
FileName = Application.GetOpenFilename(FileFilter)
If FileName = "False" Then Exit Sub
Set SrcWkb = Workbooks.Open(FileName)
SrcWkb.Sheets(1).UsedRange.Copy ThisWorkbook.ActiveSheet.Range("A1")
SrcWkb.Close False
End Sub

Cheers

 

[RoryA]

When you say "pull the whole sheet", do you mean actually copy the worksheets, or just the data from the worksheets?

 

[BarneyLTD]

apologies, copy the actual worksheets.

 

[RoryA]

Then you can replace this:

SrcWkb.Sheets(1).UsedRange.Copy ThisWorkbook.ActiveSheet.Range("A1")

with something like this:

SrcWkb.Sheets(Array(1, 2, 3, 4)).Copy Before:=ThisWorkbook.Sheets(1)

 

[BarneyLTD]

works perfectly! thanks RoryA

 

[RoryA]

Glad to help.