VBA for copy and paste stopped working

[Godders199]

HI, i have been running the following code every day for 5 months with no issues, it now debugs, with the following error "we cannot paste because the copy screen and paste area arent the same size. The spreadsheet the data is dumped in is purely an archive which is not used on a daily basis. I think i need to change the code to avoid any formatting issues, but cannot work out what to change.

Dim xm As Long
Sheets("submissions").Select
ActiveSheet.ShowAllData
Columns("H").Select
Range("p1").Activate
Selection.EntireColumn.Hidden = True
Columns("t:v").Select
Range("v1").Activate
Selection.EntireColumn.Hidden = True
ActiveSheet.Range("A:w").AutoFilter Field:=18, Criteria1:="<>"
ActiveSheet.Range("A:w").AutoFilter Field:=23, Criteria1:="<>"
Range("D2:AC2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Open Filename:= _
"S:\4th Floor\SQ Operations\OPs Mi Spreadsheets\Allocation audit tools\Allocation Data\test.xlsx"
Range("a1").Select
xm = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("a" & xm).PasteSpecial xlPasteValues


Any help would ne much appreciate...

when i debug it is highlighting the last line of the code,
Range("a" & xm).PasteSpecial xlPasteValues

 

[frank_AL]

I found a similar issue on a Microsoft board and the issue was the file was still in "Compatibility Mode" and he was exceeding the 64,000 row limit of older versions of Excel.

Check this out and see if it resolves your issue.

"[FONT=&quot]This was the problem in my case:[/FONT][FONT=&quot]
[/FONT]
[FONT=&quot]1. I had saved the destination file as .xlsx format, but it was still in Compatibility Mode (I didn't realize this)[/FONT]
[FONT=&quot]2. The data I was pasting would have gone beyond the ~64,000 row limit of .xls files in the destination workbook[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Therefore: it wouldn't paste.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]The solution was to close the destination workbook (after saving, of course), and then re-open. When it was opened again, it was no longer in compatibility mode, and I was able to paste the data."

The link to this is: https://answers.microsoft.com/en-us...y/1c1327b2-2420-49a5-84a5-52b73f38f70a?page=2[/FONT]

 

[Godders199]

thanks your reply. The destination workbook is not in compatibility Mode and we are only on line 4800 so dont think that is the problem