Hello,

I'm totally new to macros in Visual Basic and need help. I'm running Office
2003 in Windows XP.

I tried to record a macro used to copy and rearrange information from one
worksheet to another.

When I recorded the macro, it used hard coded ranges which makes it
impossible to move information to different locations in the destination
spreadsheet. Instead, I'd like the macro to place the information RELATIVE
to the currenly selected cell in the destination worksheet.

I've included the macro text below. Notice all the absolute references on
the "NEW" or destination worksheet. I'd like to convert all those absolute
range references to relative ranges. Is that possible?

Is it possible at the time of recording a macro to tell Excel to keep all
range references relative to the currently active cell in each worksheet?

~~~~~~~~~~~~
Selection.Copy
Sheets("NEW").Select
Range("A51:A58").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=
_
xlNone, SkipBlanks:=False, Transpose:=False
Range("B43:B50").Select
Application.CutCopyMode = False
Selection.Copy
Range("B51").Select
ActiveSheet.Paste
Sheets("2003").Select
Range("C762:J762").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("NEW").Select
Range("C51").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Sheets("2003").Select
Range("K762").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("NEW").Select
Range("D51:D58").Select
ActiveSheet.Paste
Range("A59").Select
~~~~~~~~~~~~

Thanks in advance for your help!

Ed

When you activate the macro recorder, you should get a toolbar w/two buttons
on it. One is to stop recording, the other turns on/off relative references.

You can use relative references in your code

Activecell.Range("B2").Select

This will select the cell one column to the right and one row down from the
activecell.

You can also use the offset method:

Activecell.Offset(1,1).Select

Which will also select the cell one column to the right and one row down
from the activecell.

Or something like:
ActiveCell.Offset(1, 1).Range("A1:G1").Select

Which will select a range that is 1 row by 7 columns. The leftmost cell in
this range will be one column to the right and one row down from the active
cell.


"edself" wrote:

Hello,

I'm totally new to macros in Visual Basic and need help. I'm running Office
2003 in Windows XP.

I tried to record a macro used to copy and rearrange information from one
worksheet to another.

When I recorded the macro, it used hard coded ranges which makes it
impossible to move information to different locations in the destination
spreadsheet. Instead, I'd like the macro to place the information RELATIVE
to the currenly selected cell in the destination worksheet.

I've included the macro text below. Notice all the absolute references on
the "NEW" or destination worksheet. I'd like to convert all those absolute
range references to relative ranges. Is that possible?

Is it possible at the time of recording a macro to tell Excel to keep all
range references relative to the currently active cell in each worksheet?

~~~~~~~~~~~~
Selection.Copy
Sheets("NEW").Select
Range("A51:A58").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=
_
xlNone, SkipBlanks:=False, Transpose:=False
Range("B43:B50").Select
Application.CutCopyMode = False
Selection.Copy
Range("B51").Select
ActiveSheet.Paste
Sheets("2003").Select
Range("C762:J762").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("NEW").Select
Range("C51").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Sheets("2003").Select
Range("K762").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("NEW").Select
Range("D51:D58").Select
ActiveSheet.Paste
Range("A59").Select
~~~~~~~~~~~~

Thanks in advance for your help!

Ed

Thank you! I clicked relative references, re-recorded the macro and it
worked!

"JMB" wrote in message
...
When you activate the macro recorder, you should get a toolbar w/two
buttons
on it. One is to stop recording, the other turns on/off relative
references.

You can use relative references in your code

Activecell.Range("B2").Select

This will select the cell one column to the right and one row down from
the
activecell.

You can also use the offset method:

Activecell.Offset(1,1).Select

Which will also select the cell one column to the right and one row down
from the activecell.

Or something like:
ActiveCell.Offset(1, 1).Range("A1:G1").Select

Which will select a range that is 1 row by 7 columns. The leftmost cell
in
this range will be one column to the right and one row down from the
active
cell.


"edself" wrote:

Hello,

I'm totally new to macros in Visual Basic and need help. I'm running
Office
2003 in Windows XP.

I tried to record a macro used to copy and rearrange information from one
worksheet to another.

When I recorded the macro, it used hard coded ranges which makes it
impossible to move information to different locations in the destination
spreadsheet. Instead, I'd like the macro to place the information
RELATIVE
to the currenly selected cell in the destination worksheet.

I've included the macro text below. Notice all the absolute references
on
the "NEW" or destination worksheet. I'd like to convert all those
absolute
range references to relative ranges. Is that possible?

Is it possible at the time of recording a macro to tell Excel to keep all
range references relative to the currently active cell in each worksheet?

~~~~~~~~~~~~
Selection.Copy
Sheets("NEW").Select
Range("A51:A58").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:=
_
xlNone, SkipBlanks:=False, Transpose:=False
Range("B43:B50").Select
Application.CutCopyMode = False
Selection.Copy
Range("B51").Select
ActiveSheet.Paste
Sheets("2003").Select
Range("C762:J762").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("NEW").Select
Range("C51").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Sheets("2003").Select
Range("K762").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("NEW").Select
Range("D51:D58").Select
ActiveSheet.Paste
Range("A59").Select
~~~~~~~~~~~~

Thanks in advance for your help!

Ed

When recording a macro a toolbar with two options should be displayed.
One is: Stop Recording, another toggles to: Relative References.

Tomek Polak
www.vba.blog.onet.pl

On Wed, 12 Oct 2005 18:16:01 -0700, JMB
wrote:

When you activate the macro recorder, you should get a toolbar w/two buttons
on it. One is to stop recording, the other turns on/off relative references.

Seems to be missing on my Macro Recorder. Any ideas how I re-instate
this please?

Have I screwed up some Registry setting perhaps?

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi Richard

From a posting by Bernie Dietrich

Sub MakeToolbarVisible()
With Application.CommandBars("Stop Recording")
.Visible = True
.Enabled = True
End With
End Sub

If you close the toolbat with the "X" button, whilst recording a macro
it disappears.
Stop recording the macro with STOP and not "X"

Regards

Roger Govier



Richard Buttrey wrote:

On Wed, 12 Oct 2005 18:16:01 -0700, JMB
wrote:



When you activate the macro recorder, you should get a toolbar w/two buttons
on it. One is to stop recording, the other turns on/off relative references.



Seems to be missing on my Macro Recorder. Any ideas how I re-instate
this please?

Have I screwed up some Registry setting perhaps?

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi Roger,

Thanks for that.

I've certainly got the extra toolbar although I'm still slightly
puzzled.

The original VB toolbar has the Run, Record, Security, VB Editor,
Control Toolbox, Design Mode and MS Script Editor icons.

This new one has the Record Macro and Relative Reference icon. However
the Record Macro is greyed out and inoperative.

The Relative Reference icon is correctly acting as a toggle but I
have to use the Record icon from the original toolbar.

Non of this is a particular problem, but I'm just wondering why the
icons aren't combined in one toolbar.

Rgds






On Thu, 13 Oct 2005 13:09:43 +0100, Roger Govier
wrote:

Hi Richard

From a posting by Bernie Dietrich

Sub MakeToolbarVisible()
With Application.CommandBars("Stop Recording")
.Visible = True
.Enabled = True
End With
End Sub

If you close the toolbat with the "X" button, whilst recording a macro
it disappears.
Stop recording the macro with STOP and not "X"

Regards

Roger Govier


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________