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