| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
paste special code
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
paste special code
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 On 22 Jul., 08:33, NDBC wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
paste special code
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(a rrData, 1), _ UBound(arrData, 2)) = arrData If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
paste special code
Thanks, worked a treat.
"Per Jessen" wrote: 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 On 22 Jul., 08:33, NDBC wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
paste special code
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" wrote: 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(a rrData, 1), _ UBound(arrData, 2)) = arrData If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
paste special code
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 -- If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: 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" wrote: 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(a rrData, 1), _ UBound(arrData, 2)) = arrData If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Paste Special - Values code | Excel Programming | |||
| Simplify Code for Copy/Paste Special | Excel Programming | |||
| Code Assistance (Paste Special) | Excel Programming | |||
| Modify this code to paste special, values | Excel Programming | |||
| Copy Paste Special Value using Code over Several Worksheets | Excel Programming | |||
Linear Mode
