VBA to select user multiple selected ranges

**longbow007** · 02-10-2010, 12:39 AM

Hi, could some one please help me to do the following?

Please refer to attached Excel 2003 screen-capture.

The User has made multiple selections with the mouse.

The spreadsheet is filtered.

The user will usually make different multiple selections on the following columns:

A and X through to AR (inclusive).

I just need some code to capture these various multiple selected ranges so that I can copy the selected range as shown below:

I have searched Google and not found an answer to what I need.

Any assistance would be greatly appreciated.

kind regards,

Chris.

Selection.Copy
    Sheets("Data to Text").Select
    ActiveSheet.Paste

**Mallycat** · 02-10-2010, 02:26 AM

You don't make it clear where you want the data pasted in the new sheet. ie should it go into the same cells from the source sheet? This is what this macro will do for you.

Sub copyMultiRange()
    Dim myRange As Range
    Set myRange = Selection
    Sheets("Data to Text").Select
    For Each cell In myRange
        Range(cell.Address).Value = cell.Value
    Next cell
End Sub

**longbow007** · 02-10-2010, 06:14 AM

Hello Mallycat - I am sorry that I did not make myself very clear. The Excel screen-shot was taken from worksheet named: Data

I am going to copy 'n' paste the user's selection in the same workbook but on worksheet named: Data to Text at cell A1

I am having a small problem compiling your code.

Excel is advising me that the cell variable is not defined.

If you need further clarification, please do not hesitate to contact me.

Kind regards,

Chris.

**longbow007** · 02-10-2010, 06:07 PM

Hello again Mallycat, I changed the worksheet name and the code you provided works well - thank you. Also, I do not get any more compile errors.

Cheers,

Chris :-)