[George Clark]

Stuart -

The problem is that the line:

.Name = ws.Name

is not fully qualified. Well, actually it is, but it's defined to be the
worksheet in the source workbook. Try this instead (note that I've made a
few other changes, notably adding a workbook object and some worksheet
objects, to make for a little less typing):

Sub CopySheets(SourceWorkbook As Workbook)

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim wb As Workbook

Set wb = Workbooks("NewQSBofQ.xls")

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

End Sub


George

On 7/13/03 06:44, Stuart did expound most eloquently:

How best to create a mirror of the sheet names in another
workbook please?

So if wkbk1 holds 3 sheets named a, b, c then I need to
create 3 new sheets in wkbk2 named a, b, c.

I can add the sheets but not name them:

For Each ws In Workbooks(SourceWorkbook).Worksheets
With ws
If Not (UCase(.Name) = "MASTER") Then
Workbooks("NewQSBofQ.xls").Sheets.Add _
After:=Workbooks("NewQSBofQ.xls").Sheets _
(Sheets.Count)
.Name = ws.Name ''' problem here?
End If
End With
Next

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



--
Mac Word FAQ: <http://www.mvps.org/word/FAQs/WordMac/index.html
Entourage Help Page: <http://www.entourage.mvps.org/
Or try Google: <http://www.google.com

Please do not reply to this message. Although the email address is perfectly
valid, any replies to this account never get to the Inbox on my computer.

[Stuart[_5_]]

Many thanks.

Regards.

"George Clark" wrote in message
...
Stuart -

The problem is that the line:

.Name = ws.Name

is not fully qualified. Well, actually it is, but it's defined to be the
worksheet in the source workbook. Try this instead (note that I've made a
few other changes, notably adding a workbook object and some worksheet
objects, to make for a little less typing):

Sub CopySheets(SourceWorkbook As Workbook)

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim wb As Workbook

Set wb = Workbooks("NewQSBofQ.xls")

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

End Sub


George

On 7/13/03 06:44, Stuart did expound most eloquently:

How best to create a mirror of the sheet names in another
workbook please?

So if wkbk1 holds 3 sheets named a, b, c then I need to
create 3 new sheets in wkbk2 named a, b, c.

I can add the sheets but not name them:

For Each ws In Workbooks(SourceWorkbook).Worksheets
With ws
If Not (UCase(.Name) = "MASTER") Then
Workbooks("NewQSBofQ.xls").Sheets.Add _
After:=Workbooks("NewQSBofQ.xls").Sheets _
(Sheets.Count)
.Name = ws.Name ''' problem here?
End If
End With
Next

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



--
Mac Word FAQ: <http://www.mvps.org/word/FAQs/WordMac/index.html
Entourage Help Page: <http://www.entourage.mvps.org/
Or try Google: <http://www.google.com

Please do not reply to this message. Although the email address is
perfectly
valid, any replies to this account never get to the Inbox on my computer.



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