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

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

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