Copy sheets from closed workbook

BarneyLTD

New Member
Joined
Sep 27, 2017
Messages
26
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.

Code:
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 :)
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,991
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
When you say "pull the whole sheet", do you mean actually copy the worksheets, or just the data from the worksheets?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,991
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Then you can replace this:

Code:
SrcWkb.Sheets(1).UsedRange.Copy ThisWorkbook.ActiveSheet.Range("A1")
with something like this:

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,991
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Glad to help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,378
Messages
5,468,265
Members
406,576
Latest member
mbehring

This Week's Hot Topics

Top