200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I have been using this report with no issues until the supplier changed their source report format.
i now get this error that appears to be in relation to selecting the copy range.
attached is
1 screen shot of debug error message
2 suppliers new report (raw data) called "training matrix"
3 "report" - the raw data is copied to "sheet 1" then in sheet "VOC" you click the green "import columns" and the script runs.
but now there is a run-time error !!! grrrr
Imported from source 09/04/2024 11:54:28 but didn't find matches for report headers:
HLTAID001 - CPR - Refresher due every 12 months
SP029 - Vehicles and Driving
SP030 - Heavy Vehicle National Law
SP017 - Articulated Haul and Haul Truck Operations
SP033 - Haul Truck
SP017 - Articulated Haul and Haul Truck Operations
SP021 - Vibrating Plant and Equipment
SP011 - Trenching and Excavations
VOC IT DOOSAN DL300
RIISAM211 - Remove, repair and refit tyres and tubes
SP016 - Plant Service and Refuelling
SP012 - Crane and Lifting Operations
HRWL - CB Bridge and Gantry Crane
SP012 - Crane and Lifting Operations
HRWL - RI Rigging IntermediateSue
From the Discussion above, it seems your question .xlsm file didn't include the new Report format (so Willie and I found no error).
In the first attached file below, I deleted the existing Sheet0 and copied Sheet0 from the Report.xlsx file to that file.
I then found that a runtime error 1004 occurred because -when writing the first column (with n=1) - the array element ColXRef(1, 2) was emptyso .Cells(2, ColXRef(n, 2) would evaluate to .Cells(1,0) which doesn't exist in Excel since there is no column 0.
I think I fixed it using the simple expedient of setting it to 1 when n=1 and the array element is empty, as in bold in the code extract below:
' #4. Copy data from source to report using cross refs
' go across headers
For n = 1 To RepLstCl
' if first column or there's a cross ref to source
If n = 1 Or Not IsEmpty(ColXRef(n, 2)) Then
' when n=1 set value to 1
If IsEmpty(ColXRef(n, 2)) Then ColXRef(n, 2) = 1
' copy source rows 2 to end
With Sheets(2)
Set CpyRng = .Range(.Cells(2, ColXRef(n, 2)), .Cells(SrcLstRw, ColXRef(n, 2)))
CpyRng.Copy Destination:=Sheets(1).Cells(3, n)
End With
Else
'otherwise collect headings not imported
NoX = NoX & ColXRef(n, 1) & vbCr
End If
' write borders and fill background like header
With Sheets(1).Range(Cells(3, n), Cells(SrcLstRw + 1, n))
.Borders.LineStyle = xlContinuous
.Interior.Color = Sheets(1).Cells(1, n).Interior.Color
End With
Next nThe button now runs without problem (and the missing refs are reported as before).
Hope this fixes your problem- if so, please remember to mark this Answer as Selected.