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

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