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
|