See attached.
Sub Transfer_data()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim outcol As Integer
Dim var As Variant
Set ws1 = Worksheets("Rej Rep")
ws_Name = ws1.Range("B4")
Set ws2 = Worksheets(ws_Name) ' Set worksheet name
'
' Determine output column bassed on date in "Rej Rep" E3
'
var = Application.Match(ws1.Range("E3"), Worksheets(ws_Name).Range("A4:AE4"), 0)
outcol = var
'
' Copy data from "Rej Rep"
'
ws1.Range("C8:C30").Copy Destination:=ws2.Cells(5, outcol)
ws1.Range("C7").Copy Destination:=ws2.Cells(29, outcol)
ws_Name = ws1.Range("E4")
Set ws2 = Worksheets(ws_Name)
ws1.Range("F8:F30").Copy Destination:=ws2.Cells(5, outcol)
ws1.Range("F7").Copy Destination:=ws2.Cells(29, outcol)
ws_Name = "PC GF"
Set ws2 = Worksheets(ws_Name)
ws1.Range("I38:I49").Copy Destination:=ws2.Cells(4, outcol)
ws1.Range("I37").Copy Destination:=ws2.Cells(17, outcol)
ws_Name = "PC SF"
Set ws2 = Worksheets(ws_Name)
ws1.Range("L38:L49").Copy Destination:=ws2.Cells(4, outcol)
ws1.Range("l37").Copy Destination:=ws2.Cells(17, outcol)
ws_Name = "GF LC"
Set ws2 = Worksheets(ws_Name)
ws1.Range("O38:O49").Copy Destination:=ws2.Cells(4, outcol)
ws1.Range("O37").Copy Destination:=ws2.Cells(17, outcol)
End Sub
Bookmarks