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

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