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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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

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
2
Views
217
B-radK
B
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
372
sspatriots
S

Forum statistics

Threads
1,214,925
Messages
6,122,303
Members
449,078
Latest member
nonnakkong

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