Simple copying and pasting macro

[Guest]

Hi,

I am pretty much a novice in vba, but I think what I am trying to do is
pretty simple.

I wrote the following code

Public Sub Qtr1()
Dim cell1 As Range
For Each cell1 In Worksheets("projection").Range("E5:E69")
cell1.Resize(1, 1).Copy
With Worksheets("vol qtr")
.Range("J5").PasteSpecial Paste:=xlValues
End With
Next
Application.CutCopyMode = False
End Sub

The purpose of the code is to copy and paste values from one sheet into
another. This code works without any problems! However, I want to do the
following: after it has copied E5:E69 into "vol qtr"
I want it to copy E5:E69 into "price qtr" cell (J5)

I tried just repeating the same code (changing the variable from cell1 to
cell2) after next statement, but that does not work. I want the entire
process to be within the same macro so that it can be executed with a click
on just one button.

Thank you, in advance, for your help.

Henrik

 

[Trevor Shuttleworth]

Henrik

one way:

Sub test2()
With Worksheets("projection")
.Range("E5:E69").Copy
End With
With Worksheets("vol qtr")
.Range("J5").PasteSpecial Paste:=xlPasteValues
End With
With Worksheets("price qtr")
.Range("J5").PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub

or, more simply:

Sub test3()
Worksheets("projection").Range("E5:E69").Copy
Worksheets("vol qtr").Range("J5").PasteSpecial Paste:=xlPasteValues
Worksheets("price qtr").Range("J5").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Regards

Trevor