q; loop through all open workbooks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JIM.H. - 02 Oct 2006 00:03 GMT

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.

Reply to this Message

Roger Govier - 02 Oct 2006 08:34 GMT

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

Signature

Regards

Roger Govier

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

Reply to this Message

JIM.H. - 02 Oct 2006 13:27 GMT

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?

> Hi Jim
>
[quoted text clipped - 8 lines]
> > workbooks
> > under one file.

Reply to this Message

Dave Peterson - 02 Oct 2006 13:48 GMT

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

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

Signature

Dave Peterson

Reply to this Message