Macro to copy & paste data

[Satish]

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.

 

[Bernie Deitrick]

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

 

[Eduardo]

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(rngAddress)

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]

Thanks Eduardo & Bernie.

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(rngAddress)

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.