Many thanks to you both. This seems to work:

Set wb = Workbooks("NewQSBofQ.xls")
For Each ws In Workbooks(SourceWorkbook).Worksheets
With ws
If Not (UCase(.Name) = "MASTER") Then
wb.Worksheets("MASTER").Copy _
After:=wb.Sheets(wb.Sheets.Count)
ActiveSheet.Name = ws.Name
End If
End With
Next

Regards.

"Rob Bovey" wrote in message
...
Hi Stuart,

The worksheet copy method doesn't return a reference to the copied
worksheet (although it really should, and I don't understand why it was
designed that way). The worksheet you copy will become the ActiveSheet as
soon as the copy is complete, so you have to set your reference to the
ActiveSheet immediately after the copy operation.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Stuart" wrote in message
...
In the following I receive an 'Object required' error
on the line 'Set ws2 etc'. Why is this, please?

For Each ws In Workbooks(SourceWorkbook).Worksheets
With ws
If Not (UCase(.Name) = "MASTER") Then
With wb
Set ws2 = wb.Worksheets("MASTER").Copy(After:= _
wb.Sheets(wb.Sheets.Count))
ws2.Name = ws.Name
etc

The copy is made when stepping through, but only after the
error message is displayed.

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003