Copy Selection to new sheet

[James]

Hi

How can I copy Multiple Selection selected by user to copy
to a new sheet using VBA. Excel 2002, win 2k
Please advise

Thanks a lot
James

 

[Bob Phillips]

Hi James,

You can't copy multiple selections in one go, so try something like

For Each area In Range("A1:A6,B1:B3,G2:J4,C1616")
area.Copy Destination:=Worksheets("Sheet1").Range(area.Address)
Next area

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[TH]

Try this in a macro:

Sub CopyUserSelectedCells()
Dim mySelection as Range
Set mySelection = Selection 'Assigns the current user selection,
'even if it's multiple non-contiguous cells
For Each C In mySelection
'copy each cell here to wherever you want
Worksheets("Name of new sheet").Range("Cell to copy to") = C
Next
End Sub


TH

 

[Dave Peterson]

I think Bob meant this:

Dim area As Range
For Each area In Range("A1:A6,B1:B3,G2:J4,C1616").Areas
area.Copy Destination:=Worksheets("Sheet1").Range(area.Address)
Next area

Without the .areas, it'll do it cell by cell (which will work).

but judging by the variable name....