Copy and paste selected dynamic data columns from one sheet to another sheet

0
votes

I want to copy data from specific column in Sheet 1 to a specific column in Sheet 2. There are 20 such columns and that mapping is maintained in a table like

enter image description here

I have written the code to search column name (source and destination sheets) from but am unable to copy the data from source column (dynamic range) to destination column.

 Sub search_validate()

    Dim j As Integer

    Dim sourcSearch, destSearch As String
    Dim sCell, dCell As Range

    For j = 3 To 20

        sourcSearch = Sheet6.Range("Z" & j).Value ' pickup selected source column name 
        destSearch = Sheet6.Range("AA" & j).Value ' pickup selected destination column name 

        Set sCell = Sheet1.Rows(2).Find(What:=sourcSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        Set dCell = Sheet2.Rows(2).Find(What:=destSearch, LookIn:=xlValues, _
          LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
          MatchCase:=False, SearchFormat:=False)

    '  sCell.Address or sCell.Column to get source column header address but data starts after this column. dynamic range

    '  dCell.Address or dCell.Column to get destination column header address. no data in destination column except header.

    Next j

End Sub
2
excelvba

2 Answers

1
votes

This should append the data to the end of the destination column

    If Not sCell Is Nothing And Not dCell Is Nothing Then
        Dim Source As Range, Target As Range
        Set Source = Intersect(Sheet1.UsedRange, sCell.EntireColumn).Offset(1)
        Set Target = Sheet2.Cells(Sheet2.Rows.Count, dCell.Column).End(xlUp).Offset(1)
        Source.Copy Destination:=Target
    End If
0
votes

You need to paste new data after last row in your current table? U can find last row with this:

lastRow = Columns("Enter your column number here").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Then just make a copy-paste, like this:

Set copyRange = Range(YOUR RANGE HERE)
 copyRange.Copy
Cells(lastRow + 1, "Enter your column number here").PasteSpecial Paste:=xlPasteValues