Run Time Error '1004' PasteSpecial method of Range class failed

[Kittrell]

Okay, so this one is driving me batty. Very simple, I am pasting data from one workbook to another. I am doing this several times as I have to pull the data under certain assigned accounts. I receive the above error for this line of code, Selection.PasteSpecial Paste:=xlPasteValues. I have read through countless threads with this issue and the only conclusion I have come to is that possibly this bit of code that is running between the copy and paste is clearing the clipboard:

    Windows("DC OIF " & " " & Format(Now(), "MMDDYY")).Activate
    Sheets("ZOTCM DD").Select
        Set WS = ActiveSheet
        For Each Cell In WS.Columns(1).Cells
        If IsEmpty(Cell) = True Then Cell.Select: Exit For
        Next Cell
    'Select next available empty cell in column A

Full code

Sub PER_OIF_ZOTCM_Update()'
'ZOTCM Update - Ctrl + Shift + Z
'


'
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("DC OIF " & " " & Format(Now(), "MMDDYY")).Activate
    Sheets("ZOTCM DD").Select
        Set WS = ActiveSheet
        For Each Cell In WS.Columns(1).Cells
        If IsEmpty(Cell) = True Then Cell.Select: Exit For
        Next Cell
    'Select next available empty cell in column A
    
    Selection.PasteSpecial Paste:=xlPasteValues
    'Paste SAP data
    
    Range("A2:T2").CurrentRegion.Sort key1:=Range("Q2"), order1:=xlAscending, Header:=xlGuess
    'Sort SAP data
    
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.RefreshAll
    'Refresh Workbook
    
    Sheets("Summary").Select
    
    
End Sub

Any thoughts? Since I need it to select the next empty cell to paste the data, as not to overwrite what is already there, I am not sure how I could remove or move this bit of code.

 

[taurean]

Those select and windows syntax can be gotten rid of but for that you need to describe what you are trying to achieve.

Instead of looping you can use:

Sheets("ZOTCM DD").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

 

[Kittrell]

It ended up being that the tabs were hidden. So I wrote in code to unhide and rehide the tabs, problem solved!