paste special code

N

NDBC

i have copied a worksheet to a new worksheet with

wb.Sheets("A Lap").Range("a1:" & Cells(ARow, MaxA).Address).Copy
wbNew.Sheets("Sheet1").Range("A2")

I just realised the filed are now linked. how do I paste special so that
only the values get saved in the new worksheet.

I gather I have to use this

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

but don't know where to incorporate it.


Thanks
 
P

Per Jessen

Hi

Try this:

wb.Sheets("A Lap").Range("a1:" & Cells(ARow, MaxA).Address).Copy

wbNew.Sheets("Sheet1").Range("A2").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

Regards,
Per
 
J

Jacob Skaria

Try the below which is equivalent to PasteSpecial

Dim arrData as Variant
arrData = wb.Sheets("A Lap").Range("a1" , Cells(ARow, MaxA))
wbNew.Sheets("Sheet1").Range("A2").Resize(UBound(arrData, 1), _
UBound(arrData, 2)) = arrData

If this post helps click Yes
 
N

NDBC

Thanks, worked a treat.


Per Jessen said:
Hi

Try this:

wb.Sheets("A Lap").Range("a1:" & Cells(ARow, MaxA).Address).Copy

wbNew.Sheets("Sheet1").Range("A2").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

Regards,
Per
 
N

NDBC

Thanks jacob. One problem I am facing with paste special is I am loosing font
size and formatting for headings. does this method get around that and keep
formatting.
 
J

Jacob Skaria

Try the below..Repeat the paste special with formats

wb.Sheets("A Lap").Range("a1:" & Cells(ARow, MaxA).Address).Copy

wbNew.Sheets("Sheet1").Range("A2").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

wbNew.Sheets("Sheet1").Range("A2").PasteSpecial _
Paste:=xlPasteFormats Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top