Object required confusion
 

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

Object required confusion
 

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

Object required confusion
 

Only use the Set keyword when you're assigning a value to an object
variable. Copy is a worksheet method that doesn't return a reference to an
object (even though you've just used it to create a new object).

"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

Object required confusion
 

is wb set to a workbook.

Also, if you are going to use wb explicitly in each instance, you don't need
the with wb statement.
'
' wb refers to the workbook where n copies of
' "master" are created
set wb = workbooks("somebook.xls")
For Each ws In Workbooks(SourceWorkbook).Worksheets
With ws
If Not (UCase(.Name) = "MASTER") Then
Set ws2 = wb.Worksheets("MASTER").Copy(After:= _
wb.Sheets(wb.Sheets.Count))
ws2.Name = ws.Name
end if
End With
Next

Regards,
Tom Ogilvy


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

Object required confusion
 

Rob is absolutely correct - and I missed that

? typename(wb.Worksheets("MASTER").Copy(After:=wb.Sh eets(wb.Sheets.Count)))
Boolean

But that would give a type mismatch error I think (at least in Excel 97 it
does).

So you may still have problems with wb in addition to that. A possible
revision

' wb refers to the workbook where n copies of
' "master" are created
set wb = workbooks("somebook.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

--
Tom Ogilvy


Tom Ogilvy wrote in message
...
is wb set to a workbook.

Also, if you are going to use wb explicitly in each instance, you don't
need
the with wb statement.
'
' wb refers to the workbook where n copies of
' "master" are created
set wb = workbooks("somebook.xls")
For Each ws In Workbooks(SourceWorkbook).Worksheets
With ws
If Not (UCase(.Name) = "MASTER") Then
Set ws2 = wb.Worksheets("MASTER").Copy(After:= _
wb.Sheets(wb.Sheets.Count))
ws2.Name = ws.Name
end if
End With
Next

Regards,
Tom Ogilvy


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

Object required confusion
 

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