VBA alternative to copy paste syntax that doiesnt use paste .... clipboard popup

[Davavo]

Hi,
if i use the current syntax with this macro i get an annoying "clipboard has a lot of info on it" popup.
To avoid it i would like to use the non paste method, but i cant figure out the appropriate syntax for the situation.

Thanks for any help.
This is the code section in question

ws.Range("A2:N" & Usdrws).Copy

If Testcell <> "" Then


    Trgtws.Range("A" & Lastrow).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    'Sheets("Trgtws").Cells (???????)


Else


    Trgtws.Range("A" & Lastrow).PasteSpecial Paste:=xlPasteValues


End If

This is the whole macro ...


This macro allows the user to select a workbook and then loops through all of the sheets on that workbook to copy data.
It first checks a specific column (i), if that column it has a specific value in the header, ("Invoice Type") then it copies the entire row and pastes it to a sheet on the workbook that has the macro, appending a table.
In the current iteration there is no need to resize the table. If i use the non paste syntax, i understand i will have to resize the table. I already have the code for that part. I just cant get the syntax for the paste right.
Thanks

Sub InvoiceReadMacro()


  Dim wb As Workbook
  Dim tw As Workbook
  Dim ws As Worksheet
  Dim Trgtws As Worksheet, myFile
  Dim ob As ListObject
  Dim Lrow1 As Long
  Dim Usdrws As Long
  Dim Lastrow As Long
  Dim Testcell As Range
  Dim INVRead As Worksheet
  Dim j As Long
  Dim CheckHeader As Range
  
  Set tw = ThisWorkbook
  Set Trgtws = tw.Sheets("INVRead")
  Set Testcell = Trgtws.Cells(3, 9)
  
  Application.ScreenUpdating = False
  
  'open workbook
  myFile = Application.GetOpenFilename(, , "Browse for Workbook")
  Trgtws.Range("z2") = myFile
  Set wb = Workbooks.Open(Filename:=myFile, ReadOnly:=False)


For Each ws In wb.Worksheets


Set CheckHeader = ws.Range("I1")


If InStr(CheckHeader.Value, "Invoice Type") = 1 Then
    
    On Error Resume Next
    Columns.EntireColumn.Hidden = False
    Rows.EntireRow.Hidden = False
    
If ws.Visible = True Then
    ws.Activate
    ActiveWindow.FreezePanes = False
End If
    
    ActiveSheet.Cells.ClearFormats
       
    '------------------------------------------------------------
        
    For j = Cells(Rows.Count, "I").End(xlUp).Row To 1 Step -1
    On Error Resume Next
    If Cells(j, "I") = "" Then Cells(j, "I").EntireRow.Delete xlUp
    
    Next j
    
    '-------------------------------------------------------
    
    Lastrow = Trgtws.Cells(Rows.Count, 1).End(xlUp).Row
    Usdrws = ws.Range("I" & Rows.Count).End(xlUp).Row
    
    ws.Range("A2:N" & Usdrws).Copy


If Testcell <> "" Then


    Trgtws.Range("A" & Lastrow).Offset(1, 0).PasteSpecial Paste:=xlPasteValues    '(this works but i end up with a large amount of data on the clipboardf which causes a popup) 
    'Sheets("Trgtws").Cells (???????)                                                                   'i am not sure how to phrase this


Else


    Trgtws.Range("A" & Lastrow).PasteSpecial Paste:=xlPasteValues


End If
End If


Next ws


    'resize the table  (Not required with pastespecial method)


    'Lrow1 = Sheets("INVRead").Cells(Rows.Count, "I").End(xlUp).Row
    'Set wb = ThisWorkbook
    'Set ws = wb.Worksheets("INVRead")
    'Set ob = ws.ListObjects("TINVRead")


    'ob.Resize ob.Range.Resize(Lrow1)
    
    MsgBox "Input Complete"
    ActiveWorkbook.Close False
    Application.ScreenUpdating = True
    Trgtws.Activate
    Range("A1").Select
    
End Sub

 

[Norie]

Are you only copying values?

If you are then you could use arrays, i.e. put the values you want to copy into an array and then put those values into the destination range.

If it's not only values that's not an option, however you can get rid of the pop-up by clearing the clipboard with this.

Application.CutCopyMode = False

 

[Davavo]

Are you only copying values?

If you are then you could use arrays, i.e. put the values you want to copy into an array and then put those values into the destination range.

If it's not only values that's not an option, however you can get rid of the pop-up by clearing the clipboard with this.

Application.CutCopyMode = False

Great, thanks!
I didn't realise that Application.CutCopyMode = False did that. Figured i didn't need to include it since i was closing the worksheet.

Sorry, it is just values, but i don't know how to do this

put the values you want to copy into an array and then put those values into the destination range.

Just for my education, I would like to know the proper syntax for the cells method if anyone can.

Thanks for the help Norie, Application.CutCopyMode = False works great!

 

[Norie]

It might look something like this.

arrVals = ws.Range("A2:N" & Usdrws).Value

If Testcell <> "" Then


    Trgtws.Range("A" & Lastrow).Offset(1, 0).Resize(UBound(arrVals,1), UBound(arrVals,2)).Value = arrVals

Else

    Trgtws.Range("A" & Lastrow).Resize(UBound(arrVals,1), UBound(arrVals,2)).Value = arrVals

End If