q; loop through all open workbooks

J

JIM.H.

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.
 
J

JIM.H.

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?
 
D

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
 
Top