Thread: Copy and Paste Using a Macro

Hi

you need to use the "paste values" option in paste special

this macro will copy the values from cells J2:J100 in sheet "days" to cell B2 in sheet "weeks":

Sub paste_values()
    Sheets("Days").Range("j2:j100").Copy
    Sheets("Weeks").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Assuming you want to paste this week's data in the column after last week's data in sheet "weeks", this macro will paste the values from J2:J100 in the first empy cell to the right of cell A2 in sheet "weeks":

Sub paste_values2()
    Sheets("Days").Range("j2:j100").Copy
    Sheets("Weeks").Select
    Range("A2").Select
    Do While ActiveCell.Value <> ""
        ActiveCell.Offset(0, 1).Select
    Loop
    Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Hi NickyC
Your formula works a treat with one minor problem - on executing the 2nd macro you proposed, the cells on the "Days" sheet in Column "J" remain selected with the marquee.
Can this be stopped?

Hi
adding this before the End Sub should clear it:

Application.CutCopyMode = False

Hi Nicky
Yes that worked - you are a champion!!!!

glad to be of help