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
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????
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: