Forum Discussion
Combing workheets into one workboolk
Help please. I have 150 separate worksheets that I need to combine into one document so I can convert to Adobe PDF and upload them to Indesign. Need to stay separate pages, but don't want to have to input each separately.
Someone else actually had a question and a comment that worked, but I did not save the instructions and now I can't remember how to do it.
The first time I did it, it automatically made page numbers that I do not want. (Prefer to create the page numbers with InDesign. Any help greatly appreciated.
Place all the workbooks that you want to combine in the same folder, without other workbooks.
I'll assume that each workbook has one worksheet (or if it has multiple sheets, that you want the first one).
Run this macro. With 150 workbooks, it'll take a while.
Sub CombineWorkbooks() Dim strFolder As String Dim strFile As String Dim wbkSource As Workbook Dim wbkTarget As Workbook With Application.FileDialog(4) ' msoFileDialogFolderPicker If .Show Then strFolder = .SelectedItems(1) Else Beep Exit Sub End If End With Application.ScreenUpdating = False Application.DisplayAlerts = False Set wbkTarget = Workbooks.Add(Template:=xlWBATWorksheet) If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\" End If strFile = Dir(strFolder & "*.xls*") Do While strFile <> "" Set wbkSource = Workbooks.Open(Filename:=strFolder & strFile) wbkSource.Worksheets(1).Copy After:=wbkTarget.Worksheets(wbkTarget.Worksheets.Count) wbkSource.Close SaveChanges:=False strFile = Dir Loop wbkTarget.Worksheets(1).Delete Application.DisplayAlerts = True Application.ScreenUpdating = True End SubHansVogelaar Thanks but I don't understand any of that. I am not familiar with Excel. There was a simple way to combine them that did not include anything like that. As I said, I read it somewhere and it worked, but now I don't remember that I did.
I hope that someone else will have a better suggestion for you.
