Keep calculation time down in large Workbooks
 

Dear All,

I have a really large workbook and am trying to keep the calculation time
down as much as possible. In one of the sheets I am trying to use the
following code each time there is a change in range "Input". The formulas to
be copied each time are sitting in cells G2 and H2.

It is working fine when using dropdown boxes because the cursor will stay in
the same cell. However when entering the new data in "Input" and pressing
Enter or Down arrow the formula will be copied onto the row immediately
below. When I press Up arrow the formula will be copied onto the row
immediately above.

When pressing left or right arrow nothing happens.

Any help would be much appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ThisCell As Range

For Each ThisCell In Selection.Cells
If Not Intersect(ThisCell, [Input]) Is Nothing Then
[G2:H2].Copy
ThisCell.Offset(0, 1).PasteSpecial xlPasteFormulas
Range(ThisCell, ThisCell.Offset(0, 1)).Offset(0, 1).Copy
Range(ThisCell, ThisCell.Offset(0, 1)).Offset(0,
1).PasteSpecial xlPasteValues
End If
Next ThisCell

End Sub

--
Regards,

Martin

Keep calculation time down in large Workbooks
 

Shouldn't you be copying relative to Target, rather than selection.

--
Regards,
Tom Ogilvy


"Martin" wrote:

Dear All,

I have a really large workbook and am trying to keep the calculation time
down as much as possible. In one of the sheets I am trying to use the
following code each time there is a change in range "Input". The formulas to
be copied each time are sitting in cells G2 and H2.

It is working fine when using dropdown boxes because the cursor will stay in
the same cell. However when entering the new data in "Input" and pressing
Enter or Down arrow the formula will be copied onto the row immediately
below. When I press Up arrow the formula will be copied onto the row
immediately above.

When pressing left or right arrow nothing happens.

Any help would be much appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ThisCell As Range

For Each ThisCell In Selection.Cells
If Not Intersect(ThisCell, [Input]) Is Nothing Then
[G2:H2].Copy
ThisCell.Offset(0, 1).PasteSpecial xlPasteFormulas
Range(ThisCell, ThisCell.Offset(0, 1)).Offset(0, 1).Copy
Range(ThisCell, ThisCell.Offset(0, 1)).Offset(0,
1).PasteSpecial xlPasteValues
End If
Next ThisCell

End Sub

--
Regards,

Martin

Keep calculation time down in large Workbooks
 

Many thanks Tom. You are absolutely right.
--
Regards,

Martin


"Tom Ogilvy" wrote:

Shouldn't you be copying relative to Target, rather than selection.

--
Regards,
Tom Ogilvy


"Martin" wrote:

Dear All,

I have a really large workbook and am trying to keep the calculation time
down as much as possible. In one of the sheets I am trying to use the
following code each time there is a change in range "Input". The formulas to
be copied each time are sitting in cells G2 and H2.

It is working fine when using dropdown boxes because the cursor will stay in
the same cell. However when entering the new data in "Input" and pressing
Enter or Down arrow the formula will be copied onto the row immediately
below. When I press Up arrow the formula will be copied onto the row
immediately above.

When pressing left or right arrow nothing happens.

Any help would be much appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ThisCell As Range

For Each ThisCell In Selection.Cells
If Not Intersect(ThisCell, [Input]) Is Nothing Then
[G2:H2].Copy
ThisCell.Offset(0, 1).PasteSpecial xlPasteFormulas
Range(ThisCell, ThisCell.Offset(0, 1)).Offset(0, 1).Copy
Range(ThisCell, ThisCell.Offset(0, 1)).Offset(0,
1).PasteSpecial xlPasteValues
End If
Next ThisCell

End Sub

--
Regards,

Martin