Cycle through all worksheets in a workbook

[Arlen]

I have some code which automatically copies and pastes data off all workbooks
(1 sheet each) in a folder.

How about if I want to make it cycle through multiple worksheets within a
workbook before closing it?

I made a few modifications to this loop, but it is not working. Anyone know
why?

Do While sFil <> ""
Workbooks.Open sPath & sFil
For Each w In ActiveWorkbook.Worksheets
<---added this
With ThisWorkbook.Worksheets("Bulk")
<---and this
k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row
n = k + 4
Set oWbk = Workbooks.Open(sPath & "\" & sFil)
Range("B4:Z1000").Copy
Set w = ThisWorkbook.Sheets(1)
ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial
End With
<---added this
Next w
<---and this
oWbk.Close True <--should this be False

I appreciate your help as always.

Arlen

 

[Jim Thomlinson]

I am not sure what you are doing with this line
Set w = ThisWorkbook.Sheets(1)
since it is inside of this loop
For Each w In ActiveWorkbook.Worksheets
'
'
next w

Genearlly speaking your loop is going to move through all of the worksheets
in the activeworkbook but not if you change the reference to w within the
loop.

 

[Mike H]

Hi,

Several ways, here's one

Dim x As Long
For x = 1 To Worksheets.Count
MsgBox Worksheets(x).Name
'do something
Next


Mike

 

[Arlen]

Jim,

I apologize for the confusion. I found the good cycling code from one macro
and combined it with the good copy/pasting of another. Here is the whole
thing, and again, it will go through a single sheet and close the book just
fine, but it won't cycle through multiple worksheets before closing the book
and moving on.

Sub Cycler()
Dim oWbk As Workbook
Dim w As Worksheet
Dim sFil As String
Dim sPath As String
Dim k As Long, n As Long
sPath = "C:\Documents and Settings\gl1b\Desktop\ExcelStuff\2008\Tacoma\Bulk"
ChDir sPath
sFil = Dir("*.xls") 'change or add formats
Application.DisplayAlerts = False
k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row
n = k + 1
Do While sFil <> ""
For Each w In ActiveWorkbook.Worksheets
k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row
n = k + 1
Set oWbk = Workbooks.Open(sPath & "\" & sFil)
Range("B4:Z1000").Copy
ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial
Next w
oWbk.Close True
sFil = Dir
Loop
Application.DisplayAlerts = True
End Sub

I know not what is wrong. I only understand people's explanations.

Thank you for your effort on my behalf.

Arlen

 

[Arlen]

Mike,

Thanks for helping. I tried this code and I still get only one sheet at a
time.

I'll keep playing with it.

Arlen

 

[Jim Thomlinson]

You have a copule of problems.
1 - Each time you go from 1 sheet to the next you try to open a new workbook.
2 - You do not explicitly reference the workbook you are dealing with which
is very important when accessing multiple books...

Sub Cycler()
Dim oWbk As Workbook
Dim w As Worksheet
Dim sFil As String
Dim sPath As String

sPath = "C:\Documents and Settings\gl1b\Desktop\ExcelStuff\2008\Tacoma\Bulk"
ChDir sPath
sFil = Dir("*.xls") 'change or add formats
Application.DisplayAlerts = False
Do While sFil <> ""
Set oWbk = Workbooks.Open(sPath & "\" & sFil)
For Each w In oWbk .Worksheets
w.Range("B4:Z1000").Copy Destination:= _
ThisWorkbook.Sheets(1).cells(rows.count, "A").end(xlup).offset(1,0)
Next w
oWbk.Close False 'don't save
sFil = Dir
Loop
Application.DisplayAlerts = True
End Sub

 

[Ron de Bruin]

Hi Arlen

See
http://www.rondebruin.nl/fso.htm

Or use the add-in
http://www.rondebruin.nl/merge.htm