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

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

Jim Thomlinson


"Arlen" wrote:

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

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" wrote:

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

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

"Jim Thomlinson" wrote:

> 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.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Arlen" wrote:
>
> > 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
> >
> >
> >

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

"Mike H" wrote:

> 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" wrote:
>
> > 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
> >
> >
> >

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

--
HTH...

Jim Thomlinson


"Arlen" wrote:

> 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
>
> "Jim Thomlinson" wrote:
>
> > 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.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Arlen" wrote:
> >
> > > 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
> > >
> > >
> > >

Hi Arlen

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

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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Arlen" <(E-Mail Removed)> wrote in message news:B14ACA27-4676-422D-99E4-(E-Mail Removed)...
>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
>
>
>