Macro to merge open workbooks

[Guest]

Is there a small macro that will take all the worksheets from all open
workbooks and put them in one new consolidated workbook?

Thanks

Barry

 

[Ron de Bruin]

See your other thread

 

[Guest]

My intranet won't let me access that site. Can you please give me the code Ron?

 

[Ron de Bruin]

See Misc group where you also post
Please use one group

Copy this macro in a new workbook and run it
Or do you want to use it in your personal.xls

Sub test()
Dim wb As Workbook
For Each wb In Application.Workbooks
If wb.Windows(1).Visible And wb.Path <> "" And wb.Name <> ThisWorkbook.Name Then
wb.Worksheets.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next
End Sub

 

[Guest]

Basic code would be:

Sub CopySheets()
Dim bk As Workbook, bk1 As Workbook
Set bk = Workbooks.Add()
For Each bk1 In Application.Workbooks
If bk1.Windows(1).Visible Then
Application.DisplayAlerts = False
bk1.Worksheets.Copy After:=bk.Worksheets(bk.Worksheets.Count)
Application.DisplayAlerts = True
End If
Next
End Sub

 

[Guest]

Yeah sorry, posted in general by mistake.

Think this works. Thank you very much for your help.