Run Time 1004 Error

D

Dokat

Active Member
Joined
Jan 19, 2015
Messages
304
Office Version
  1. 365
Hi,

I have below code that I use without any issues until today. Now it started giving Run Time 1004 error. I double checked the ranges and cells and didn't find an issue. I can;t figure out why this is happening, appreciate anyone who can help.

1645896844165.png

VBA Code: 
Sub CopyRangeTest2()

    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 <> "Commercial" And ws.Name <> "Walmart" Then
            With desWS
            
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("H3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("H4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("i3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("i4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("J3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("J4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("K3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("K4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("L3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("L4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("M3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("M4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("N3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("N4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("O3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("O4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("P3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("P4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("Q3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("Q4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("R3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("R4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("S3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("S4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("T3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("T4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("U3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("U4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("V3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("V4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("W3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("W4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("X3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("X4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("Y3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Range("Y4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("Z3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("Z4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AA3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AA4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AB3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AB4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AC3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AC4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AD3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AD4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AE3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AE4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AF3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AF4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AG3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AG4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AH3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AH4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AI3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AI4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AJ3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AJ4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AK3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AK4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AL3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AL4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AM3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AM4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AN3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AN4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AO3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AO4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AP3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AP4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AQ3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AQ4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AR3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AR4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AS3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AS4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AT3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AT4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AU3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AU4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AV3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AV4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AW3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AW4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AX3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AX4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AY3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AY4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AZ3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("AZ4")
                .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("BA3")
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Range("BA4")
               End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub
 
Sort by date Sort by votes
Alex Blakenburg said:
@Dokat - if your resizes aren't a gross oversimplification eg in this case you only have 2 sizes and there are no skipped columns, then you could consider simplifying the code.
(You can take this much further but I wanted to keep it looking somewhat similar to what you had)

VBA Code: 
Sub CopyRangeTest2()

    Application.ScreenUpdating = False
    Dim desWS As Worksheet, ws As Worksheet
    Dim i As Long, j As Long
   
    Set desWS = Sheets("POS")
    For Each ws In Sheets
        If ws.Name <> "POS" And ws.Name <> "National" And ws.Name <> "Commercial" And ws.Name <> "Walmart" Then
            With desWS
           
                For i = Columns("H").Column To Columns("Y").Column
                    .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Cells(3, i)
                    .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Cells(4, i)               
                Next i
               
                For j = Columns("Z").Column To Columns("BA").Column
                    .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Cells(3, i)
                    .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Cells(4, i)
                Next j
               
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub
Click to expand...
Thank you for your response it tried the code and it worked efficiently however it only copied the values to H and I. It didn't copy any values to cell A-G and J. Anyway to incorporate them to the code. Thanks
 
Upvote 0

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Alex Blakenburg said:
Oops in my j loop I didn't swap out the i to j please change
ws.Cells(3, i) & ws.Cells(4, i) in the For j loop,
to
ws.Cells(3, j) & ws.Cells(4, j)
Click to expand...
Thanks. I made the changes however it's still only copying cell H and I and not rest of the columns.
 

Attachments

  • 1645981284512.png
    1645981284512.png
    4.4 KB · Views: 6
Upvote 0
Alex Blakenburg said:
@Dokat - if your resizes aren't a gross oversimplification eg in this case you only have 2 sizes and there are no skipped columns, then you could consider simplifying the code.
(You can take this much further but I wanted to keep it looking somewhat similar to what you had)

VBA Code: 
Sub CopyRangeTest2()

    Application.ScreenUpdating = False
    Dim desWS As Worksheet, ws As Worksheet
    Dim i As Long, j As Long
   
    Set desWS = Sheets("POS")
    For Each ws In Sheets
        If ws.Name <> "POS" And ws.Name <> "National" And ws.Name <> "Commercial" And ws.Name <> "Walmart" Then
            With desWS
           
                For i = Columns("H").Column To Columns("Y").Column
                    .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Cells(3, i)
                    .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Cells(4, i)               
                Next i
               
                For j = Columns("Z").Column To Columns("BA").Column
                    .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Cells(3, i)
                    .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Cells(4, i)
                Next j
               
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub
Click to expand...
Here is the part of code that copy paste rest of the values from A through G and J.

VBA Code: 
With desWS
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("H5:H15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("I5:I15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("J5:J15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("K5:K15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("L5:L15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("M5:M15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("N5:N15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("O5:O15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("P5:P15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("Q5:Q15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("R5:R15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("S5:S15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("T5:T15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("U5:U15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("V5:V15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("W5:W15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("X5:X15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("Y5:Y15117").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("Z5:Z11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AA5:AA11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AB5:AB11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AC5:AC11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AD5:AD11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AE5:AE11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AF5:AF11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AG5:AG11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AH5:AH11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AI5:AI11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AJ5:AJ11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AK5:AK11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AL5:AL11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AM5:AM11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AN5:AN11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AO5:AO11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AP5:AP11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AQ5:AQ11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AR5:AR11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AS5:AS11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AT5:AT11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AU5:AU11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AV5:AV11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AW5:AW11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AX5:AX11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AY5:AY11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("AZ5:AZ11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                ws.Range("BA5:BA11741").Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
End With

End If

Next ws

Application.ScreenUpdating = True

End Sub
 
Upvote 0
The code I gave you only replaces the code you had in Post #1. It is a bit of a guide for you to use Loops rather lines and lines of hard coded values.
It should make it easier to see what the code is doing and be easier for future maintenance.

You could do a similar thing with the additional code you just posted.
Note: In post #1 you are effectively only copying values, while in your post #13 you are copying everything, so this would need to be addressed.
 
Upvote 0
Alex Blakenburg said:
The code I gave you only replaces the code you had in Post #1. It is a bit of a guide for you to use Loops rather lines and lines of hard coded values.
It should make it easier to see what the code is doing and be easier for future maintenance.

You could do a similar thing with the additional code you just posted.
Note: In post #1 you are effectively only copying values, while in your post #13 you are copying everything, so this would need to be addressed.
Click to expand...
Thank you for your reply. I agree, using loops would be alot better approach however my fear is there are different ranges that gets copy paste in the data that's why i hard coded the ranges thinking otherwise it would copy blank cell
 
Upvote 0
Dokat said:
Thank you for your reply. I agree, using loops would be alot better approach however my fear is there are different ranges that gets copy paste in the data that's why i hard coded the ranges thinking otherwise it would copy blank cell
Click to expand...
The additional code you sent could be incorporated in something like the below:
Note:
'XXX Added signifies added to the previous code
I think you need to address
- that the no of rows this time are a few rows different to what you had last time
- that we are using "=" for the initial copy (effectively paste values) and copy this time

VBA Code: 
Sub CopyRangeTest2_Pt2()

    Application.ScreenUpdating = False
    Dim desWS As Worksheet, ws As Worksheet
    Dim i As Long, j As Long
  
    Set desWS = Sheets("POS")
    For Each ws In Sheets
        If ws.Name <> "POS" And ws.Name <> "National" And ws.Name <> "Commercial" And ws.Name <> "Walmart" Then
            With desWS
          
                For i = Columns("H").Column To Columns("Y").Column
                    .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(15113).Value = ws.Cells(3, i)
                    .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(15113).Value = ws.Cells(4, i)
                  
                    ' XXX Added
                    ws.Range("A5:G15117").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                    ws.Range(Cells(5, i), Cells(15117, i)).Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                Next i
              
                For j = Columns("Z").Column To Columns("BA").Column
                    .Cells(.Rows.Count, "H").End(xlUp).Offset(1).Resize(11737).Value = ws.Cells(3, j)
                    .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(11737).Value = ws.Cells(4, j)
              
                    ' XXX Added
                    ws.Range("A5:G11741").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                    ws.Range(Cells(5, j), Cells(11741, j)).Copy .Cells(.Rows.Count, "J").End(xlUp).Offset(1)
                Next j
              
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Extra condition in search VBA.
Replies
2
Views
164
pete4monc
P
S
  • Question
Parse data - Overwrite lines with updated data
Replies
0
Views
121
Scifo
S
F
  • Question
VBA Code to record changes/logs modification needed?
Replies
0
Views
62
FGaxha
F
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
1
Views
207
bobaol
B
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
371
sspatriots
S

Forum statistics

Threads
1,214,905
Messages
6,122,172
Members
449,071
Latest member
cdnMech

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top