Keep calculation time down in large Workbooks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Many thanks Tom. You are absolutely right.
--
Regards,

Martin


Tom Ogilvy said:
Shouldn't you be copying relative to Target, rather than selection.

--
Regards,
Tom Ogilvy


Martin said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Setting Cell Values from VBA 1
Copy row info but not in normal column sequence 6
Import multiple workbooks to current workbook in specified row number 0
Convert A1 value to a sheet name and copy to it 2
Select Case Code does not run... 10
COPY BY MACRO DOWN TO LAST VALUE CELL IN COLUMN "A" 1
Select Case code error 8
Scattered array cells copy to scattered array cells another workbook 14

Back
Top