VBA code, insert code end of column

[plshelpimpoor]

<a href="http://tinypic.com?ref=ega2xg" target="_blank"><img src="http://i64.tinypic.com/ega2xg.png" border="0" alt="Image and video hosting by TinyPic"></a>

Sub CopyToAnotherSheet2()'copy and transpose
Dim NextRow As Long
Dim cpRng As Range


Application.CutCopyMode = False
Application.ScreenUpdating = False


    Set cpRng = ActiveSheet.Range("c31:ab31")
    NextRow = Sheets("weekly").Cells(Rows.Count, 1).End(xlUp).Row + 1
    Sheets("Summary").Range("p4:p29").Copy
        Sheets("weekly").Cells(NextRow, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True


Application.CutCopyMode = True
Application.ScreenUpdating = True
End Sub

Above is the code. How do I get the code to run in column C (and below the previous week data) instead of at the very bottom?

 

[Trevor_S]

Try changing the following lines as shown:

    NextRow = Sheets("weekly").Cells(Rows.Count - 1, 3).End(xlDown).Row + 1
    Sheets("Summary").Range("p4:p29").Copy
        Sheets("weekly").Cells(NextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True