PDA

View Full Version : Importing and updating "Master" worksheet.



ozibryan
04-04-2006, 04:59 AM
The master sheet contains a number of values that may be updated by either the update or refresh sheets as this information is available.

When applying the update sheet the following needs to occur:

When the sheet, Update Exchange value matches the master Exchange value the corresponding Capacity value needs to be reflected within the master,. An example is the Master capacity value for ABCF7 "after update is applied".

Additionally, the following events are needed when applying the Refresh sheet to the Master::

When an exchange on the Refresh sheet matches the Master exchange, the Refresh Upgrade value is applied to the Master Projected value for the corresponding exchange.

This would be subsequently added to capacity within the projected date column as per ABCF7 & ABCDA1 examples.

I hope this gives a bit more clarity to the problem I am trying to resolve. Please let me know if I can explain this with more clarity

jindon
04-04-2006, 10:36 PM
paste the code onto Master sheet module
not sure if this is what you wanted..
it only updates "capacity" acc to the data in "Update"

Note: Sheet name "Update" is actually "Update " , sapce at the end
therefore, delete the space before you paste the code, otherwise,
it will delete the data and gives error.

if this is not what you wanted, post the file with your desired results...

Private Sub Worksheet_Activate()
Dim dic As Object, a, w
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
With Range("a1").CurrentRegion
With .Offset(1).Resize(.Rows.Count - 1)
a = .Value
.ClearContents
End With
End With
For i = 1 To UBound(a, 1)
If Not IsEmpty(a(i, 1)) Then
If Not dic.exists(a(i, 1)) Then
ReDim w(1 To UBound(a, 2))
For ii = 1 To UBound(a, 2)
w(ii) = a(i, ii)
Next
dic.Add a(i, 1), w
End If
End If
Next
With Sheets("Update")
a = .Range("a2", .Range("a" & Rows.Count).End(xlUp)).Resize(, 3).Value
End With
For i = 2 To UBound(a, 1)
If dic.exists(a(i, 1)) Then
w = dic(a(i, 1))
w(3) = a(i, 2)
dic(a(i, 1)) = w
End If
Next
If dic.Count = 0 Then Exit Sub
y = dic.items: Set dic = Nothing: Erase a
With Range("a2")
For i = 0 To UBound(y)
.Offset(i).Resize(, UBound(y(i))) = y(i)
Next
End With
End Sub