Merge 2 Excel Worksheets together

[Eyrinn]

Hi There,
I need help merging 2 excel worksheets together in one workbook. I have been self teaching Programming with VBA for this project but I just don't have enough knowledge and experience to create/fix this code. We had a working code, but I did not create it and cannot fix it either, this is why I have been self teaching to try and understand what the codes mean to see if I can fix it. The reason the code does not work is because they changed the layout of the BOM

I have to take a Build of Materials (BOM) Report as the main page, and merge certain columns from a daily Materials report, and I need to make it automatic. so anyone can open this template that was created and update the materials report tab and it will automatically update the BOM report tab.

the trick to this is the BOM Structure can not change. due to certain levels each piece of material is at. some are at a level 1 some are at level 2 and some are lower level 3,4, 5. So they have to stay in that order. So taking the column of Material part number and sorting the BOM is not going to work.

Step1:
Paste the daily materials report into the second tab of the workbook and then remove 75% of the columns as only certain ones are required. I always mark the column title in Yellow so I know which ones not to remove

Step2:
I would need to find a way to take the material numbers from the Daily Materials report and match them to the material numbers in the BOM. 2 things should happen:
1. If the material matches - I need to take the remaining data in each material row from the daily material report and move it to the end of the row that matches the materials number in the BOM Report
2. The Material number does not match - The material number will be added to the end of the material Column in the BOM (Say column B and row 8 is the last row, the materials number would be entered into Cell B9) then the remaining data from the daily materials report will be added to Row 9 but in Columns j.k.l.m.n.


Please help if you can I would really appreciate it.
Erin :)

[SamT]

Moderator Bump

[offthelip]

try this

I have done it using variant arrays which is much faster than working directly in the worksheet.


Sub OTLBOMMerge()

Dim LastRow As Long
Dim LastRowC As Long
Dim Parts As Long
Dim ComparePart As String
Dim FindPart As String
Dim CopyRange As String
'set up array to copy specfic columns
Dim indi(1 To 10) As Integer
indi(1) = 17
indi(2) = 18
indi(3) = 22
indi(4) = 24
indi(5) = 26
indi(6) = 27
indi(7) = 28
indi(8) = 32
indi(9) = 34
indi(10) = 35
LastRowC = Sheets("Material Sheet").Range("C" & Rows.Count).End(xlUp).Row
'Finds Last Row of Data on Materials Sheet.
With Worksheets("Material Sheet")
Materialarray = Range(.Cells(1, 1), .Cells(LastRowC, 40))
End With
lastrowbom = Sheets("BOM").Range("C" & Rows.Count).End(xlUp).Row
'Finds Last Row of Data on BOM Sheet.
With Worksheets("BOM")
BOMarray = Range(.Cells(1, 1), .Cells(lastrowbom, 54))
Range(.Cells(6, 54), .Cells(3 + 2 * lastrowbom, 63)) = ""

matched = Range(.Cells(1, 54), .Cells(lastrowbom, 63))
Blankarr = Range(.Cells(2 + lastrowbom, 54), .Cells(3 + 2 * lastrowbom, 63))
blanki = 2
Blankarr(1, 1) = "Not found"
Materials = 1
For Parts = 2 To LastRowC Step 1
'Starts the Loop that will move through the parts on the materials sheet.


found = False
For i = 6 To lastrowbom
If Materialarray(Parts, 17) = BOMarray(i, 2) Then
found = True
'Looks for ComparePart on the BOM Sheet
For k = 1 To 10
matched(i, k) = Materialarray(Parts, indi(k))
Next k
End If
Next i


If Not (found) Then
' copy to the blankarray
For k = 1 To 10
Blankarr(blanki, k) = Materialarray(Parts, indi(k))
Next k
blanki = blanki + 1
End If
Next Parts
' write the array aback to the spreadsheet
Range(.Cells(1, 54), .Cells(lastrowbom, 63)) = matched
Range(.Cells(lastrowbom + 2, 54), .Cells(3 + 2 * lastrowbom, 63)) = Blankarr

End With


End Sub

[Eyrinn]

Hi Offthelip,
First I want to say a huge thank you for helping me.. I appreciate it very much :) someday I will get to you level of VBA knowledge :) I also want to apologize for the time frame it took me to get to the message and test it out. Being an admin for a whole department of engineers, it is very, very busy work.
I tested the code out and it works almost perfectly.
the only thing I need to change is the material number from Sheet2 (Material Sheet) should stay in the material sheet if it matches the the material number in Sheet1 (BOM). If the material number does not match then it should be put at the end of the column C(CT Part#) in Sheet1 (BOM). The remaining info from Sheet 2 should be moved tot he same row as the material number, but in column BB to BJ.
If that makes any sense to you.
Let me know your thoughts.. it is okay if you don't want to fix it as I understand (I hate asking others for help, I feel like I ask too much :))

I will check back again. Also again I just want to say thank you so much for the help you have given me :)

Erin M :)

[offthelip]

I am not sure whether this is requirements creep or just poorly defined requirements in the first place, however I am still not at all clear what your additional requirements are:


the only thing I need to change is the material number from Sheet2 (Material Sheet) should stay in the material sheet if it matches the the material number in Sheet1 (BOM)

I don't touch anything on the Material sheet so everything stays there!! Are you asking to change something on the Material sheet as well?


If the material number does not match then it should be put at the end of the column C(CT Part#) in Sheet1 (BOM). The remaining info from Sheet 2 should be moved tot he same row as the material number, but in column BB to BJ.
If that makes any sense to you.

Currently the Material Number for unmatched items is put into column BB, with all the remaining information in columns BC to BK. This means the items tie up with the heading for the columns in row 5. If I do exactly what you specify you will end up with "qty/Parent" information for the ummatched items being in column BB, and "issued qty" in BC , etc, which doesn't make sense to me. I think what you really want is for the information in column BB for, line 656 onwards to be copied to column C as well. This is a trivial change, which I hope you could do yourself, however this should also do it ok:
Change the last few lines to this:


' write the array aback to the spreadsheet
Range(.Cells(1, 54), .Cells(lastrowbom, 63)) = matched
Range(.Cells(lastrowbom + 2, 3), .Cells(3 + 2 * lastrowbom, 12)) = Blankarr
Range(.Cells(lastrowbom + 2, 4), .Cells(3 + 2 * lastrowbom, 54)) = ""
Range(.Cells(lastrowbom + 2, 54), .Cells(3 + 2 * lastrowbom, 63)) = Blankarr


all I am doing here iswriting the array out to column C, then deleting the bits between D and BA then writing the arrray out again to B