Sub Step1_CopyRange()
Dim lr As Long
Application.ScreenUpdating = False
With Sheets("ad_hoc") 'Source worksheet name
Set DestSh = Sheets("test") 'Destination worksheet name
'START of confirmation message box'
response = MsgBox("Run Macro?", vbYesNo)
If response = vbNo Then
MsgBox ("Macro Canceled!")
Exit Sub
End If
'END of confirmation message box'
lr = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
'Ir = last column of x
.Range(.Cells(17, "B"), .Cells(lr, "B")).Copy DestSh.Cells(4, "A")
.Range(.Cells(17, "C"), .Cells(lr, "C")).Copy DestSh.Cells(4, "B")
.Range(.Cells(17, "E"), .Cells(lr, "E")).Copy DestSh.Cells(4, "C")
.Range(.Cells(17, "H"), .Cells(lr, "H")).Copy DestSh.Cells(4, "D")
.Range(.Cells(17, "Q"), .Cells(lr, "Q")).Copy DestSh.Cells(4, "E")
.Range(.Cells(17, "AD"), .Cells(lr, "AD")).Copy DestSh.Cells(4, "F")
.Range(.Cells(17, "R"), .Cells(lr, "R")).Copy DestSh.Cells(4, "G")
.Range(.Cells(17, "S"), .Cells(lr, "S")).Copy DestSh.Cells(4, "H")
.Range(.Cells(17, "T"), .Cells(lr, "T")).Copy DestSh.Cells(4, "I")
.Range(.Cells(17, "U"), .Cells(lr, "U")).Copy DestSh.Cells(4, "J")
.Range(.Cells(17, "V"), .Cells(lr, "V")).Copy DestSh.Cells(4, "K")
.Range(.Cells(17, "W"), .Cells(lr, "W")).Copy DestSh.Cells(4, "L")
.Range(.Cells(17, "X"), .Cells(lr, "X")).Copy DestSh.Cells(4, "M")
.Range(.Cells(17, "Y"), .Cells(lr, "Y")).Copy DestSh.Cells(4, "N")
.Range(.Cells(17, "Z"), .Cells(lr, "Z")).Copy DestSh.Cells(4, "O")
.Range(.Cells(17, "AA"), .Cells(lr, "AA")).Copy DestSh.Cells(4, "P")
.Range(.Cells(17, "AB"), .Cells(lr, "AB")).Copy DestSh.Cells(4, "Q")
.Range(.Cells(17, "AC"), .Cells(lr, "AC")).Copy DestSh.Cells(4, "R")
End With
Application.ScreenUpdating = True
'START MSG'
MsgBox "Copy Completed!"
Exit Sub
'End MSG'
End Sub