Copying user selected cells to a new worksheet using VBA
 

Is there a way I can make a macro which copies USER-selected cells to a
new worksheet?

PS - My ultimate task is to do mail merge with these User-selected
cells..

thx


--
dvd
------------------------------------------------------------------------
dvd's Profile: http://www.excelforum.com/member.php...o&userid=27834
View this thread: http://www.excelforum.com/showthread...hreadid=473429

Copying user selected cells to a new worksheet using VBA
 

Hi dvd

Try this

Sub Copy_Selection()
Dim source As Range
Dim dest As Workbook

Set source = Nothing
On Error Resume Next
Set source = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "The source is not a range or the sheet is protect, please correct and try again.", vbOKOnly
Exit Sub
End If

If ActiveWindow.SelectedSheets.Count 1 Or _
Selection.Cells.Count = 1 Or _
Selection.Areas.Count 1 Then
MsgBox "An Error occurred :" & vbNewLine & vbNewLine & _
"You have more than one sheet selected." & vbNewLine & _
"You only selected one cell." & vbNewLine & _
"You selected more than one area." & vbNewLine & vbNewLine & _
"Please correct and try again.", vbOKOnly
Exit Sub
End If

Application.ScreenUpdating = False
Set dest = Workbooks.Add(xlWBATWorksheet)
source.Copy
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
' If you use Excel 97 use the other example
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

Application.ScreenUpdating = True
End Sub


See also
http://www.mvps.org/dmcritchie/excel/mailmerg.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"dvd" wrote in message ...

Is there a way I can make a macro which copies USER-selected cells to a
new worksheet?

PS - My ultimate task is to do mail merge with these User-selected
cells..

thx


--
dvd
------------------------------------------------------------------------
dvd's Profile: http://www.excelforum.com/member.php...o&userid=27834
View this thread: http://www.excelforum.com/showthread...hreadid=473429

Copying user selected cells to a new worksheet using VBA
 

Give this a try...

Sub CopySelection()
Dim rngCopy As Range
Dim rngPaste As Range
Dim wksNew As Worksheet

Set rngCopy = Selection
Set wksNew = Worksheets.Add
Set rngPaste = wksNew.Range("C3")

rngCopy.Copy rngPaste
End Sub
--
HTH...

Jim Thomlinson


"dvd" wrote:


Is there a way I can make a macro which copies USER-selected cells to a
new worksheet?

PS - My ultimate task is to do mail merge with these User-selected
cells..

thx


--
dvd
------------------------------------------------------------------------
dvd's Profile: http://www.excelforum.com/member.php...o&userid=27834
View this thread: http://www.excelforum.com/showthread...hreadid=473429

Copying user selected cells to a new worksheet using VBA
 

Jim Thomlinson

Using yours macro and it works great. Can I make it save the output i
a new seperate workbook? th

--
dv
-----------------------------------------------------------------------
dvd's Profile: http://www.excelforum.com/member.php...fo&userid=2783
View this thread: http://www.excelforum.com/showthread.php?threadid=47342

Copying user selected cells to a new worksheet using VBA
 

hi

if no cell is selected by the user, how do I detect this condition?
the Selection object is not null and the Range.Count returns 1, but no
cell is selected by the user.

how can I figure this out?