Macro Goal
After Selecting any range of cells in a first worksheet,
copy contents of the selection
Paste the contents of the selected cells at a second worksheet without using
..paste link:=True.
The cells have to correspond. For instance, content of A2:A4 in the first
worksheet are placed in A2:A4 in the second worksheet. I'm using this
approach for copying formats and values of the selected cells without copying
the entire worksheet.

My approach is not to be specific on calling A2:A4. Therefore using Range
("A2:A4).Select is not an option I need.


I'm trying to get the reference of the first cell in a selected range in the
first worksheet and have the macro automatically move to a second worksheet
and select the first cell corrsonding to the first cell in the selected
range. I hope this make sense.

so far I got this far which pastes the contents starting at the first cell I
last selected in the second worksheet.

Sub copyrangeselected()

Set rng=Selection
rng.copy 'copies cells in my first worksheet
Sheets("Sheet2").Select

Activesheet.Paste

End Sub

Thanks.

Maximouse

I think I understand and this should do what you need

Sub copyrangeselected()
Dim rng As Range
Dim sAddr As String
Set rng = Selection
sAddr = rng.Address
rng.Copy Destination:=Worksheets("Sheet2").Range(sAddr)
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"maximouse" wrote in message
...
Macro Goal
After Selecting any range of cells in a first worksheet,
copy contents of the selection
Paste the contents of the selected cells at a second worksheet without
using
.paste link:=True.
The cells have to correspond. For instance, content of A2:A4 in the first
worksheet are placed in A2:A4 in the second worksheet. I'm using this
approach for copying formats and values of the selected cells without
copying
the entire worksheet.

My approach is not to be specific on calling A2:A4. Therefore using Range
("A2:A4).Select is not an option I need.


I'm trying to get the reference of the first cell in a selected range in
the
first worksheet and have the macro automatically move to a second
worksheet
and select the first cell corrsonding to the first cell in the selected
range. I hope this make sense.

so far I got this far which pastes the contents starting at the first cell
I
last selected in the second worksheet.

Sub copyrangeselected()

Set rng=Selection
rng.copy 'copies cells in my first worksheet
Sheets("Sheet2").Select

Activesheet.Paste

End Sub

Thanks.

in this simple example, i have a range called testrange, c6:h16

this this code will give you the address of the upper left cell of the range
called testrange in the immediate window, which is c6


Sub test()
Range("testrange").Select
Debug.Print Range("testrange").Range("A1").Address
End Sub


--


Gary


"maximouse" wrote in message
...
Macro Goal
After Selecting any range of cells in a first worksheet,
copy contents of the selection
Paste the contents of the selected cells at a second worksheet without
using
.paste link:=True.
The cells have to correspond. For instance, content of A2:A4 in the first
worksheet are placed in A2:A4 in the second worksheet. I'm using this
approach for copying formats and values of the selected cells without
copying
the entire worksheet.

My approach is not to be specific on calling A2:A4. Therefore using Range
("A2:A4).Select is not an option I need.


I'm trying to get the reference of the first cell in a selected range in
the
first worksheet and have the macro automatically move to a second
worksheet
and select the first cell corrsonding to the first cell in the selected
range. I hope this make sense.

so far I got this far which pastes the contents starting at the first cell
I
last selected in the second worksheet.

Sub copyrangeselected()

Set rng=Selection
rng.copy 'copies cells in my first worksheet
Sheets("Sheet2").Select

Activesheet.Paste

End Sub

Thanks.

forgot to mention, you don't actually need the Range("testrange").Select
line.
it is just there for illustration purposes when you look at the sheet.


--


Gary


"maximouse" wrote in message
...
Macro Goal
After Selecting any range of cells in a first worksheet,
copy contents of the selection
Paste the contents of the selected cells at a second worksheet without
using
.paste link:=True.
The cells have to correspond. For instance, content of A2:A4 in the first
worksheet are placed in A2:A4 in the second worksheet. I'm using this
approach for copying formats and values of the selected cells without
copying
the entire worksheet.

My approach is not to be specific on calling A2:A4. Therefore using Range
("A2:A4).Select is not an option I need.


I'm trying to get the reference of the first cell in a selected range in
the
first worksheet and have the macro automatically move to a second
worksheet
and select the first cell corrsonding to the first cell in the selected
range. I hope this make sense.

so far I got this far which pastes the contents starting at the first cell
I
last selected in the second worksheet.

Sub copyrangeselected()

Set rng=Selection
rng.copy 'copies cells in my first worksheet
Sheets("Sheet2").Select

Activesheet.Paste

End Sub

Thanks.

Mr. Hodge, you have mastered my question. Gracias.

"Nick Hodge" wrote:

Maximouse

I think I understand and this should do what you need

Sub copyrangeselected()
Dim rng As Range
Dim sAddr As String
Set rng = Selection
sAddr = rng.Address
rng.Copy Destination:=Worksheets("Sheet2").Range(sAddr)
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"maximouse" wrote in message
...
Macro Goal
After Selecting any range of cells in a first worksheet,
copy contents of the selection
Paste the contents of the selected cells at a second worksheet without
using
.paste link:=True.
The cells have to correspond. For instance, content of A2:A4 in the first
worksheet are placed in A2:A4 in the second worksheet. I'm using this
approach for copying formats and values of the selected cells without
copying
the entire worksheet.

My approach is not to be specific on calling A2:A4. Therefore using Range
("A2:A4).Select is not an option I need.


I'm trying to get the reference of the first cell in a selected range in
the
first worksheet and have the macro automatically move to a second
worksheet
and select the first cell corrsonding to the first cell in the selected
range. I hope this make sense.

so far I got this far which pastes the contents starting at the first cell
I
last selected in the second worksheet.

Sub copyrangeselected()

Set rng=Selection
rng.copy 'copies cells in my first worksheet
Sheets("Sheet2").Select

Activesheet.Paste

End Sub

Thanks.