Type mismatch error?!?

[adamenel]

Hi guys,

I'm desperately trying to resolve type mismatch error but no results. Any tips?
This button is supposed to copy all data from actual worksheet/table (SourceTable) to the end of summary table (DestinationTable) in other sheet.

Private Sub CommandButton1_Click()

Dim SourceTable, DestinationTable As ListObject


Set SourceTable = ActiveSheet.ListObjects("ZN")
Set DestinationTable = Sheets("Zbirni").ListObjects("ZBIRNI")

SourceTable.DataBodyRange.Copy Destination:=DestinationTable.DataBodyRange.Offset(DestinationTable.DataBodyRange.Rows.Count).Resize(1, 1).PasteSpecial(SourceTable).xlPasteValues

End Sub

 

[DanteAmor]

Hi @adamenel, Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Please, try the following code:

Private Sub CommandButton1_Click()
  Dim SourceTable, dt As ListObject

  Set SourceTable = ActiveSheet.ListObjects("ZN")
  Set dt = Sheets("Zbirni").ListObjects("ZBIRNI")
  
  SourceTable.DataBodyRange.Copy dt.DataBodyRange.Offset(dt.DataBodyRange.Rows.Count).Resize(1, 1)
End Sub

Or this:

Private Sub CommandButton1_Click()
  Dim SourceTable, dt As ListObject
  Dim nrow As Long

  Set SourceTable = ActiveSheet.ListObjects("ZN")
  Set dt= Sheets("Zbirni").ListObjects("ZBIRNI")
  dt.ListRows.Add AlwaysInsert:=True
  nrow = dt.DataBodyRange.Rows.Count
  SourceTable.DataBodyRange.Copy dt.ListRows(nrow).Range
End Sub

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[adamenel]

Hi @adamenel, Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Please, try the following code:

Private Sub CommandButton1_Click()
  Dim SourceTable, dt As ListObject

  Set SourceTable = ActiveSheet.ListObjects("ZN")
  Set dt = Sheets("Zbirni").ListObjects("ZBIRNI")
 
  SourceTable.DataBodyRange.Copy dt.DataBodyRange.Offset(dt.DataBodyRange.Rows.Count).Resize(1, 1)
End Sub

Or this:

Private Sub CommandButton1_Click()
  Dim SourceTable, dt As ListObject
  Dim nrow As Long

  Set SourceTable = ActiveSheet.ListObjects("ZN")
  Set dt= Sheets("Zbirni").ListObjects("ZBIRNI")
  dt.ListRows.Add AlwaysInsert:=True
  nrow = dt.DataBodyRange.Rows.Count
  SourceTable.DataBodyRange.Copy dt.ListRows(nrow).Range
End Sub

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Hi Dante,

Thanks for the warm welcome and your prompt response. The second option works fine. Somehow after execution of code (copy/paste process) a range of cells is left selected in source sheet. Any idea how to remove that selection? Another question is how to paste content in destination table shifted by 1 column to the right?

Regards,
Adam

 

[DanteAmor]

Somehow after execution of code (copy/paste process) a range of cells is left selected in source sheet. Any idea how to remove that selection? Another question is how to paste content in destination table shifted by 1 column to the right?

Try this:

Private Sub CommandButton1_Click()
  Dim SourceTable As ListObject, dt As ListObject
  Dim nrow As Long

  Set SourceTable = ActiveSheet.ListObjects("ZN")
  Set dt = Sheets("Zbirni").ListObjects("ZBIRNI")
  dt.ListRows.Add AlwaysInsert:=True
  nrow = dt.DataBodyRange.Rows.Count
  SourceTable.DataBodyRange.Copy dt.DataBodyRange.Cells(nrow, 2)
  SourceTable.DataBodyRange.Cells(1).Select
End Sub

 

[adamenel]

Brilliant! Big thank you!