Add new sheet and Name it
 

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.

Add new sheet and Name it
 

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