VBA Copy and Past

[Martin_Man]

Hi there,

I've searched endlessly on google for an answer but can't find nowt.

Basically I've created a command button in excel and so far so good it copies the Data>Opens a new Workbook and pasts it. I need it so when it pasts the data into the new workbook it pasts as text rather then value. Here is my code:

Private Sub cmdCopy_Click()

Range("B10:N3000").Select
Selection.Copy
Application.Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


End Sub

Whenever I try and edit

 Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

to include Format:="Text" there's a bug. Is there anyone that can help me with this problem?

 

[dazzerd]

You simply need to change :

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


to:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

As far as I know there is not a format parameter for the pastespecial method.

By the way using pastespecial is not a great approach and can be pretty slow. You probably want to directly refer from source sheet to target sheet.

Either by individual cell or by setting a couple of range objects. If you have no idea what I'm going on about that don't worry

 

[Souleh]

Why use copy/paste when you can just do something like;
Worksheet(newsheet).range(x).value = Worksheet(oldsheet).range(y).value

Then you can add the following;
Worksheet(newsheet).range(x) = format(Worksheet(newsheet).range(x), {formatType})

http://www.techonthenet.com/excel/formulas/format_string.php