Copy list of workbooks ( one sheet only) to one workbook in tab number order

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jul 17, 2014

• #2

What's your "formula"?

 

Upvote 0

D

David123456

New Member

Joined

Jul 16, 2014

Messages

48

• Jul 17, 2014

• #3

Hi Andrew

Firstly thanks for replying.

The formula was on my original thread on my first request.

Also could you recommend any books just starting to get going with Macros. Enjoying it and don't mind putting in the hard work.

Thanks

David

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jul 17, 2014

• #4

That code doesn't compile.

For sorting worksheets see:

Sorting And Ordering Worksheets

I like John Walkenbach's Power Programming with VBA books:

The Spreadsheet Page - Excel Books by John Walkenbach

 

Upvote 0

D

David123456

New Member

Joined

Jul 16, 2014

Messages

48

• Jul 17, 2014

• #5

Thanks Andrew.

The formula I provide above does put the worksheets in the workbook automatically and on individual tabs.

Just needed to know what I needed to include in the formula to make sure it copies the worksheets in order in individual tabs. But also names the tabs.

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jul 17, 2014

• #6

If you post some code that compiles I will see if I can amend it for you.

 

Upvote 0

D

David123456

New Member

Joined

Jul 16, 2014

Messages

48

• Jul 17, 2014

• #7

Hi Andrew,

Can send direct from my own E Mail. As I cannot copy and paste in this version.

I can mail you my E Mail

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jul 17, 2014

• #8

Copy the code in the Visual Basic Editor and paste it into a reply, surrounded by Code tags.

 

Upvote 0

D

David123456

New Member

Joined

Jul 16, 2014

Messages

48

• Jul 17, 2014

• #9

Sub GetSheets()
Path = "Y:"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jul 17, 2014

• #10

Try:

Code:

Sub GetSheets()
    Path = "Y:"
    Filename = Dir(Path & "*.xls")
    Do While Filename <> ""
        Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
        ActiveWorkbook.Worksheets(1).Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        ActiveSheet.Name = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
        Workbooks(Filename).Close
        Filename = Dir()
    Loop
End Sub

 

Upvote 0