VBA to move to right of active cells

ridgetown_rick

Board Regular
Joined
Aug 28, 2002
Messages
193
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
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:

Code:
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

Board Regular
Joined
Aug 28, 2002
Messages
193
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
This code will paste in the same row as the ActiveCell's row:
Code:
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

Board Regular
Joined
Aug 28, 2002
Messages
193
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

Board Regular
Joined
Aug 28, 2002
Messages
193
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,291
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top