Macro to Copy and paste values in a specified column to a column in another worksheet

[LOPA]

Hi,
i have a "payroll sheet" which have a cell (say D2)containing the week#. There is data in a certain column let say "C" which i want to copy to my "Consolidated sheet". I am looking for a macro which can copy the values in column C in payroll sheet to the appropriate column in the consolidated sheet e.g. if the value in Cell D2 is 5 then it should paste the values in column E of consolidated sheet. if it is 6 then paste the values in column F of concolidated sheet. Basically the payroll sheet will be updated every week with Cell D2 showing the week# and i want the consolidated sheet to show the data for all the weeks in seperate columns. hope i am clear. help will be greatly appreciated! Thanks.

 

[JoeMo]

Try this:

Sub ConsolidatePayrollData3()
Const pSh = "Payroll"  'Adjust to your sheet name
Const cSh = "Consolidated"  'Adjust to your sheet name
Dim wRng As Range, cRng As Range, sSh As Worksheet, rSh As Worksheet, lRw As Long
Set sSh = Worksheets(pSh)
Set rSh = Worksheets(cSh)
Set wRng = sSh.Range("G2")   'adjust to your range
lRw = sSh.Range("C" & Rows.Count).End(xlUp).Row
Set cRng = sSh.Range("C2", "E" & lRw)
With Application
    .ScreenUpdating = False
End With
cRng.Copy
rSh.Range("1:1").Find(wRng.Value, after:=rSh.Range("A1"), _
    LookIn:=xlValues, lookat:=xlWhole).Offset(1, 0).PasteSpecial Paste:=xlValues
With Application
    .ScreenUpdating = True
    .CutCopyMode = False
End With

End Sub

 

[LOPA]

Excellent!. it works beautiful