CODEWITHSUNDEEP
vbaexcel
Bryan F
Bryan F

Reputation: 960

A line of VBA code in my macro works on it own, but fails when the entire macro runs

I tested each part of my code individually, before running the macro and all parts worked just fine.

The one that fails in my codes is: 

SQL.Range(Cells(2, 2), Cells(SQL.UsedRange.Rows.Count, 2)).Copy

The error message is as follows: Run-time error '1004' Application-defined or object-defined error

Here is my code up until the point of error. What could be going 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

Upvotes: 3

Views: 90

Answers (1)

user4039065
user4039065

Reputation:

You are trying to define the Range object using two Range.Cells properties from other worksheets.

Define every reference within 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

Upvotes: 3

Related Questions