P
Petitboeuf
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.Worksheets.Count)
Destination.Worksheets(Destination.Worksheets.Count).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
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.Worksheets.Count)
Destination.Worksheets(Destination.Worksheets.Count).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