I have commented out copy of "Total Rejection%" as I am not sure whether it should be calculated in "Test Report".

To remove comment, delete " ' " (single quote) in macro

' ws1.Range("O73").Copy Destination:=ws2.Cells(37, outcol)

And you should avoid using merged cells for data: it causes problems when copying from merged to unmerged cells.

In future please CLEARLY define what you require rather than leaving respondents to guess/interpret your requirements.


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 ouput 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("C5").Copy Destination:=ws2.Cells(28, 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("F5").Copy Destination:=ws2.Cells(28, 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)

ws_Name = "Test Report"
Set ws2 = Worksheets(ws_Name)

ws1.Range("I56").Copy Destination:=ws2.Cells(2, outcol)
ws1.Range("K56").Copy Destination:=ws2.Cells(3, outcol)
ws1.Range("L56").Copy Destination:=ws2.Cells(4, outcol)
ws1.Range("M56").Copy Destination:=ws2.Cells(5, outcol)

ws1.Range("H63:L63").Copy
ws2.Cells(7, outcol).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        
ws1.Range("I70:O70").Copy
ws2.Cells(14, outcol).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
                
ws1.Range("I71:O71").Copy
ws2.Cells(22, outcol).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
                                

ws1.Range("I72:O72").Copy
ws2.Cells(30, outcol).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
'
'  Rejection %
'
' ws1.Range("O73").Copy Destination:=ws2.Cells(37, outcol)


End Sub