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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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: 7
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

M
  • Question Question
Re-produce code to copy last row from listbox on userform to sheet
Replies
3
Views
166
MKLAQ
M
B
  • Question Question
Error message when copying and renaming a worksheet
2
Replies
13
Views
344
smozgur
smozgur
E
  • Solved
This Code fills in below current month.
Replies
2
Views
196
Eric Penfold
E
V
  • Solved
Need VBA to split data into multiple sheets paste formulas, formatting AND data validation
Replies
2
Views
123
vanessavalentino83
V
B
  • Question Question
VBA: for loop unexpectedly ignoring first row....
Replies
1
Views
184
RoryA
RoryA

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

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