q; loop through all open workbooks

[Guest]

Hello,
How can I loop through all open workbooks and copy the first sheet in a new
file in Excel macro. I am trying to collect sheets in different workbooks
under one file.

 

[Roger Govier]

Hi Jim

Ron de Bruin has lots of code for carrying out this type of operation on
his site
http://www.rondebruin.nl/copy2.htm

 

[Guest]

Hi Roger,
Thanks for your reply. Many of these macros open the workbook and copy
cells, I have all the workbooks open I just need to loop through the open
workbook, process one by one and close one by one. Is there any example for
this?

 

[Dave Peterson]

Maybe something like:

Option Explicit
Sub testme01()

Dim wkbk As Workbook
Dim NewWkbk As Workbook

Set NewWkbk = Workbooks.Add(1) 'single sheet
NewWkbk.Worksheets(1).Name = "deletemelater"

For Each wkbk In Application.Workbooks
If wkbk.Windows(1).Visible = False Then
'skip it???
Else
If wkbk.Name = NewWkbk.Name Then
'skip this one, too
Else
wkbk.Worksheets(1).Copy _
after:=NewWkbk.Worksheets(1)
End If
End If
Next wkbk

Application.DisplayAlerts = False
NewWkbk.Worksheets("deletemelater").Delete
Application.DisplayAlerts = True
End Sub