Runtime error 1004 - Copy-paste area aren't the same size

L

learningVBA1982

New Member
Joined
Jun 1, 2018
Messages
1
Hi there. I had the following Original Code, working well. The purpose it to feed a dashboard file with some source data, for analysis. What is being done is opening a file, copying the data into a "Stage" file, adding few columns with formulas, copy-pasting values, deleting some columns, copy-pasting to the dashboard file.


ORIGINAL CODE

Code: 
Sub FilterResolved()
Dim strPath As String
Dim strStage As String
Dim StageFile As String
Dim wbStage As Workbook
Dim wbKPI As Workbook
Dim FilterOwner As Variant
'Dim FilterStatus As Variant
Dim shStg As Worksheet
Dim shStg2 As Worksheet
Dim shRes As Worksheet
Dim PasteStart As Range

strPath = ActiveWorkbook.Path
strStage = "\Stage.xlsx"
StageFile = strPath & strStage

Set wbKPI = ThisWorkbook                            'set the KPI workbook
Set wbStage = Workbooks.Open(Filename:=StageFile)   'set the Stage workbook

Set shRes = wbKPI.Sheets("RESOLVED")                   'set the OPEN sheet in the KPI workbook
Set shStg = wbStage.Sheets("STAGE")                 'set the STAGE sheet in the Stage workbook
Set shStg2 = wbStage.Sheets("STAGE2")               'set the STAGE2 sheet in the Stage workbook
Set shSteps = wbKPI.Sheets("Steps")                 'set the Steps sheet in the KPI workbook
i = shStg.Range("A1", shStg.Range("A1").End(xlDown)).Rows.Count     'define counter for STAGE
f = shRes.Range("A1", shRes.Range("A1").End(xlDown)).Rows.Count   'define counter for RESOLVED

Set PasteStart = shRes.Range("A" & f + 1)

'define Owner filter criteria on sheet "Steps" range N5:N204
Set FilterOwner = shSteps.Range("N5:N204")
'define Workstream filter criteria on sheet "Steps" range M5:M204
'Set FilterStatus = shSteps.Range("M5:M204")

'start processing the data in the Stage.xlsx workbook
shStg.Select
 
'filter out the GOOD records from RESOLVED file
Rows("1:1").Select
Selection.AutoFilter
Columns("k:L").Select
    Selection.NumberFormat = "General"
    Selection.NumberFormat = "dd/mm/yy"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=TRUNC(RC[-1])"
    Range("L2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range("K2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
shStg.Select
ActiveSheet.Range("$A$1:$CF$999999").AutoFilter Field:=10, Criteria1:=Application.Transpose(FilterOwner), Operator:=xlFilterValues

'get ALL data from STAGE sheet and put it back on the STAGE2 sheet. This is only the GOOD data
shStg.Select
    Cells.Select
    Selection.Copy
shStg2.Select
    Range("A1").Select
    ActiveSheet.Paste
    
'remove the extra columns while in STAGE2 Sheet
shStg2.Select
    Columns("K:K").Select
    Selection.Copy
    Columns("Q:Q").Select
    ActiveSheet.Paste
    Columns("J:J").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("R:R").Select
    ActiveSheet.Paste
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("S:S").Select
    ActiveSheet.Paste
    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("T:T").Select
    ActiveSheet.Paste
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("U:U").Select
    ActiveSheet.Paste
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("V:V").Select
    ActiveSheet.Paste
    Columns("O:O").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("W:W").Select
    ActiveSheet.Paste
    Columns("A:P").Select
    Selection.Delete Shift:=xlToLeft
    
'get ALL data from STAGE sheet and put it back on the RESOLVED sheet. This is only the GOOD data
shStg2.Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
j = shStg2.Range("A1", shStg2.Range("A1").End(xlDown)).Rows.Count     'define counter for STAGE2
    
wbKPI.Activate
shRes.Select
    PasteStart.Select
    ActiveSheet.Paste
    Rows(f + 1).Select 'delete the header that was copied from the STAGE
    Selection.Delete
shRes.Select
    Columns("A:A").Select
    Selection.NumberFormat = "dd/mm/yy"
    
k = shRes.Range("A1", shRes.Range("A1").End(xlDown)).Rows.Count 'counter for unique filter
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Range("A1:N" & k).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6 _
        ), Header:=xlYes
l = shRes.Range("A1", shRes.Range("A1").End(xlDown)).Rows.Count 'counter for ALL resolved cases
 
 
wbStage.Close savechanges:=False
wbKPI.Activate
shSteps.Select
'display success message
MsgBox "You have successfully added the RESOLVED cases. To this day, you have resolved a total of " & l - 1 & " cases"
Kill StageFile
End Sub

Code: 
Sub Resolved()
' combines the GetReceived and FilterReceived

MsgBox "You are about to load the RESOLVED file. The operation may take up to 5 minutes"

Call GetFile
Call FilterResolved
ActiveWorkbook.Save
End Sub

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Then, my RESOLVED file (source of info) has been updated by another team - additional columns were added, and I started to get the following message:

"RUN-TIME ERROR1004 - You can't paste this here because the Copy area and paste area aren't the same size. Select just one cell in the past area or an area that's the same size, and try pasting again." When I clicked "debug" - it showed me the error was in ActiveSheet.Paste

So, what I did is that I took the additional colums added and adapted the code to include them in this copy-pasting from one file to another. Then I started getting the same error, I though the problem was perhaps the fact that it was a "Stage.xlsx" file - I changed the code to create a "Stage.xlsm" file instead. (The files were having more than 65k rows.) That also didn't solve the issue.

Then started to see if the problem was coming from selection, then updated some of the coding... nothing, same message.

Then I tried if the problem was the PasteStart definition was causing the issue. No. Also not the issue.

I have no more ideas... Can anyone help please????


Code: 
UPDATED CODE
Sub FilterResolved()
Dim strPath As String
Dim strStage As String
Dim StageFile As String
Dim wbStage As Workbook
Dim wbKPI As Workbook
Dim FilterOwner As Variant
'Dim FilterStatus As Variant
Dim shStg As Worksheet
Dim shStg2 As Worksheet
Dim shRes As Worksheet
Dim PasteStart As Range

strPath = ActiveWorkbook.Path
strStage = "\Stage.xlsm"
StageFile = strPath & strStage

Set wbKPI = ThisWorkbook                            'set the KPI workbook
Set wbStage = Workbooks.Open(Filename:=StageFile)   'set the Stage workbook

Set shRes = wbKPI.Sheets("RESOLVED")                   'set the OPEN sheet in the KPI workbook
Set shStg = wbStage.Sheets("STAGE")                 'set the STAGE sheet in the Stage workbook
Set shStg2 = wbStage.Sheets("STAGE2")               'set the STAGE2 sheet in the Stage workbook
Set shSteps = wbKPI.Sheets("Steps")                 'set the Steps sheet in the KPI workbook
i = shStg.Range("A1", shStg.Range("A1").End(xlDown)).Rows.Count     'define counter for STAGE
f = shRes.Range("A1", shRes.Range("A1").End(xlDown)).Rows.Count   'define counter for RESOLVED

Set PasteStart = shRes.Range("A" & Rows.Count).End(xlUp).Offset(1)

'define Owner filter criteria on sheet "Steps" range N5:N204
Set FilterOwner = shSteps.Range("N5:N204")
'define Workstream filter criteria on sheet "Steps" range M5:M204
'Set FilterStatus = shSteps.Range("M5:M204")

'start processing the data in the Stage.xlsm workbook
shStg.Select
 
'filter out the GOOD records from RESOLVED file
Rows("1:1").Select
Selection.AutoFilter
Columns("k:L").Select
    Selection.NumberFormat = "General"
    Selection.NumberFormat = "dd/mm/yy"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=TRUNC(RC[-1])"
    Range("L2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range("K2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
shStg.Select
ActiveSheet.Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter Field:=10, Criteria1:=Application.Transpose(FilterOwner), Operator:=xlFilterValues
'get ALL data from STAGE sheet and put it back on the STAGE2 sheet. This is only the GOOD data
shStg.Select
    Cells.Select
    Selection.Copy
shStg2.Select
    Range("A1").Select
    ActiveSheet.Paste
    
'remove the extra columns while in STAGE2 Sheet
shStg2.Select
    Columns("K:K").Select
    Selection.Copy
    Columns("S:S").Select
    ActiveSheet.Paste
    Columns("J:J").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("T:T").Select
    ActiveSheet.Paste
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("U:U").Select
    ActiveSheet.Paste
    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("V:V").Select
    ActiveSheet.Paste
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("W:W").Select
    ActiveSheet.Paste
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("X:X").Select
    ActiveSheet.Paste
    Columns("O:O").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("Y:Y").Select
    ActiveSheet.Paste
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("Z:Z").Select
    ActiveSheet.Paste
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("AA:AA").Select
    ActiveSheet.Paste
    Columns("P:P").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("AB:AB").Select
    ActiveSheet.Paste
    Columns("Q:Q").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("AC:AC").Select
    ActiveSheet.Paste
    Columns("R:R").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("AD:AD").Select
    ActiveSheet.Paste
    Columns("A:R").Select
    Selection.Delete Shift:=xlToLeft
    
'get ALL data from STAGE sheet and put it back on the RESOLVED sheet. This is only the GOOD data
shStg2.Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
j = shStg2.Range("A1", shStg2.Range("A1").End(xlDown)).Rows.Count     'define counter for STAGE2
    
wbKPI.Activate
shRes.Select
    PasteStart.Select
    ActiveSheet.Paste
    Rows(f + 1).Select 'delete the header that was copied from the STAGE
    Selection.Delete
shRes.Select
    Columns("A:A").Select
    Selection.NumberFormat = "dd/mm/yy"
    
   
k = shRes.Range("A1", shRes.Range("A1").End(xlDown)).Rows.Count 'counter for unique filter
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Range("A1:R" & k).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 _
        ), Header:=xlYes
l = shRes.Range("A1", shRes.Range("A1").End(xlDown)).Rows.Count 'counter for ALL resolved cases
 
 
wbStage.Close savechanges:=False
wbKPI.Activate
shSteps.Select
'display success message
MsgBox "You have successfully added the RESOLVED cases. To this day, you have resolved a total of " & l - 1 & " cases"
Kill StageFile
End Sub

Code: 
Sub Resolved()
' combines the GetReceived and FilterReceived

MsgBox "You are about to load the RESOLVED file. The operation may take up to 5 minutes"

Call GetFile
Call FilterResolved
ActiveWorkbook.Save
End Sub
 
Last edited by a moderator:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
The basic thing that dawned on me one day was: If you try to cut and paste a range that includes merged cells into ANY part of the original area, it will give you an error. You can paste into a non used area and then move it to where you need it without error.

Jeff
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
441
myall_blues
myall_blues
D
  • Solved
VBA If statement to continue
2
Replies
17
Views
653
dinunan
D
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
273
rlv01
R
D
  • Solved
Trying to copy data from a specific sheet in one workbook to another with VBA
Replies
2
Views
177
druck21
D
S
  • Question Question
Macro Formatting to all WS
Replies
4
Views
360
MARK858
MARK858

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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