Object required confusion

[Stuart]

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.

 

[Rob Bovey]

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 *

 

[Ian Ripsher]

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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

Rob is absolutely correct - and I missed that

? typename(wb.Worksheets("MASTER").Copy(After:=wb.Sheets(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

 

[Stuart]

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.