| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy paste - Values - formula - Optimise
Hi have have code as below and I am looking to Optimise the same.. something
like.. sheets("calc").Range("B1:B200").Value= sheets("IndBIDs").Range("A1:A200").Value sheets("calc").Range("A1:A200") = sheets("calc").Range("A1:A200").Value But range will be dynamic as per below code.. 1st............ Sheets("IndBIDs").Select Application.Goto Reference:="R1C1" Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("calc").Select Application.Goto Reference:="R2C1" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False 2nd........... Selection.Copy Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy paste - Values - formula - Optimise
Hi
This should do it: Dim TargetSh As Worksheet Dim DestSh As Worksheet Dim CopyCell As Range Dim DestCell As Range Set TargetSh = Sheets("IndBIDs") Set DestSh = Sheets("calc") With TargetSh .Range("A1", .Range("A1").End(xlDown)).Copy End With DestSh.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False '2nd........... With DestSh Set CopyCell = .Range("A1") ' change cell as desired End With Set DestCell = Range(CopyCell, CopyCell.End(xlDown)) CopyCell.Copy DestCell.PasteSpecial Paste:=xlPasteFormulas, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False DestCell.Copy DestCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Regards, Per "Kashyap" skrev i meddelelsen ... Hi have have code as below and I am looking to Optimise the same.. something like.. sheets("calc").Range("B1:B200").Value= sheets("IndBIDs").Range("A1:A200").Value sheets("calc").Range("A1:A200") = sheets("calc").Range("A1:A200").Value But range will be dynamic as per below code.. 1st............ Sheets("IndBIDs").Select Application.Goto Reference:="R1C1" Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("calc").Select Application.Goto Reference:="R2C1" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False 2nd........... Selection.Copy Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy paste - Values - formula - Optimise
Hi, I do not want to do copy | pastespecial | values.. I need to assign value
only.. "Per Jessen" wrote: Hi This should do it: Dim TargetSh As Worksheet Dim DestSh As Worksheet Dim CopyCell As Range Dim DestCell As Range Set TargetSh = Sheets("IndBIDs") Set DestSh = Sheets("calc") With TargetSh .Range("A1", .Range("A1").End(xlDown)).Copy End With DestSh.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False '2nd........... With DestSh Set CopyCell = .Range("A1") ' change cell as desired End With Set DestCell = Range(CopyCell, CopyCell.End(xlDown)) CopyCell.Copy DestCell.PasteSpecial Paste:=xlPasteFormulas, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False DestCell.Copy DestCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Regards, Per "Kashyap" skrev i meddelelsen ... Hi have have code as below and I am looking to Optimise the same.. something like.. sheets("calc").Range("B1:B200").Value= sheets("IndBIDs").Range("A1:A200").Value sheets("calc").Range("A1:A200") = sheets("calc").Range("A1:A200").Value But range will be dynamic as per below code.. 1st............ Sheets("IndBIDs").Select Application.Goto Reference:="R1C1" Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("calc").Select Application.Goto Reference:="R2C1" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False 2nd........... Selection.Copy Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy and paste up to values in last row | Excel Discussion (Misc queries) | |||
| Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
| Copy/Paste Values | Excel Programming | |||
| Urgent Help needed on! Copy and Paste a Formula as Values using a Loop statement | Excel Programming | |||
| How do i compare values from two sheet and copy & paste if values match? | Excel Programming | |||
Linear Mode
