Looop through many sheets
 

Hey all,

I have a workbook which contains many worksheets the first of which i
always called '01' and the
last which is always called 'Reference'. I also have a second workboo
called 'Test'.

What I need is a macro that scrolls through all the sheets in the firs
book and copies
the data from the last row to paste into a s sheet in Test so all thes
rows can be added together.

I have this (which doesn't work):

Sub NewQCM55()
Dim owksheet As Worksheet
For Each owksheet In Worksheet
Range("A8").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Windows("test.xls").Activate
Sheets("New_Jersey").Select
Range("B1").Select
ActiveSheet.Paste
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Windows("New_jersey_agent.xls").Activate
Next owksheet
End Sub



But my main issue is that I can't get the macro to move past the firs
sheet it is copying data from,
I know I can do this in a For loop but I would really aprieciate
pointer in the right direction.

Cheers

--
Hru4
-----------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...fo&userid=2489
View this thread: http://www.excelforum.com/showthread.php?threadid=55400

Looop through many sheets
 

first
For Each owksheet In Worksheet
For Each owksheet In WorksheetS

try something like this. UNtested.
You may be overwriting by copying the currentregion to one cell???

Sub NewQCM55()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Range("a8").CurrentRegion.Copy
workbooks("test.xls").sheets("New_Jersey").Range(" B1")
workbooks("test.xls").sheets("New_Jersey").Rows(1) .Insert
Next ws
End Sub


--
Don Guillett
SalesAid Software

"Hru48" wrote in
message ...

Hey all,

I have a workbook which contains many worksheets the first of which is
always called '01' and the
last which is always called 'Reference'. I also have a second workbook
called 'Test'.

What I need is a macro that scrolls through all the sheets in the first
book and copies
the data from the last row to paste into a s sheet in Test so all these
rows can be added together.

I have this (which doesn't work):

Sub NewQCM55()
Dim owksheet As Worksheet
For Each owksheet In Worksheet
Range("A8").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Windows("test.xls").Activate
Sheets("New_Jersey").Select
Range("B1").Select
ActiveSheet.Paste
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Windows("New_jersey_agent.xls").Activate
Next owksheet
End Sub



But my main issue is that I can't get the macro to move past the first
sheet it is copying data from,
I know I can do this in a For loop but I would really aprieciate a
pointer in the right direction.

Cheers.


--
Hru48
------------------------------------------------------------------------
Hru48's Profile:
http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=554007

Looop through many sheets
 

It's grand as long as you enjoy making selections to slow down your code.

--
Don Guillett
SalesAid Software

"Hru48" wrote in
message ...

Thanks thats grand!


--
Hru48
------------------------------------------------------------------------
Hru48's Profile:
http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=554007

Looop through many sheets
 

Hi Hru48,

Try this, it should run faster b/c it's not made slower by screen
updating or making selections (as Don mentioned)...

Sub ModifiedSeeSheetNames()
Dim ws As Worksheet
Dim CopyRange As Range
Dim PasteRange As Range
Dim i As Long
Dim LastRow As Long

Set PasteRange =
Workbooks("test.xls").Sheets("New_Jersey").Range(" b1")
For Each ws In Workbooks("New_jersey_agent.xls").Worksheets '*
With ws
If .Name = "Summary" Or .Name = "Reference" Then GoTo ByPass
LastRow = .Cells(Rows.Count, "a").End(xlUp).Row
Set CopyRange = _
..Range(.Range("A" & LastRow), .Range("A" & LastRow).End(xlToRight))
If CopyRange.Columns.Count = .Columns.Count Then _
Set CopyRange = .Cells(CopyRange.Row, 1) '**
End With
CopyRange.Copy PasteRange.Offset(i, 0) '***
i = i + 1
ByPass:
Next ws
Set CopyRange = Nothing
Set PasteRange = Nothing

End Sub

Comments:
'*I have worded it like this to be precise in selection. However, if
the macro is to be used to copy more than one file change it to
something like: "For Each ws In ActiveWorkbook.Worksheets"
'**This is to prevent pasting errors if there is only data in column A
if this is the case the CopyRange becomes the whole row and a whole row
can't be pasted into column B of the other file.
Also, I'm just learning too & am not sure if the CopyRange needs to be
"Set" everytime or not (does anyone know?) - it works though so I
haven't changed it.
'***This assumes it doesn't matter what the order is when the rows are
added to the "test" file therefore new info is added to the bottom
rather than by inserting at the top (as in original macro).

Don,
just curious, why knock it when you could share some knowledge & offer
an alternative. Do you have any suggestions to improve my modified
version?

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


Don Guillett Wrote:
It's grand as long as you enjoy making selections to slow down your
code.

--
Don Guillett
SalesAid Software

"Hru48" wrote in
message ...

Thanks thats grand!


--
Hru48

------------------------------------------------------------------------
Hru48's Profile:
http://www.excelforum.com/member.php...o&userid=24895
View this thread:
http://www.excelforum.com/showthread...hreadid=554007



--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=554007