The line of VBA code in my macro works on its own, but the whole macro fails
I tested every part of my code individually before running the macro and all parts worked just fine.
The one that doesn't work in my codes:
SQL.Range(Cells(2, 2), Cells(SQL.UsedRange.Rows.Count, 2)).Copy
The error message looks like this: Run-time error '1004' User-defined or object error
Here is my code up to the point of the error. What could be wrong?
Sub Prep()
Dim BO, HOC, Pol, Adv, Asg, Pay, SQL, Fml, Tbl As Worksheet
Dim c As Integer
Set BO = Sheets("BO")
Set HOC = Sheets("HOC")
Set Pol = Sheets("Policy")
Set Adv = Sheets("Advisor")
Set Asg = Sheets("Assignee")
Set Pay = Sheets("Payer")
Set SQL = Sheets("SQL")
Application.ScreenUpdating = False
BO.Range("L:L").Insert
BO.Range("L2").Value = "=DATE(LEFT(K2,4),MID(K2,5,2),RIGHT(K2,2))"
With BO.Range(Cells(2, 12), Cells(BO.UsedRange.Rows.Count, 12))
.FillDown
.Copy
End With
With BO.Range(Cells(2, 11), Cells(BO.UsedRange.Rows.Count, 11))
.PasteSpecial xlPasteValues
.NumberFormat = "mm/dd/yyyy"
End With
BO.Columns(12).EntireColumn.Delete
BO.Range("M:M").Insert
BO.Range("M2").Value = "=DATE(LEFT(L2,4),MID(L2,5,2),RIGHT(L2,2))"
With BO.Range(Cells(2, 13), Cells(BO.UsedRange.Rows.Count, 13))
.FillDown
.Copy
End With
With BO.Range(Cells(2, 12), Cells(BO.UsedRange.Rows.Count, 12))
.PasteSpecial xlPasteValues
.NumberFormat = "mm/dd/yyyy"
End With
BO.Columns(13).EntireColumn.Delete
BO.Range("N:N").Insert
BO.Range("N2").Value = "=IFERROR(DATE(LEFT(M2,4),MID(M2,5,2),RIGHT(M2,2)),"""")"
With BO.Range(Cells(2, 14), Cells(BO.UsedRange.Rows.Count, 14))
.FillDown
.Copy
End With
With BO.Range(Cells(2, 13), Cells(BO.UsedRange.Rows.Count, 13))
.PasteSpecial xlPasteValues
.NumberFormat = "mm/dd/yyyy"
End With
BO.Columns(14).EntireColumn.Delete
SQL.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
Header:=xlYes
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Formulas"
Set Fml = Sheets("Formulas")
With Fml.Cells(1, 1)
.Value = "=EOMONTH(TODAY(),-1)"
.Copy
.PasteSpecial xlPasteValues
.NumberFormat = "mm/dd/yyyy"
End With
SQL.Range(Cells(2, 2), Cells(SQL.UsedRange.Rows.Count, 2)).Copy
+3
source to share
1 answer
You are trying to define a Range object using two Range.Cells properties in other sheets.
Define each link in your definition explicitly.
SQL.Range(SQL.Cells(2, 2), SQL.Cells(SQL.UsedRange.Rows.Count, 2)).Copy
Or a more preferred method,
with SQL
.Range(.Cells(2, 2), .Cells(.UsedRange.Rows.Count, 2)).Copy
end with
+3
source to share