VBA - Copy Paste value base on the header

[SmartCookie]

Hello friends and community,

I am very new to vba and trying to figure out how to do below.

I need to copy a pivot table's data from one workbook to another
Copy from the highlighted header (Week number)

Paste to the highlighted header in another sheet

Difficulty & Concern:

1. the header doesn't not start in the first column & first Rows in both the origin & destination sheet
2. the destination sheet's header might not in order as the origin sheet. eg, sometimes the origin header might be skipping a week because there is no data in that week, but in the destination sheet it has all the week
3. I have searched around and found below code using the 'scripting dictionary', but the code is written where by default both origin and destination header start in the first row/column. I tried to modify the code but never able to get it to work the way i want. If anyone can help me understand how this code work, or help me come up with a new method that does the work it will be great.

Dim HeaderRow_A As Long
Dim HeaderLastColumn_A As Long
Dim TableColStart_A As Long
Dim NameList_A As Object
Dim SourceDataStart As Long
Dim SourceLastRow As Long
Dim Source As Variant

Dim i As Long

Dim ws_B_lastCol As Long
Dim NextEntryline As Long
Dim SourceCol_A As Long

Set wb = ActiveWorkbook
Set ws_A = wb.Worksheets("WIP-RSWW1")
Set ws_B = wb.Worksheets("Base Data_RS")
Set NameList_A = CreateObject("Scripting.Dictionary")

With ws_A
    SourceDataStart = 2
    HeaderRow_A = 2  'set the header row in sheet A
    TableColStart_A = 2 'Set start col in sheet A
    HeaderLastColumn_A = .Cells(HeaderRow_A, Columns.Count).End(xlToLeft).Column  'Get number of NAMEs you have

    For i = TableColStart_A To HeaderLastColumn_A
        If Not NameList_A.Exists(UCase(.Cells(HeaderRow_A, i).Value)) Then  'check if the name exists in the dictionary
             NameList_A.Add UCase(.Cells(HeaderRow_A, i).Value), i 'if does not exist record name as KEY and Column number as value in dictionary
        End If
    Next i

End With




With ws_B  'worksheet you want to paste data into
    ws_B_lastCol = .Cells(HeaderRow_A, Columns.Count).End(xlToLeft).Column ' Get number of DATA you have in sheet B
    For i = 1 To ws_B_lastCol   'for each data
        SourceCol_A = NameList_A(UCase(.Cells(1, i).Value))  'get the column where the name is in Sheet A from the dictionaary

        If SourceCol_A <> 0 Then  'if 0 means the name doesnt exists
            SourceLastRow = ws_A.Cells(Rows.Count, SourceCol_A).End(xlUp).Row
            Set Source = ws_A.Range(ws_A.Cells(SourceDataStart, SourceCol_A), ws_A.Cells(SourceLastRow, SourceCol_A))
            NextEntryline = .Cells(Rows.Count, i).End(xlUp).Row + 1 'get the next entry line of the particular name in sheet A

            .Range(.Cells(NextEntryline, i), _
                   .Cells(NextEntryline, i)) _
                   .Resize(Source.Rows.Count, Source.Columns.Count).Cells.Value = Source.Cells.Value
        End If

    Next i
End With


End Sub

 

[SmartCookie]

Anybody able to help here?