VBA to move to right of active cells

[ridgetown_rick]

I have recorded the following macro to copy two numbers in columns E and F (I knew enough VB to change it to ActiveCell) and paste them (transposed) into another worksheet where a complex bunch of formulas are used, then copy the output back to the original where it is pasted in as values. What I DON'T know is how to dynamically reference to the cells that are beside E and F. Right now it just hardcodes it to G23 in the line: Range("G21").select

Can you help? Thanks.
Rick

ActiveCell.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Formulae.xls").Activate
Range("C4").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Range("E16:F16").Select
Selection.Copy
Windows("614INVNT.xls").Activate
Range("G21").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

 

[Barrie Davidson]

How do you determine the cell to paste in (G21 in the example)? Also, if you're looking to speed up your code, don't select the cells. You can accomplish the same thing by using:

ActiveCell.Copy
Workbooks("Formulae.xls").Range("C4").PasteSpecial _
    Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
    , Transpose:=True
Range("E16:F16").Copy
Workbooks("614INVNT.xls").Range("G21").PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

Regards,

 

[ridgetown_rick]

Barrie,

G21 was manually selected during recording the macro. What I am doing is converting the values in E2,F2 then pasting them into G2,H2 then moving down to the next row and repeating. I just happened to be at row 21 when I decided to speed things up with a macro.

rg

 

[Barrie Davidson]

This code will paste in the same row as the ActiveCell's row:

Dim ActiveRow As Long
ActiveRow = ActiveCell.Row
ActiveCell.Copy
Workbooks("Formulae.xls").Range("C" & ActiveRow).PasteSpecial _
    Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
    , Transpose:=True
Range("E" & ActiveRow & ":F" & ActiveRow).Copy
Workbooks("614INVNT.xls").Range("G" & ActiveRow).PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

Rick, is this what you need?

 

[ridgetown_rick]

When I try your formula I get the error message:
Run Time Error 438
Object doesn't support this property or method

and it occurs in the second line of your first code (I haven't tried the revised one yet)

 

[ridgetown_rick]

For anyone who may be interested, I solved the problem with the following code:

For i = 1 To 105
Range("E" & 19 + i, "F" & 21 + i).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Formulae.xls").Activate
Range("C4").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Range("E16:F16").Select
Selection.Copy
Windows("614INVNT.xls").Activate
Range("G" & 19 + i).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next i