How to stop PasteSpecial popup message

[gorgon777]

Hi I have the below code to iterate and copy/paste data.

    Application.DisplayAlerts = False
Dim r As Range
Set r = ThisWorkbook.Sheets("POList").Range("A2:A150")
Dim sheetName
sheetName = "Expenses"


Dim XL_File
XL_File = "C:\Kros\"
'Iterate through Unique Number List
For Each cell In r
    Dim val As String
    val = cell.value
    
    'Create Excel file in memory
    Dim XL As Excel.Application
    Dim WBK As Excel.Workbook
    Set XL = CreateObject("Excel.Application")
    Set WBK = XL.Workbooks.Add
    XL.Worksheets(1).Name = "EXP"
    
    ThisWorkbook.Sheets("Data Control").Select
    ThisWorkbook.Sheets("Data Control").Range("A1:U1").Select
    ThisWorkbook.Sheets("Data Control").Range("U1").Activate
    Selection.AutoFilter
    
    ActiveSheet.Range("$A$1:$U$4300").AutoFilter Field:=19, Criteria1:="" & val
    ThisWorkbook.Sheets("Data Control").Columns("A:U").Select
    ThisWorkbook.Sheets("Data Control").Range("U1").Activate
    Selection.Copy
    XL.Sheets(sheetName).Select
    XL.Worksheets(sheetName).Columns("A:U").PasteSpecial Paste:=xlPasteValues


    DoEvents
    WBK.SaveAs (XL_File & CStr(val) & "_Expenses.xlsx")
    WBK.Application.Quit
    XL.Application.Quit
    'XL.Close
    DoEvents
    Application.DisplayAlerts = True
    Application.CutCopyMode = False
Next

This works fine, but I always get a message saying:

"Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway?"

The method completes when I click OK, but I am having to do this hundreds of time as there are a lot of files to be created.
Is there a way to stop this message?

With Thanks.

 

[JoeMo]

I haven't looked at your code in detail, but try changing this:
XL.Worksheets(sheetName).Columns("A:U").PasteSpecial Paste:=xlPasteValues
to this:
XL.Worksheets(sheetName).Range("A1").PasteSpecial Paste:=xlPasteValues

 

[gorgon777]

I haven't looked at your code in detail, but try changing this:
XL.Worksheets(sheetName).Columns("A:U").PasteSpecial Paste:=xlPasteValues
to this:
XL.Worksheets(sheetName).Range("A1").PasteSpecial Paste:=xlPasteValues

Wow, it works! I wonder why its somewhat contradicting the popup message, because surely "A1" is not the same size as "A:U"?

Thanks again, appreciate it

 

[JoeMo]

Wow, it works! I wonder why its somewhat contradicting the popup message, because surely "A1" is not the same size as "A:U"?

Thanks again, appreciate it

You are welcome. When you paste a range to a single cell, Excel assumes that you have chosen the left-most upper cell for the paste and no longer concerns itself with the size & shape of the range to be pasted!