How to copy and paste dynamic table values in next empty cell?

Question

I'm looking to select cells with values from a dynamic table, and copy and paste these values into another worksheet with a table where the next empty cell is available.

I've only been able to find code that will copy and paste a defined range into the next empty cell or copy and paste a dynamic range with some values and empty cells.

Sub SaveKit_1()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopy As Long
Dim lDest As Long

  Set wsCopy = Worksheets("Kit_db") 'Copies table from Kit_db worksheet
  Set wsDest = Worksheets("Kit_db (1)") 'Pastes table to Kit_db (1) worksheet

  lCopy = wsCopy.Cells(wsCopy.Rows.Count, "E").End(xlUp).Row
  lDest = wsDest.Cells(wsDest.Rows.Count, "E").End(xlUp).Offset(1).Row

  'Copies any cell within the table
  wsCopy.Range("B3:I" & lCopy).Copy _ 
     wsDest.Range("B" & lDest)

End Sub

All cells in the dynamic table are copied and pasted rather than the cells in the table with values. I'm looking to paste values only as some cells in the dynamic table have formulas.

I have tried the following Paste Special

   PasteSpecial _
   Paste:=xlPasteValuesAndNumberFormats, _
   Operation:=xlPasteSpecialOperationNone, _
   SkipBlanks:=True, _
   Transpose:=False

But I don't think I placed it correctly with the existing code or I'm using the wrong type of code for paste.

Either paste special, pasting values, or do a value transfer. — BigBen
Transfer values through some resize of your destination and leave the copy paste if you not interested in formatting. — JvdV

Bilal Bilal · Accepted Answer · 2019-10-29T20:42:05

Here is an example of paste special as values with your code

Sub SaveKit_1()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopy As Long
Dim lDest As Long

  Set wsCopy = Worksheets("Kit_db") 'Copies table from Kit_db worksheet
  Set wsDest = Worksheets("Kit_db (1)") 'Pastes table to Kit_db (1) worksheet

  lCopy = wsCopy.Cells(wsCopy.Rows.Count, "E").End(xlUp).Row
  lDest = wsDest.Cells(wsDest.Rows.Count, "E").End(xlUp).Offset(1).Row

  'Copies any cell within the table
  wsCopy.Range("B3:I" & lCopy).Copy
  wsDest.Range("B" & lDest).PasteSpecial xlPasteValues

End Sub

How to copy and paste dynamic table values in next empty cell?

1 Answers