Copy Selection to new sheet

J

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
 
B

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,C16:D16")
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)
 
T

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
 
D

Dave Peterson

I think Bob meant this:

Dim area As Range
For Each area In Range("A1:A6,B1:B3,G2:J4,C16:D16").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....
 
Top