Copy Two Columns from One Table and Paste in Second Table

[Maryna]

I have table tblActivties on sheet Activities and tblTrainingLog on sheet TrainReg. I want to copy two columns from tbleActivties and paste them in the corresponding columns on sheet TrainReg. The amount of columns to copy is known and the number of rows to copy in tblActivities in unknown (last row to be determined). There is already many filled rows in tblTrainingLog and I want the data to be pasted to the first blank cell. I have code to copy one column but not sure how to expand this to copying two columns. I need the fastest solution as I will be copy/pasting over 100 rows
[P.S. The top and bottom of my code is an attempt to speed up the code but I'm not sure if this is effective]
[P.S.S. I am very new to VBA so looking to build something basic and slowly upgrade as I learn more efficient code]

QUESTION: How do i update code to copy range A10:B10 to lastrow in tblActivities in sheet Activities and paste data in first empty cell in tblTrainingReg in sheet TrainReg

Public Sub TurnOffFunctionality() 'To run code fast turn off certain VBA functionality at the start of our code. Turn it back on, at the end of our code.

Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.ScreenUpdating = False

End Sub

Private Sub CommandButton1_Click()

Dim lastrow As Long, nextrow As Long 'Define variable type lastrow and nextrow

lastrow = Worksheets("Activities").Cells(Rows.Count, 1).End(xlUp).Row 'Check the last filled line on sheet named TrainReg

For i = 10 To lastrow 'Loop will run from row 10 on Activities Sheet to last filled row in table

Worksheets("Activities").Cells(i, 2).Copy 'Copy the row
nextrow = Worksheets("TrainReg").Cells(Rows.Count, 13).End(xlUp).Row 'Evaluating how many rows are already filled in table TrainReg
Worksheets("Activities").Paste Destination:=Worksheets("TrainReg").Cells(nextrow + 1, 6)

Next i 'Closes loop

Worksheets("TrainReg").Activate 'Returns to TrainReg Sheet
Application.CutCopyMode = False

End Sub

Public Sub TurnOnFunctionality()

Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[igold]

Does this get you close to what you want to do...

Sub CommandButton1_Click()

    Dim tblAct As Object, TlastRow As Object
    
    Set tblAct = Worksheets("Activities").ListObjects("tblActivities")
    Set TlastRow = Worksheets("TrainReg").ListObjects("tblTrainingLog").ListRows.Add
    tblAct.Range.Rows("10:" & tblAct.ListRows.Count + 1).Copy
    TlastRow.Range.PasteSpecial
    Application.CutCopyMode = False
    
    
End Sub

 

[Maryna]

Yes thank you it gets me very close. The only issue is that it pastes the 'Activities' table (which is three columns wide) several times across the 'TrainReg' table (which is 10 columns wide). I want columns A and B from 'Activities' table to be copy-pasted into columns E and F of 'TrainReg table'

 

[Maryna]

I updated the following lines as follows to only copy the two specified columns and paste them in the correct cells in the second table.

tblAct.Range.Columns("A:B").Rows("2:" & tblAct.ListRows.Count + 1).Copy
TlastRow.Range.Offset(0, 3).PasteSpecial Paste:=xlPasteValues

I am just not sure if this is the most eloquent solution. I want to change "A:B" to named columns incase a new column is added to the table and the column reference changes

 

[igold]

Does your changes work for you... If not, could you explain what you need using the table column numbers as opposed to the sheet column numbers. For example if your table starts in cell G3 and you want to work with column H, then that is really column 2 of your table. Same thing with the row numbers

This is a great resource for working with tables. Have a look at it:

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

 

[Maryna]

Yes it works. Apologies if I am confusing the terminology. Thank you for the resource, it explains a lot.

Essentially I am trying to create a training register. For each employee I must show all the possible activities the employee can be trained in and their training status for each activity (i.e. trained, in training or trained). I've attached screen shots to hopefully help illustrate what I am trying to explain.

The table 'tblActivities' list all the activities that a person can be trained in. New activities can be added to this table. To start off with I want to be able to click a button and add all these activities and their corresponding ID (hence the two columns in the table) to the corresponding columns in table tblTrainingReg.
The data in table 'tblActivties' in the sheet 'Activities' starts in cell A2 and I want to copy the first two columns of the table (which is in column A and B of Activities sheet). I want to paste this data in column 4 and 5 of the table tblTrainingLog (which is column E and F of the sheet)

 

[igold]

Ok, I am a little confused as your post #1 and post #6 seem to conflict as to which rows of the tblActivites you want to paste and as to what row you want it pasted to in the tblTrainingLog.

The below code will take all of the tblActivities (Columns 1 & 2) and paste that to the first row without data in the tblTrainingLog in columns 4 & 5. If this is not correct then let me know what needs to be changed and we can try again.

Sub CommandButton1_Click()

    Dim tblAct As Object, tblLog As Object
    Set tblLog = Worksheets("TrainReg").ListObjects("tblTrainingLog")
    Set tblAct = Worksheets("Activities").ListObjects("tblActivities")
    
    tblLog.ListRows.Add AlwaysInsert:=True
    With tblAct
        Range(.DataBodyRange(1, 1), tblAct.DataBodyRange(.ListRows.Count, 2)).Copy
    End With
    With tblLog
        .DataBodyRange(.ListRows.Count, .ListColumns.Count).Offset(0, -6).PasteSpecial
    End With
    Application.CutCopyMode = False
    
    
End Sub

 

[Maryna]

Yes thank you so much for your help. I've achieved my first goal. Going to play around with some more vba code to build even more on my register. (And learn to state my problems in nonconfusing ways).

 

[igold]

You are welcome. I was happy to help. Thanks for the feedback!