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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
which line gives you that error ???

Rich (BB code): 
For Each ws In Sheets
do you have other then worksheets, like charts ?
Perhaps better to use For Each ws In WorkSheets

Do you still have +10,000 free cells in each of your columns ?
 
Upvote 0
For every worksheet you are copying over 600,000 values to columns H & I of 'POS'. So you will run out of rows on 'POS' before you get finished even the second worksheet.
 
Upvote 0
BSALV said:
which line gives you that error ???

Rich (BB code): 
For Each ws In Sheets
do you have other then worksheets, like charts ?
Perhaps better to use For Each ws In WorkSheets

Do you still have +10,000 free cells in each of your columns ?
Click to expand...
Thanks for your response. I don't have any other charts. The issue disappeared on its on for now. I think It's due to number of rows i am copying.
 
Upvote 0
Peter_SSs said:
For every worksheet you are copying over 600,000 values to columns H & I of 'POS'. So you will run out of rows on 'POS' before you get finished even the second worksheet.
Click to expand...
Thanks for your response. Issue is disappeared for now. I think you are right its due to the number of rows i am copying.
 
Upvote 0
Dokat said:
Issue is disappeared for now.
Click to expand...
Then you are only copying data from one sheet or else there are a lot of blanks at the bottom of what you are copying each time, or at least some of the time.
If you get the error again, you just need to look to see how many rows are left at the bottom of H & I in 'POS' and you will probably find that there is less than 15,113 or 11,737 depending on which line of code it errors on.
 
Upvote 0
Peter_SSs said:
Then you are only copying data from one sheet or else there are a lot of blanks at the bottom of what you are copying each time, or at least some of the time.
If you get the error again, you just need to look to see how many rows are left at the bottom of H & I in 'POS' and you will probably find that there is less than 15,113 or 11,737 depending on which line of code it errors on.
Click to expand...

Total number of rows I am copying is 600,671. I will keep an eye on if the error happens again. Thanks for the feedback
 
Upvote 0
@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
 
Upvote 0
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)
 
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,161
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