PDA

View Full Version : Update specific data in one sheet with NEW data from another?



lmp884
06-04-2009, 09:52 AM
In the workbook at the link below, I have "box referenced" (on each sheet) where I am not able to find an solution to the issue of updating the existing sheet with new data feed from my supplier into the active running sheet. :dunno

Please look at the sheet to the very right first then it addresses the sheet next to it on the left. ... then it gets a bit more complicated (for me) with the sheets even further to the left from a different supplier.

Any help, I can't find the right function in any of the threads ... many come very close, but I can't get the result I'm looking for by trying to modify the function meant for a different task.



Please and thank you! :help


PS -- My current version of Excel is 2007 if that makes any difference.

mdmackillop
06-04-2009, 11:14 AM
It's preferable to include all the referred sheets in the sample, so best guess!

Option Explicit
Sub GetCosts()
Dim rng As Range, cel As Range, c As Range
Dim NetPart As Range
Dim AllPart As Range
Dim Costs As Range
Dim XPart As Range
Dim Cst As Single

With Sheets("NET Active")
Set NetPart = Range(.Cells(2, 3), .Cells(Rows.Count, 3).End(xlUp))
.Columns("H").ClearContents
End With
With Sheets("NET_ALL")
Set Costs = Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))
End With
With Sheets("ALL Running")
Set AllPart = Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))
End With
With Sheets("X-Active")
Set XPart = Range(.Cells(2, 3), .Cells(Rows.Count, 3).End(xlUp))
End With
For Each cel In NetPart
Cst = Costs.Find(cel).Offset(, 12)
If cel.Offset(, 3) <> Cst Then
cel.Offset(, 3) = Cst
cel.Offset(, 5) = "Changed"
End If
Next
For Each cel In AllPart
Set c = NetPart.Find(cel)
If Not c Is Nothing Then
cel.Offset(, 4) = c.Offset(, 2)
cel.Offset(, 4).Interior.ColorIndex = 3
Else
cel.Offset(, 4) = XPart.Find(cel).Offset(, 2)
cel.Offset(, 4).Interior.ColorIndex = 6
End If
Next
End Sub

lmp884
06-04-2009, 01:06 PM
OMGosh ... I knew this board was probably for more advanced users, but under Excel, I saw where others seemed as inexperienced as me. I have spent the last hour just trying to find what you wrote on the first line. I clicked VBA in excel and it brought up a different program (I think) ... called VBA project. Not one single tab is Option or Explicit, so I have VERY lost. Thank you for trying to help me. Please don't hold it against me that I am illiterate to advanced coding. It's foreign.

Can this be done with a "function"?

I had to delete most of the workbook as the file was too large for this board. I keep getting an error that I need 5 posts to enter a link in a thread. The entire file is here ... without spaces and no www
h t t p :// rpv4tx.com/inventories.xlsx

I don't know what to do!

mdmackillop
06-04-2009, 02:06 PM
Here is my test example

lmp884
06-04-2009, 02:42 PM
:yes This is close, so close. It took me a bit to figure out enable macros to see it and whahlah!!

I just wanted to acknowledge this and thank you for your time ...

I have to leave for about two hours and then I will post on the example.

One page works EXACTLY as I was hoping ... the other 3 are corrupt or something and the ALL Running has this board/forum inbedded in it and many data changes.

I will post the specifics when I get back.