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

Davavo

Board Regular
Joined
Aug 3, 2019
Messages
76
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

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

Code:
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
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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.
Code:
Application.CutCopyMode = False
 

Davavo

Board Regular
Joined
Aug 3, 2019
Messages
76
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.
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
It might look something like this.
Rich (BB code):


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
 

Forum statistics

Threads
1,082,258
Messages
5,364,088
Members
400,779
Latest member
lumers

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top