updating data w/o activating another sheet...

[Guest]

I am needing to update information on one sheet w/ information from another
sheet. I want it to update the information in the background...anotherwords
I do not want the sheets to switch back and forth when retrieving and
updating information from one sheet to another. I am fairly new at this.
Any help is very much appreciated!

 

[Tom Ogilvy]

Someone asked a similar question a little while ago. Perhaps the answer I
gave that person will give you some ideas:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nMth As Long, sVal As String
Dim rng As Range, rng1 As Range
Dim res As Variant
If Target.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
nMth = Month(Date)
sVal = Cells(Target.Row, 1)
With Worksheets("sheet2")
Set rng = .Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
res = Application.Match(sVal, rng, 0)
If Not IsError(res) Then
Set rng1 = rng(res)
rng1.Offset(0, nMth).Value = Target.Value
End If

End Sub

--
Regards,
Tom Ogilvy

I have two sheets, one which has the latest price of an item and another
which maintains a history of the price per item, I need to be able to update
the historical price on sheet 2 in the correct column when the price is
changed on sheet1.

Example

Sheet1

Item Price
A 10
B 12


Sheet2

Item Jan Feb Mar Apr...
A 8 9 8 10
B 12 11 10 12

The values in sheet2 for April need to be updated by the changes to the
values in sheet1

The column offset is particularly perplexing me!

Anyone got any ideas

Many thanks in advance

 

[vickie_raven]

I am needing to update information on one sheet w/ information from another
sheet. I want it to update the information in the background...anotherwords
I do not want the sheets to switch back and forth when retrieving and
updating information from one sheet to another. I am fairly new at this.
Any help is very much appreciated!

try this

ScreenUpdating Property
See Also Applies To Example Specifics
True if screen updating is turned on. Read/write Boolean.

Remarks
Turn screen updating off to speed up your macro code. You won't be
able to see what the macro is doing, but it will run faster.

Remember to set the ScreenUpdating property back to True when your
macro ends.

Example
This example demonstrates how turning off screen updating can make
your code run faster. The example hides every other column on Sheet1,
while keeping track of the time it takes to do so. The first time the
example hides the columns, screen updating is turned on; the second
time, screen updating is turned off. When you run this example, you
can compare the respective running times, which are displayed in the
message box.

Dim elapsedTime(2)
Application.ScreenUpdating = True
For i = 1 To 2
If i = 2 Then Application.ScreenUpdating = False
startTime = Time
Worksheets("Sheet1").Activate
For Each c In ActiveSheet.Columns
If c.Column Mod 2 = 0 Then
c.Hidden = True
End If
Next c
stopTime = Time
elapsedTime(i) = (stopTime - startTime) * 24 * 60 * 60
Next i
Application.ScreenUpdating = True
MsgBox "Elapsed time, screen updating on: " & elapsedTime(1) & _
" sec." & Chr(13) & _
"Elapsed time, screen updating off: " & elapsedTime(2) & _
" sec."