paste special code

[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

 

[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

 

[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

 

[NDBC]

Thanks, worked a treat.

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

 

[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.

 

[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