Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
updating data w/o activating another sheet...
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
updating data w/o activating another sheet...
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 "Steve Barber" wrote in message ... 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 -- Regards, Tom Ogilvy "Ark_Bouldering" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
updating data w/o activating another sheet...
On Fri, 25 Feb 2005 05:27:03 -0800, "Ark_Bouldering"
wrote: 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." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically updating sheet upon data Refresh All? | Excel Discussion (Misc queries) | |||
updating external data sheet causes loss of data on linked pages | Excel Worksheet Functions | |||
Auto Updating Filtered Data on Next Sheet | Excel Discussion (Misc queries) | |||
Updating excel sheet with selected data from another sheet in the same file | Excel Worksheet Functions | |||
Problem in activating a sheet | Excel Programming |