Copy & Paste Values In Next Column From B2

[outlook]

Hi All,

I have two worksheets:

"Pipeline"
"Weekly Pipelines"

I need to copy data from B1 to B4 on "Pipeline" and paste values to the next available column after B2 in "Weekly Pipelines".

Would anybody be able to help me with the code as I have tried lots of different codes, and nothing seems to work exactly how I need it to.

Thank you so much!

 

[VoG]

Assuming that you really did mean next available column (not row) try

Sub test()
Dim LC As Integer
LC = Sheets("Weekly Pipelines").Cells(2, Columns.Count).End(xlToLeft).Column + 1
Sheets("Pipeline").Range("B1:B4").Copy Destination:=Sheets("Weekly Pipelines").Cells(2, LC)
End Sub

 

[outlook]

I definately meant next column.

Thank you for the code, it's works a treat.

The only problem I have now is that it's posting the formular that I have in B1:B4, and not values.

Can this be done?

Thank you.

 

[VoG]

Sorry, I missed the values part. Try

Sub test()
Dim LC As Integer
LC = Sheets("Weekly Pipelines").Cells(2, Columns.Count).End(xlToLeft).Column + 1
Sheets("Pipeline").Range("B1:B4").Copy
Sheets("Weekly Pipelines").Cells(2, LC).PasteSpecial Paste:=xlPasteValues
End Sub

 

[outlook]

Excellent, it works!!!

Thank you.

 

[outlook]

Would you be able to help me further ...

If I wanted something similar for another worksheet, but for rows instead, would I just change "LC" to "LR" and "Columns" to "Rows"?

Thanks, again!

 

[VoG]

It is easier with rows. For example

Sub test2()
Sheets("Pipeline").Range("B1:B4").Copy
Sheets("Weekly Pipelines").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End Sub

 

[outlook]

Sorry to be a pain, but would this still work if one of the worksheets was in another workbook? If so, how?

Thanks.

 

[VoG]

With both workbooks open

Sub test2()
Workbooks("Book1.xls").Sheets("Pipeline").Range("B1:B4").Copy
Workbooks("Book2.xls").Sheets("Weekly Pipelines").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End Sub

 

[outlook]

Yet again, it works and you have made me very happy!!!

Thank you.