Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this script!?
Hiya Experts! The following scripts, after completing almost all the tasks, falls appart and gives me the following error: Run time error '91': Object variable or With block variable not set Code: -------------------- Sub PromoTrack_Potatoes() Dim Counter As Long Dim Source As Workbook Dim Destination As Workbook Dim rDivision, rYear, rCategory, rOwner As Range Const MyDir As String = "c:\PromoTrack\MSA\" Application.ScreenUpdating = False For Counter = 1 To 8240 Set Source = Workbooks.Open(MyDir & Counter & ".msa") Set rDivision = Range("B2") Set rYear = Range("B58") Set rCategory = Range("B73") Set rOwner = Range("B66") If rDivision.Value = "Frozen and Chilled" Then If rYear.Value = "2006" Then If rCategory = "Potatoes" Then If rOwner = "SOP" Then If Counter = 1 Then Source.Worksheets.Copy Set Destination = ActiveWorkbook ActiveSheet.Name = Counter Else Source.Worksheets.Copy After:=Destination.Worksheets(Destination.Workshee ts.Count) Destination.Worksheets(Destination.Worksheets.Coun t).Name = Counter End If End If End If End If End If Source.Close False Next Destination.SaveAs MyDir & "Summary Potatoes.xls" Application.ScreenUpdating = True MsgBox "Frozen MSAs compiled" End Sub -------------------- It seems to have problems with the SaveAs at the end - at least it is where the Debug functions put me. I am using Excell 2003 and VB 6.3 from MSOffice. Any ideas? The script is real long to run and a pain if you ask me but i really need it!! Many thanks in advance! Julien -- Petitboeuf ------------------------------------------------------------------------ Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602 View this thread: http://www.excelforum.com/showthread...hreadid=557786 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this script!?
You are setting the Destination variable only if all 5 IF
statement are true. You should move that code out of the IF statements to directly before your Next statement. Source.Close False Set Destination = ActiveWorkbook Next -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Petitboeuf" wrote in message ... Hiya Experts! The following scripts, after completing almost all the tasks, falls appart and gives me the following error: Run time error '91': Object variable or With block variable not set Code: -------------------- Sub PromoTrack_Potatoes() Dim Counter As Long Dim Source As Workbook Dim Destination As Workbook Dim rDivision, rYear, rCategory, rOwner As Range Const MyDir As String = "c:\PromoTrack\MSA\" Application.ScreenUpdating = False For Counter = 1 To 8240 Set Source = Workbooks.Open(MyDir & Counter & ".msa") Set rDivision = Range("B2") Set rYear = Range("B58") Set rCategory = Range("B73") Set rOwner = Range("B66") If rDivision.Value = "Frozen and Chilled" Then If rYear.Value = "2006" Then If rCategory = "Potatoes" Then If rOwner = "SOP" Then If Counter = 1 Then Source.Worksheets.Copy Set Destination = ActiveWorkbook ActiveSheet.Name = Counter Else Source.Worksheets.Copy After:=Destination.Worksheets(Destination.Workshee ts.Count) Destination.Worksheets(Destination.Worksheets.Coun t).Name = Counter End If End If End If End If End If Source.Close False Next Destination.SaveAs MyDir & "Summary Potatoes.xls" Application.ScreenUpdating = True MsgBox "Frozen MSAs compiled" End Sub -------------------- It seems to have problems with the SaveAs at the end - at least it is where the Debug functions put me. I am using Excell 2003 and VB 6.3 from MSOffice. Any ideas? The script is real long to run and a pain if you ask me but i really need it!! Many thanks in advance! Julien -- Petitboeuf ------------------------------------------------------------------------ Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602 View this thread: http://www.excelforum.com/showthread...hreadid=557786 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why won't this simple VBA script work in excel 2002? | Excel Discussion (Misc queries) | |||
Help needed (Automatic script function) | Excel Discussion (Misc queries) | |||
need help righting script to sort rows alphabetically then seperate them to line up | Excel Worksheet Functions | |||
VBA script help..Please !!!! | Excel Discussion (Misc queries) | |||
Using a VB Script for barcodes - new to this | Excel Discussion (Misc queries) |