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]

Welcome to the Board.
Does the data in column C begin in C1 or C2? Do you want the paste to begin in the first row of the consolidated sheet or the second row (i.e. does the sheet have a header)?

 

[LOPA]

Sorry forgot to mention.. data will start from C2 and the sheet here it will be pasted will also have header row.

 

[JoeMo]

Try this. Note that you may have to adjust the sheet names as noted with comments in the code:

Sub ConsolidatePayrollData()
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("D2")   'adjust to your range
lRw = sSh.Range("C" & Rows.Count).End(xlUp).Row
Set cRng = sSh.Range("C2", "C" & lRw)
With Application
    .ScreenUpdating = False
End With
cRng.Copy
rSh.Cells(2, wRng.Value).PasteSpecial Paste:=xlValues
With Application
    .ScreenUpdating = True
    .CutCopyMode = False
End With

End Sub

 

[LOPA]

Thanks! it works like a charm. is it possible that if i put numbers in the header row of consolidated sheet (1-52) and macro checks the number in cell D2 and then puts the data in appropriate column. I really appreciate your help

 

[JoeMo]

Thanks! it works like a charm. is it possible that if i put numbers in the header row of consolidated sheet (1-52) and macro checks the number in cell D2 and then puts the data in appropriate column. I really appreciate your help

It is possible. Try this (your header numbers are assumed to be in the first row of the consolidated sheet, they need not be in consecutive columns).

Sub ConsolidatePayrollData2()
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("D2")   'adjust to your range
lRw = sSh.Range("C" & Rows.Count).End(xlUp).Row
Set cRng = sSh.Range("C2", "C" & 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]

Works exactly the way i wanted.Thanks you so much!

 

[LOPA]

Hope i am not asking for too much. is it possible that instead of copy and pasting only Column C it pastes the data in column C,D, E and F together in a bunch. the header row in consolidated sheet will have 4 columns with same week number . Also instead of D2, F2 will have the week number on payroll sheet. Thanks in advance for your help!

 

[JoeMo]

If you include column F in the data that means that the week # in F2 is part of the data to be copied and pasted into the consolidated sheet. Is that what you want?

 

[LOPA]

Sorry i meant to say week # will be G2 not F2 on pay roll sheet