Merging worksheets (Excel 2000 sr-1)

[Melanie]

I have a user who needs to combine or merge two worksheets. He received one worksheet from human resources and the other from the state - the one from HR is more current and he needs to take that worksheet and add it to the state worksheet. The problem is that the state lists certain employees, say 1-1000 and they are sorted by SS#s. The HR lists new employees also in order by SS#s. How can you insert the new employees - into the state worksheet and keep the order the same. The state worksheets have blank columns that I want to populate with the HR info. I know this is hard to understand or maybe I just cannot explain it correctly. I looked at sharing documents and accepting and rejecting revisions, but the documents are NOT two versions of the same file. They are two separate workbooks.

[Bat17]

Can't you just add the new records to the end and then sort them to the right order?

Peter

[Melanie]

OK - I knew I didn't explain this correctly. Two separate worksheets, let's call them master sheet and update sheet. The master sheet has everyone's name, rank, SS#, etc up to 10 columns, some of the columns are blank and will be populated by the update sheet. Once a week the state sends a new updated sheet with new employees and all their info. This info needs to be put into the master sheet but the columns do not match up. The existing employees are already on both the master and update sheet so if you paste each column, you get two of the same person. I will think about how to explain it better. thanks

[LegareColeman]

It sounds this will require a macro to accomplish. However, it will be almost impossible to write a macro without seeing what the worksheets look like. Could you create a dummy workbook that has two worksheets that look like what you have and that have some dummy data in them and attach that workbook to a reply to this message?

[Melanie]

ok - I took out the personal information. thank you - I appreciate anything you can do. I guess I have to attach each workbook separately?

[Melanie]

here is the update workbook

[LegareColeman]

OK, I got the workbooks and I see the general idea of what you want to do. However, I still have a few questions:

1- Column A in both workbooks appears to be an ID number of some kind. However, in the Master workbook the values in this column are text and in the Update book they are numbers. Is this the way it will be in the real workbooks?

2- In these workbooks, it looks like column B in the Master and Column C in the Update book contain a name. However, in these workbooks, the names for the same ID number do not match. What do you want to happen when the master is updated from the Update book?

3- In the Update book that you sent, row 10 contains an ID number, but the other cells on this row are empty. What should the update do with this ID?

4- Column E in the Update book and Column C in the Master look like they might be the same information. What should the update do when the information in the update book is different from that in the Master?

5- In the Update book, columns B, D, F, G, H, and I do not appear to be in the Master. Should the update just add them as columns F, G, H, I, J, and K?

6- The update book you sent did not have any IDs that were not in the master. Is this how it will always be? If not, what should happen if there is an ID that is not in the Master? Just add it to the end of the Master?

[Melanie]

Legare, thank you so much for your time. Over the weekend, the user imported into Access and linked the tables on the SS# and then exported back to excel. I never thought to have him try Access. Learn something new everyday.

Thanks

[LegareColeman]

Ok, glad you got it solved. In case you need to do this often and would rather use an all Excel solution, I am posting the macro I wrote last Friday. It does not take into account the questions I asked in my previous quote.

<pre>Public Sub UpdateMaster()
Dim strWk As String
Dim oUpdateBook As Workbook, oUpdate As Worksheet, oMaster As Worksheet
Dim I As Long, J As Long, K As Long
Dim vFileName As Variant
Application.ScreenUpdating = False
vFileName = Application.GetOpenFilename(Title:="Select Update File")
If vFileName = False Then Exit Sub
Workbooks.Open Filename:=vFileName
Set oUpdateBook = ActiveWorkbook
Set oUpdate = oUpdateBook.Worksheets("Sheet1")
Set oMaster = ThisWorkbook.Worksheets("Sheet1")
I = 0
While oUpdate.Range("A1").Offset(I, 0).Value <> ""
J = 0
While oMaster.Range("A1").Offset(J, 0).Value <> "" And _
oMaster.Range("A1").Offset(J, 0).Value <> oUpdate.Range("A1").Offset(I, 0).Value
J = J + 1
Wend
oMaster.Range("A1").Offset(J, 0).Value = oUpdate.Range("A1").Offset(I, 0).Value
oMaster.Range("A1").Offset(J, 1).Value = oUpdate.Range("A1").Offset(I, 2).Value
oMaster.Range("A1").Offset(J, 2).Value = oUpdate.Range("A1").Offset(I, 4).Value
oMaster.Range("A1").Offset(J, 5).Value = oUpdate.Range("A1").Offset(I, 1).Value
oMaster.Range("A1").Offset(J, 6).Value = oUpdate.Range("A1").Offset(I, 3).Value
oMaster.Range("A1").Offset(J, 7).Value = oUpdate.Range("A1").Offset(I, 5).Value
oMaster.Range("A1").Offset(J, 8).Value = oUpdate.Range("A1").Offset(I, 6).Value
oMaster.Range("A1").Offset(J, 9).Value = oUpdate.Range("A1").Offset(I, 7).Value
oMaster.Range("A1").Offset(J, 10).Value = oUpdate.Range("A1").Offset(I, 8).Value
oMaster.Range("A1").Offset(J, 11).Value = oUpdate.Range("A1").Offset(I, 9).Value
I = I + 1
Wend
oUpdateBook.Close
Set oUpdateBook = Nothing
Set oUpdate = Nothing
Set oMaster = Nothing
Application.ScreenUpdating = True
End Sub
</pre>

[Melanie]

Thank you SO much. You are wonderful! I cannot believe how much people like you and others go out of their way to help others.

Have a great week!!