No announcement yet.

excel vba Copying columns from one table to another

  • Filter
  • Time
  • Show
Clear All
new posts

  • excel vba Copying columns from one table to another

    I'm a beginner in the VBA, I found this code with which I transfer data from the table named "GreensTable" with columns to another table. I've raised the columns of the GreensTable table, what should I change in the code so I can select some of the columns and not move the whole table. If done with someone ListBox. How do I select columns to copy from a table and draw it to the second table Thank you in advance

    Option Explicit
    Sub CopyToPivotTables(ByVal SourceList As ListObject, _
    ByVal TargetList As ListObject, _
    ByVal rng As Range, _
    ByVal rng1 As Range)

    Dim i As Long, t As Long, s As Long, c As Long, j As Long, lstRow As ListRow

    If IsEmpty(rng.Cells(1)) Then Exit Sub
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    c = SourceList.ListColumns.Count
    s = WorksheetFunction.Subtotal(3, rng)

    Range(ActiveCell, ActiveCell.Offset(0, 5)).Copy

    With TargetList
    t = .ListRows.Count + 1
    .Resize .Range.Resize(t + s, .ListColumns.Count)
    i = t

    For Each lstRow In SourceList.ListRows
    If Not lstRow.Range.EntireRow.Hidden Then
    TargetList.ListRows(i).Range(1, 9).Value = "P"
    TargetList.ListRows(i).Range.Offset(, 1).Resize(1, c).Value = lstRow.Range.Value
    i = i + 1
    End If
    End With

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.CutCopyMode = False
    If t + s - 1 > 0 Then MsgBox "OK", vbInformation, ""

    End Select
    End Sub

    Sub CopyGreenTable()
    CopyToPivotTables Range("GreenTable").ListObject, _
    Range("SalesTable").ListObject, _
    Range("GreenTable").Columns(1), _

    End Sub
    Last edited by dimogiannis; February 27th, 2018, 16:27.

  • #2
    I attach the file
    Attached Files


    • #3

      no idea?