Hi,
I have below code where i copy paste different ranges from one worksheet to another to construct a data feed for another application. Code used to work however right now i am receiving Run time 1004 error in the middle of it code stop running. What may cause this issues other than expanding range i didnt make any changes. Below is the screenshot of the error i am receiving. Appreciate any help
I have below code where i copy paste different ranges from one worksheet to another to construct a data feed for another application. Code used to work however right now i am receiving Run time 1004 error in the middle of it code stop running. What may cause this issues other than expanding range i didnt make any changes. Below is the screenshot of the error i am receiving. Appreciate any help
VBA Code:
Sub CopyRangeTest()
Application.ScreenUpdating = False
Dim desWS As Worksheet, ws As Worksheet
Set desWS = Sheets("POS")
For Each ws In Sheets
If ws.Name <> "POS" And ws.Name <> "National" And ws.Name <> "Hardware" And ws.Name <> "Depot" Then
With desWS
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("H5:H16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("I5:I16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("J5:J16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("K5:K16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("L5:L16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("M5:M16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("N5:N16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("O5:O16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("P5:P16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("Q5:Q16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("R5:R16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("S5:S16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("T5:T16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("U5:U16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("V5:V16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("W5:W16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("X5:X16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:G16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("Y5:Y16278").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("Z5:Z12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A16278").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AA5:AA12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AB:AB12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AC5:AC12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AD5:AD12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AE5:AE12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AF5:AF12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AG5:AG12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AH5:AH12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AI5:AI12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AJ5:AJ12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AK5:AK12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AL5:AL12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AM5:AM12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AN5:AN12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AO5:AO12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AP5:AP12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AQ5:AQ12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AR5:AR12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AS5:AS12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AT5:AT12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AU5:AU12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AV5:AV12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AW5:AW12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AX5:AX12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AY5:AY12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("AZ5:AZ12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
ws.Range("A5:A12902").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
ws.Range("BA5:BA12902").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("H3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("H4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("i3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("i4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("J3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("J4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("K3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("K4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("L3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("L4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("M3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("M4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("N3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("N4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("O3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("O4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("P3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("P4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("Q3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("Q4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("R3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("R4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("S3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("S4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("T3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("T4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("U3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("U4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("V3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("V4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("W3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("W4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("X3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("X4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("Y3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(16274).Value = ws.Range("Y4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("Z2")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("Z3")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AA3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AA3")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AB3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AB4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AC3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AC4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AD3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AD4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AE3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AE4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AF3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AF4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AG3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AG4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AH3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AH4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AI3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AI4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AJ3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AJ4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AK3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AK4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AL3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AL4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AM3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AM4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AN3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AN4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AO3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AO4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AP3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AP4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AQ3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AQ4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AR3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AR4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AS3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AS4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AT3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AT4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AU3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AU4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AV3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AV4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AW3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AW4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AX3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AX4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AY3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AY4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AZ3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("AZ4")
.Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("BA3")
.Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(12898).Value = ws.Range("BA4")
End With
End If
Next ws
Application.ScreenUpdating = True
End Sub