Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Object required??? What object? | Excel Discussion (Misc queries) | |||
R/T 424 - Object required Help | Excel Discussion (Misc queries) | |||
Runtime error '424': Object Required | Excel Discussion (Misc queries) | |||
"Microsoft Visual Basic runtime error '424' object required". | Excel Worksheet Functions | |||
Object Required | Excel Discussion (Misc queries) |