Macro (I Think) To Update List in Workbook With Weekly Updates?

[Wart]

Everyone here was tremendously helpful on a similar question a few weeks ago.
I'm really hoping I can impose on you one more time:

At the top of each year, we receive a "Master List" of projects with columns
A-L of data: Column A is the unique part number, while columns B-L provide
details. Every week, Corporate sends out updates, which may include either A)
new info for existing part numbers (meaning SOMETHING, who knows what, has
changed in columns B-L, though a part number already exists somewhere in the
Master List in Column A), or B) a totally NEW part number is being added to
the Master List, so that both the number in Column A AND all of the info in
Columns B-L needs to be added to the Master List.

I guess what I'm looking for is some sort of macro that I can run. I'm
imagining something like this: Corporate sends the weekly update workbook, I
drag the sheet from it into my existing Master List workbook, and then run
the macro. Based on data in Column A, the macro looks for existing part
numbers, and then, finding them, updates B-L accordingly, or, not finding a
number, just adds it on to the bottom of the Master List.

Frankly, I'm pretty sure all of this could be done more easily in a shared
Access (or even FileMaker) database--but well, I'm just a thrall in the mines
and I don't have those tools available to me.

I know this is asking a lot--but can anyone help? Probably some sort of XL
Guru could charge a lot for the sort of guidance I'm looking for, but
meanwhile, I'm kind of stuck, without a clue, and spending a whole lot of
time ever weekupdating this "Office Space"-like Master List whose ultimate
point kind of eludes me.

 

[Dave]

Hi,
Yes, quite do-able. Is the weekly updates sheet in exactly the same format
as the master list? ie Column A has the unique part number and Columns B-L
have the mumbo jumbo?
Regards - Dave.
PS: Do you have a red stapler, or has the boss taken that already?

 

[Wart]

Hi, Dave:

Thnaks for the chucuke! I kind of needed it.

Anyway, yes, the configuration of Columns A-L is always the same (you know,
the unique part number is in Column A, with the details in B-L), but in the
Master List that I'm updating from the Corporate weekly updates, I've taken
to "keeping notes" in Columns M-P. Ideally, in a CYA sort of way, I'd like
these columns to remain unchanged for the part number even though A-L might
(will) update.

It's encouraging that you think this is "do-able." This whole thing is just
so absurd that I can't believe there's ANY solution. Thanks in advance for
any advice!

 

[Dave]

Hi,
We should be able to leave other columns untouched.
One last question before I begin:
Does your Master list or your Update lists have any blank rows?
Dave.

 

[Wart]

I'm really hopng this isn't going to be something you waste perfectly good
"Family Guy" time doing, because, well, it's supposed to be what I'M wasting
perfectly good "Family Guy" time doing.

But, anyway: the only blank rows should be at the end of the both the
"Master List" and the horrible weekly update. I'm sorry I didn't mention that
beofre--clearly, I'm actually an XL novice.

 

[Dave]

Hi,
I don't consider this a waste of time, because I'm always learning.
But I do have to go to bed now, and will carry on tomorrow.
Having no blank rows makes life easier.
Greetings from a Kiwi in Brazil
Regards - Dave.

 

[Wart]

Then I think I'll sign off as well.

Gee--I'm kind of guessing your kiwi in Brazil is a whole lot nicer than my
cubicle in Ohio. But the snack machine is nice.

I'll check in again in the AM. Again--thanks so much for your help!

 

[Dave]

Hi,
Yeah, being a Kiwi in Brazil is pretty good - but I've never been to Ohio.

Ok, try this code. Copy and paste it into a module in the VBA window.

Sub Update()
Dim A As Integer, ML As Integer, UL As Integer
ML = 2 'MasterList Start Row
UL = 2 'UpdateList Start Row
Do Until Sheets("Sheet2").Cells(UL, 1) = ""
Do Until Sheets("Sheet1").Cells(ML, 1) = ""
If Sheets("Sheet1").Cells(ML, 1) = Sheets("Sheet2").Cells(UL, 1) Then
Worksheets("Sheet2").Activate
Sheets("Sheet2").Range(Cells(UL, 1), Cells(UL, 12)).Copy _
Sheets("Sheet1").Cells(ML, 1)
ML = 2
Exit Do
End If
ML = ML + 1
If Sheets("Sheet1").Cells(ML, 1) = "" Then
Worksheets("Sheet2").Activate
Sheets("Sheet2").Range(Cells(UL, 1), Cells(UL, 12)).Copy _
Sheets("Sheet1").Cells(ML, 1)
End If
Loop
UL = UL + 1
ML = 2
Loop
End Sub

For this to work, you will have to copy and paste the update data into a new
sheet in the same workbook as the Master List. I have assumed that this new
sheet tab name will be Sheet2. If not, you will have to change the 6
instances of Sheet2 in the code to the new tab name in your workbook.

I have assumed that your Master List tab name is Sheet1. If this is not so,
you will have to change the 5 instances of Sheet1 in the code to the name of
your Master List sheet tab.

One more assumption: That you have headers in Row 1, and that the data
actually starts in Row 2, for both the Master List and the Update List. If
this is not so, you will need to change the numbers in the following code:
ML = 2
UL = 2

However, I very strongly recommend that you do all this on a copy of your
Master List workbook first, until you are content that it works (and doesn't
just transfer all the odd fractions of cents into your bank account.) On your
copy, rename the Master List sheet tab to Sheet1, and copy the update data
into Sheet2 so you don't have to change any code right away.

Hope this works for you. If there's lots of data, it may take a few seconds
to run. Let me know.
Regards - Dave.

 

[Wart]

I just got out an interminable meeting this morning and saw your email. I
tried the code immediately, and it works WONDERFULLY! Oh, my God--this is
EXACTLY what I needed! Thank you so much, Dave! You've just saved me (and
others) probably hundreds of hours of totally meaningless report-trowling
every year. I'll nominate you in absentia for the Initech Star of the Month
award.

Again--you truly have made the lives of some cubemoles in Ohio a whole lot
better. Thank you!

 

[Dave]

Hi,
Cubemoles? I thought they only existed in the movies!
Glad to help in such a productive way. If only XL did the dishes!
Regards - Dave.