Poundland
02-06-2017, 05:07 AM
Hi Guys,
I am being thwarted by a baffling Runtime Error 1004, it only happens on 1 code line, commented in the code below, but the baffling part of this is that the same code line is used in the same code stream earlier in the process and works just fine, so I cannot understand when it is called a second time that it produces the error...
Can you enlighten me to the error of my ways.. ;)
Sub Slots_Reporting()
Dim wrkThis As Workbook, shtSummary As Worksheet
Dim wrkSlots As Workbook, shtSlots As Worksheet
Dim pdat As Date
Dim rngDay(1 To 2) As Range, rngData As Range, rngDestn As Range
pdat = VBA.Format(Now() - 1, "dd/mm/yyyy")
Set wrkThis = ThisWorkbook
Set shtSummary = wrkThis.Sheets("Summary")
On Error Resume Next
Application.DisplayAlerts = False
'Set wrkSlots = Workbooks("Slots Reporting " & VBA.Format(pdat, "yymmdd") & ".xlsx") ' Testing Only
Set wrkSlots = Workbooks.Open("I:\H904 Supply Chain\Planning\Reports\Slot Reports\Slots Reporting " & VBA.Format(pdat, "yymmdd") & ".xlsx")
Set wrkSlots = Workbooks.Open("I:\H904 Supply Chain\Planning\Reports\Slot Reports\Slots Reporting " & VBA.Format(pdat, "yymmd") & ".xlsx")
Application.DisplayAlerts = True
On Error GoTo 0
On Error GoTo errorhandler
Set shtSlots = wrkSlots.Sheets("DC Dept Summary")
AA:
' springvale data
Set rngDestn = shtSummary.Cells(40, 3)
With shtSlots.Rows(3)
Set rngDay(1) = .Find("Springvale", LookIn:=xlValues).Offset(32)
Set rngDay(2) = .Find("Springvale", LookIn:=xlValues).Offset(33)
End With
Set rngData = shtSlots.Range(rngDay(1).Address, rngDay(2).Address)
rngData.Copy
rngDestn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
' wellmans
Set rngDestn = shtSummary.Cells(41, 3)
With shtSlots.Rows(3)
Set rngDay(1) = .Find("Wellmans", LookIn:=xlValues).Offset(32)
Set rngDay(2) = .Find("Wellmans", LookIn:=xlValues).Offset(33)
End With
Set rngData = shtSlots.Range(rngDay(1).Address, rngDay(2).Address)
rngData.Copy
rngDestn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
' harlow
Set rngDestn = shtSummary.Cells(42, 3)
With shtSlots.Rows(3)
Set rngDay(1) = .Find("Harlow", LookIn:=xlValues).Offset(32)
Set rngDay(2) = .Find("Harlow", LookIn:=xlValues).Offset(33)
End With
Set rngData = shtSlots.Range(rngDay(1).Address, rngDay(2).Address)
rngData.Copy
rngDestn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
' wigan
Set rngDestn = shtSummary.Cells(43, 3)
With shtSlots.Rows(3)
Set rngDay(1) = .Find("WIG", LookIn:=xlValues).Offset(32)
Set rngDay(2) = .Find("WIG", LookIn:=xlValues).Offset(33)
End With
Set rngData = shtSlots.Range(rngDay(1).Address, rngDay(2).Address)
rngData.Copy
rngDestn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
wrkSlots.Close False
Exit Sub
errorhandler:
pdat = pdat - 2
On Error Resume Next
Application.DisplayAlerts = False
'Set wrkSlots = Workbooks("Slots Reporting " & VBA.Format(pdat, "yymmdd") & ".xlsx") ' Testing Only
Set wrkSlots = Workbooks.Open("I:\H904 Supply Chain\Planning\Reports\Slot Reports\Slots Reporting " & VBA.Format(pdat, "yymmdd") & ".xlsx") ' Produces 1004 Runtime Error
Set wrkSlots = Workbooks.Open("I:\H904 Supply Chain\Planning\Reports\Slot Reports\Slots Reporting " & VBA.Format(pdat, "yymmd") & ".xlsx")
Set shtSlots = wrkSlots.Sheets("DC Dept Summary")
Application.DisplayAlerts = True
On Error GoTo 0
GoTo AA
End Sub
I am being thwarted by a baffling Runtime Error 1004, it only happens on 1 code line, commented in the code below, but the baffling part of this is that the same code line is used in the same code stream earlier in the process and works just fine, so I cannot understand when it is called a second time that it produces the error...
Can you enlighten me to the error of my ways.. ;)
Sub Slots_Reporting()
Dim wrkThis As Workbook, shtSummary As Worksheet
Dim wrkSlots As Workbook, shtSlots As Worksheet
Dim pdat As Date
Dim rngDay(1 To 2) As Range, rngData As Range, rngDestn As Range
pdat = VBA.Format(Now() - 1, "dd/mm/yyyy")
Set wrkThis = ThisWorkbook
Set shtSummary = wrkThis.Sheets("Summary")
On Error Resume Next
Application.DisplayAlerts = False
'Set wrkSlots = Workbooks("Slots Reporting " & VBA.Format(pdat, "yymmdd") & ".xlsx") ' Testing Only
Set wrkSlots = Workbooks.Open("I:\H904 Supply Chain\Planning\Reports\Slot Reports\Slots Reporting " & VBA.Format(pdat, "yymmdd") & ".xlsx")
Set wrkSlots = Workbooks.Open("I:\H904 Supply Chain\Planning\Reports\Slot Reports\Slots Reporting " & VBA.Format(pdat, "yymmd") & ".xlsx")
Application.DisplayAlerts = True
On Error GoTo 0
On Error GoTo errorhandler
Set shtSlots = wrkSlots.Sheets("DC Dept Summary")
AA:
' springvale data
Set rngDestn = shtSummary.Cells(40, 3)
With shtSlots.Rows(3)
Set rngDay(1) = .Find("Springvale", LookIn:=xlValues).Offset(32)
Set rngDay(2) = .Find("Springvale", LookIn:=xlValues).Offset(33)
End With
Set rngData = shtSlots.Range(rngDay(1).Address, rngDay(2).Address)
rngData.Copy
rngDestn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
' wellmans
Set rngDestn = shtSummary.Cells(41, 3)
With shtSlots.Rows(3)
Set rngDay(1) = .Find("Wellmans", LookIn:=xlValues).Offset(32)
Set rngDay(2) = .Find("Wellmans", LookIn:=xlValues).Offset(33)
End With
Set rngData = shtSlots.Range(rngDay(1).Address, rngDay(2).Address)
rngData.Copy
rngDestn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
' harlow
Set rngDestn = shtSummary.Cells(42, 3)
With shtSlots.Rows(3)
Set rngDay(1) = .Find("Harlow", LookIn:=xlValues).Offset(32)
Set rngDay(2) = .Find("Harlow", LookIn:=xlValues).Offset(33)
End With
Set rngData = shtSlots.Range(rngDay(1).Address, rngDay(2).Address)
rngData.Copy
rngDestn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
' wigan
Set rngDestn = shtSummary.Cells(43, 3)
With shtSlots.Rows(3)
Set rngDay(1) = .Find("WIG", LookIn:=xlValues).Offset(32)
Set rngDay(2) = .Find("WIG", LookIn:=xlValues).Offset(33)
End With
Set rngData = shtSlots.Range(rngDay(1).Address, rngDay(2).Address)
rngData.Copy
rngDestn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
wrkSlots.Close False
Exit Sub
errorhandler:
pdat = pdat - 2
On Error Resume Next
Application.DisplayAlerts = False
'Set wrkSlots = Workbooks("Slots Reporting " & VBA.Format(pdat, "yymmdd") & ".xlsx") ' Testing Only
Set wrkSlots = Workbooks.Open("I:\H904 Supply Chain\Planning\Reports\Slot Reports\Slots Reporting " & VBA.Format(pdat, "yymmdd") & ".xlsx") ' Produces 1004 Runtime Error
Set wrkSlots = Workbooks.Open("I:\H904 Supply Chain\Planning\Reports\Slot Reports\Slots Reporting " & VBA.Format(pdat, "yymmd") & ".xlsx")
Set shtSlots = wrkSlots.Sheets("DC Dept Summary")
Application.DisplayAlerts = True
On Error GoTo 0
GoTo AA
End Sub