I am looking for a macro which copies the data from the selected
range(Dynamic range) and pastes it into a different sheet. I tried recording
a macro and run it,but the range is fixed. The range should be the selected
text and should not be fixed range.
Thanks in advance.

It would help if you posted the macro, but often the solution is to change code like

Range("A2:C10").Copy .....

to

Selection.Copy

HTH,
Bernie
MS Excel MVP


"Satish" wrote in message
...
I am looking for a macro which copies the data from the selected
range(Dynamic range) and pastes it into a different sheet. I tried recording
a macro and run it,but the range is fixed. The range should be the selected
text and should not be fixed range.
Thanks in advance.

Hi,
Try this is working for me, change columns as needed

Pick a column between J and O (inclusive) that will have the most information
displayed down the sheet. For this example we'll assume it's column M, plus
this keeps us with a method that works with any of the columns, not just J or
O

Dim lastRow as Long
dim rngAddress as String
dim rngToCopy as Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow


that gives you address of the range and you can use that address to set a
range as:
Set rngToCopy = ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

then go from there for your copy/paste into the other workbook and
worksheet. A generic way (change workbook/sheet names as needed) - this does
the same as copy, assuming second workbook is open:
(this would be all one line)

Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress).Value =
rngToCopy.Value

That would copy it into J10:O## in the other book.



"Satish" wrote:

I am looking for a macro which copies the data from the selected
range(Dynamic range) and pastes it into a different sheet. I tried recording
a macro and run it,but the range is fixed. The range should be the selected
text and should not be fixed range.
Thanks in advance.

Thanks Eduardo & Bernie.

"Eduardo" wrote:

Hi,
Try this is working for me, change columns as needed

Pick a column between J and O (inclusive) that will have the most information
displayed down the sheet. For this example we'll assume it's column M, plus
this keeps us with a method that works with any of the columns, not just J or
O

Dim lastRow as Long
dim rngAddress as String
dim rngToCopy as Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow


that gives you address of the range and you can use that address to set a
range as:
Set rngToCopy = ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

then go from there for your copy/paste into the other workbook and
worksheet. A generic way (change workbook/sheet names as needed) - this does
the same as copy, assuming second workbook is open:
(this would be all one line)

Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress).Value =
rngToCopy.Value

That would copy it into J10:O## in the other book.



"Satish" wrote:

I am looking for a macro which copies the data from the selected
range(Dynamic range) and pastes it into a different sheet. I tried recording
a macro and run it,but the range is fixed. The range should be the selected
text and should not be fixed range.
Thanks in advance.