Macro to Compare to Sheets and Copy Notes

T

THRASHER69

Board Regular
Joined
Mar 29, 2012
Messages
200
Hello,

I'm needing some help with a macro. I have 2 sheets. The first one named "Back Orders" is the new report and the second one named "BO Save" is the saved sheet with notes. I would like to have a macro that compares the lines of each sheet and copies the notes from the BO Save sheet and pastes them under the matching line of the Back Orders sheet. If the line is gone it would just skip to the next one. I will post my sheets below to hopefully make things a bit clearer. Both sheets start at row 7 with a header. I put the notes I'm talking about in red. Thank you.

Back Orders Sheet:
[TABLE="width: 1366"]
<tbody>[TR]
[TD]Conf. Date[/TD]
[TD]Entr. Dt[/TD]
[TD]Item number[/TD]
[TD]Item Name[/TD]
[TD]CO Number[/TD]
[TD]Customer #[/TD]
[TD]Rem Qty[/TD]
[TD]Customer Name[/TD]
[TD]Remaining[/TD]
[TD]St[/TD]
[/TR]
[TR]
[TD] 1/6/14[/TD]
[TD] 11/14/13[/TD]
[TD]23B88G001[/TD]
[TD]2.56"5K MN ITBOP-DBL2.00" RAMS[/TD]
[TD]0001443414[/TD]
[TD]103623[/TD]
[TD]1[/TD]
[TD]RED MAN PIPE & SUPPLY -JOURDANTON#71[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"][/TD]
[/TR]
[TR]
[TD] 2/10/14[/TD]
[TD] 1/3/14[/TD]
[TD]MTR-ROD CLAMP[/TD]
[TD]MTR SET F/HERCULES ROD CLAMP[/TD]
[TD]0001447338[/TD]
[TD]112575[/TD]
[TD]1[/TD]
[TD]SONAL ENTERPRISES[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 2/11/14[/TD]
[TD] 1/29/14[/TD]
[TD]20366762[/TD]
[TD]1.5"SET RAINBOW CONE PKG[/TD]
[TD]0001451177[/TD]
[TD]131654[/TD]
[TD]20[/TD]
[TD]WEATHERFORD ALS INTERNATIONAL[/TD]
[TD]Money[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD="colspan: 10"][/TD]
[/TR]
[TR]
[TD] 2/18/14[/TD]
[TD] 12/17/13[/TD]
[TD]23B88D001[/TD]
[TD]2.56"5K MN ITBOP-1.5"&BLKHSN R[/TD]
[TD]0001447011[/TD]
[TD]76700[/TD]
[TD]2[/TD]
[TD]MCJUNKIN RED MAN CORP CHARLESTON WV[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"][/TD]
[/TR]
[TR]
[TD] 2/19/14[/TD]
[TD] 1/22/14[/TD]
[TD]23B88D001[/TD]
[TD]2.56"5K MN ITBOP-1.5"&BLKHSN R[/TD]
[TD]0001450320[/TD]
[TD]76700[/TD]
[TD]13[/TD]
[TD]MCJUNKIN RED MAN CORP CHARLESTON WV[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"][/TD]
[/TR]
[TR]
[TD] 2/24/14[/TD]
[TD] 12/2/13[/TD]
[TD]23B88G001[/TD]
[TD]2.56"5K MN ITBOP-DBL2.00" RAMS[/TD]
[TD]0001445249[/TD]
[TD]71805[/TD]
[TD]10[/TD]
[TD]LUFKIN HYDRAULIC LIFT[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"][/TD]
[/TR]
[TR]
[TD] 2/28/14[/TD]
[TD] 2/10/14[/TD]
[TD]37100105[/TD]
[TD]1"600# BRONZE BV-2PC[/TD]
[TD]0001452582[/TD]
[TD]134682[/TD]
[TD]14[/TD]
[TD]DNOW LP - TUSCALOOSA, AL[/TD]
[TD]Money[/TD]
[TD]27[/TD]
[/TR]
</tbody>[/TABLE]

BO Save sheet:
[TABLE="width: 1129"]
<tbody>[TR]
[TD]Conf. Date[/TD]
[TD]Entr. Dt[/TD]
[TD]Item number[/TD]
[TD]Item Name[/TD]
[TD]CO Number[/TD]
[TD]Customer #[/TD]
[TD]Rem Qty[/TD]
[TD]Customer Name[/TD]
[TD]Remaining[/TD]
[TD]St[/TD]
[/TR]
[TR]
[TD] 1/6/14[/TD]
[TD] 11/14/13[/TD]
[TD]23B88G001[/TD]
[TD]2.56"5K MN ITBOP-DBL2.00" RAMS[/TD]
[TD]0001443414[/TD]
[TD]103623[/TD]
[TD]1[/TD]
[TD]RED MAN PIPE & SUPPLY -JOURDANTON#71[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"]NP BOP 23B88G001-NP Due 3-5 Domino[/TD]
[/TR]
[TR]
[TD] 1/29/14[/TD]
[TD] 12/10/13[/TD]
[TD]WELLHEAD - HHS[/TD]
[TD]HHS SUBMERSIBLE WELLHEAD[/TD]
[TD]0001446158[/TD]
[TD]35725[/TD]
[TD]3[/TD]
[TD]DYNAFLO ARTIFICIAL LIFT SYSTEMS[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Today[/TD]
[/TR]
[TR]
[TD] 1/29/14[/TD]
[TD] 12/10/13[/TD]
[TD]WELLHEAD - HHS[/TD]
[TD]HHS SUBMERSIBLE WELLHEAD[/TD]
[TD]0001446159[/TD]
[TD]43840[/TD]
[TD]10[/TD]
[TD]FIVE STAR EQUIPMENT[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Today[/TD]
[/TR]
[TR]
[TD] 1/31/14[/TD]
[TD] 1/15/14[/TD]
[TD]13111170[/TD]
[TD]2.5"8RD*2.5"8RD*2"11.5V*1" CT[/TD]
[TD]0001449543[/TD]
[TD]RMENERGYAU[/TD]
[TD]6[/TD]
[TD]R&M ENERGY SYSTEMS AUSTRALIA PTY LD[/TD]
[TD]Money[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD] 1/31/14[/TD]
[TD] 1/15/14[/TD]
[TD]1292A067[/TD]
[TD]2.87"8RD X6"LG J55 TBG NIPPLE[/TD]
[TD]0001449543[/TD]
[TD]RMENERGYAU[/TD]
[TD]6[/TD]
[TD]R&M ENERGY SYSTEMS AUSTRALIA PTY LD[/TD]
[TD]Money[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD] 1/31/14[/TD]
[TD] 1/15/14[/TD]
[TD]29853129[/TD]
[TD]2.5"8R PCSB-1.25"G &ORG LUBCAP[/TD]
[TD]0001449543[/TD]
[TD]RMENERGYAU[/TD]
[TD]6[/TD]
[TD]R&M ENERGY SYSTEMS AUSTRALIA PTY LD[/TD]
[TD]Money[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD] 1/31/14[/TD]
[TD] 1/15/14[/TD]
[TD]12048233[/TD]
[TD]2.5"8RD BOP 1.25"HSN-LUGLESS C[/TD]
[TD]0001449543[/TD]
[TD]RMENERGYAU[/TD]
[TD]6[/TD]
[TD]R&M ENERGY SYSTEMS AUSTRALIA PTY LD[/TD]
[TD]Money[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Shipping Instructions[/TD]
[/TR]
[TR]
[TD] 2/10/14[/TD]
[TD] 12/19/13[/TD]
[TD]21206006C[/TD]
[TD]1.5"ROD BOSS CLAMP-CADMIUM TR[/TD]
[TD]0001447338[/TD]
[TD]112575[/TD]
[TD]20[/TD]
[TD]SONAL ENTERPRISES[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD] 2/10/14[/TD]
[TD] 1/3/14[/TD]
[TD]MTR-ROD CLAMP[/TD]
[TD]MTR SET F/HERCULES ROD CLAMP[/TD]
[TD]0001447338[/TD]
[TD]112575[/TD]
[TD]1[/TD]
[TD]SONAL ENTERPRISES[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Shipping Instructions[/TD]
[/TR]
[TR]
[TD] 2/11/14[/TD]
[TD] 1/29/14[/TD]
[TD]20366762[/TD]
[TD]1.5"SET RAINBOW CONE PKG[/TD]
[TD]0001451177[/TD]
[TD]131654[/TD]
[TD]20[/TD]
[TD]WEATHERFORD ALS INTERNATIONAL[/TD]
[TD]Money[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD="colspan: 10"].-2 Sets Canada Shipped 2-27[/TD]
[/TR]
[TR]
[TD] 2/12/14[/TD]
[TD] 1/15/14[/TD]
[TD]WELLHEAD - HHS[/TD]
[TD]HHS SUBMERSIBLE WELLHEAD[/TD]
[TD]0001449531[/TD]
[TD]21605[/TD]
[TD]1[/TD]
[TD]CENTRILIFT INC TULSA OK[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Today[/TD]
[/TR]
[TR]
[TD] 2/18/14[/TD]
[TD] 12/17/13[/TD]
[TD]23B88D001[/TD]
[TD]2.56"5K MN ITBOP-1.5"&BLKHSN R[/TD]
[TD]0001447011[/TD]
[TD]76700[/TD]
[TD]2[/TD]
[TD]MCJUNKIN RED MAN CORP CHARLESTON WV[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"]NP BOP 23B88G001-NP Due 3-3 Domino[/TD]
[/TR]
[TR]
[TD] 2/19/14[/TD]
[TD] 1/22/14[/TD]
[TD]23B88D001[/TD]
[TD]2.56"5K MN ITBOP-1.5"&BLKHSN R[/TD]
[TD]0001450320[/TD]
[TD]76700[/TD]
[TD]13[/TD]
[TD]MCJUNKIN RED MAN CORP CHARLESTON WV[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"]NP BOP 23B88G001-NP Due 3-3 Domino[/TD]
[/TR]
[TR]
[TD] 2/19/14[/TD]
[TD] 2/5/14[/TD]
[TD]10310123[/TD]
[TD]2.5"8RD M X 1.5"TYPE S[/TD]
[TD]0001451991[/TD]
[TD]76700[/TD]
[TD]3[/TD]
[TD]MCJUNKIN RED MAN CORP CHARLESTON WV[/TD]
[TD]Money[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Today[/TD]
[/TR]
[TR]
[TD] 2/20/14[/TD]
[TD] 2/4/14[/TD]
[TD]10906161[/TD]
[TD]2"SLIP-4.5-7"HHS[/TD]
[TD]0001451836[/TD]
[TD]62021[/TD]
[TD]10[/TD]
[TD]J & W SERVICES & EQUIPMENT[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Today[/TD]
[/TR]
[TR]
[TD] 2/20/14[/TD]
[TD] 2/7/14[/TD]
[TD]21040006[/TD]
[TD]1.12"-1.5"T252 ROD ROTATOR[/TD]
[TD]0001452399[/TD]
[TD]85900[/TD]
[TD]20[/TD]
[TD]DNOW LP - L565[/TD]
[TD]Money[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Today[/TD]
[/TR]
[TR]
[TD] 2/21/14[/TD]
[TD] 1/10/14[/TD]
[TD]40031120E[/TD]
[TD]20"SJ EPXY W/NI-RES & VITON[/TD]
[TD]0001448885[/TD]
[TD]T00104143[/TD]
[TD]1[/TD]
[TD]CAPE ENVIRONMENTAL MANAGEMENT INC.[/TD]
[TD]Money[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Still In Testing[/TD]
[/TR]
[TR]
[TD] 2/21/14[/TD]
[TD] 2/11/14[/TD]
[TD]21040006[/TD]
[TD]1.12"-1.5"T252 ROD ROTATOR[/TD]
[TD]0001452780[/TD]
[TD]134605[/TD]
[TD]7[/TD]
[TD]DNOW LP - BAKERSFIELD, CA[/TD]
[TD]Money[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Today[/TD]
[/TR]
[TR]
[TD] 2/24/14[/TD]
[TD] 12/2/13[/TD]
[TD]23B88G001[/TD]
[TD]2.56"5K MN ITBOP-DBL2.00" RAMS[/TD]
[TD]0001445249[/TD]
[TD]71805[/TD]
[TD]10[/TD]
[TD]LUFKIN HYDRAULIC LIFT[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"]NP BOP 23B88G001-NP Due 3-14 Domino[/TD]
[/TR]
[TR]
[TD] 2/24/14[/TD]
[TD] 2/3/14[/TD]
[TD]40031116E[/TD]
[TD]16"SJ EPXY W/NI-RES & VITON[/TD]
[TD]0001451825[/TD]
[TD]127501[/TD]
[TD]1[/TD]
[TD]ULTRAFLOTE CORP[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Still In Testing[/TD]
[/TR]
[TR]
[TD] 2/24/14[/TD]
[TD] 2/4/14[/TD]
[TD]21040006[/TD]
[TD]1.12"-1.5"T252 ROD ROTATOR[/TD]
[TD]0001451918[/TD]
[TD]76720[/TD]
[TD]16[/TD]
[TD]MCJUNKIN RED MAN CORP #112[/TD]
[TD]Money[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Today[/TD]
[/TR]
[TR]
[TD] 2/25/14[/TD]
[TD] 2/5/14[/TD]
[TD]10906161[/TD]
[TD]2"SLIP-4.5-7"HHS[/TD]
[TD]0001452096[/TD]
[TD]43100[/TD]
[TD]1[/TD]
[TD]FECHNER PUMP[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Today[/TD]
[/TR]
[TR]
[TD] 2/27/14[/TD]
[TD] 1/31/14[/TD]
[TD]10906151[/TD]
[TD]3"SLIP-4.5-7"HHS[/TD]
[TD]0001451617[/TD]
[TD]62021[/TD]
[TD]5[/TD]
[TD]J & W SERVICES & EQUIPMENT[/TD]
[TD]Money[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Today [/TD]
[/TR]
[TR]
[TD] 2/27/14[/TD]
[TD] 2/20/14[/TD]
[TD]37200107[/TD]
[TD]1-1/2"1500# 200 BV-2PC[/TD]
[TD]0001453798[/TD]
[TD]103616[/TD]
[TD]2[/TD]
[TD]RED MAN PIPE & SUPPLY -LOVINGTONM#32[/TD]
[TD]Money[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Ship From Consign Due 3-14 EMC -> ABC[/TD]
[/TR]
[TR]
[TD] 2/27/14[/TD]
[TD] 2/27/14[/TD]
[TD]11203161[/TD]
[TD]2.5"8RD SBT W/1.5"SOFT & LUB[/TD]
[TD]0001454542[/TD]
[TD]23801[/TD]
[TD]2[/TD]
[TD]CLIFTON EQUIPMENT & SUPPLY[/TD]
[TD]Money[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Today? Pick Up?[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
this seemed to work. It assumes you already have an empty row between each entry on sheet Back Orders. If not then this will overwrite the data that is there now. To avoid that, take the apostrophe off of the line of code I have commented out and it will insert a blank row for the data to be pasted into.
Code: 
Sub getnotes()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range, fLoc As Range
Dim fAdr As String
Set sh1 = Sheets("Back Orders")
Set sh2 = Sheets("BO Save")
lr = sh1.Cells(Rows.Count, 5).End(xlUp).Row
Set rng = sh1.Range("E7:E" & lr)
    For Each c In rng
        Set fLoc = sh2.Range("E:E").Find(c.Value, , xlValues)
            If Not fLoc Is Nothing Then
                fAdr = fLoc.Address
                Do
                    If Trim(c.Offset(0, -4).Value) = Trim(fLoc.Offset(0, -4).Value) Then
                        If fLoc.Offset(1, 0) = "" And fLoc.Offset(1, -4) <> "" Then
                            'c.Offset(1, 0).EntireRow.Insert
                            fLoc.Offset(1, -4).Copy c.Offset(1, -4)
                        End If
                        Exit Do
                    End If
                    fLoc = sh2.Range("E:E").FindNext(fLoc)
                Loop While fAdr <> fLoc.Address
            End If
    Next
End Sub
 
Upvote 0
I will try this out first thing in the morning when I get back to work and let you know. Thank you very much JLGWhiz!

THRASHER
 
Upvote 0
That is a beautiful thing :). It works like a charm! I need to ask one more favor I forgot to mention in my original post if you don't mind. Could I get you make it where when the note is copied over it will merge cells A:J and center the note? I really appreciate the help. The macro is great and saves me a ton of time each morning.
 
Upvote 0
THRASHER69 said:
That is a beautiful thing :). It works like a charm! I need to ask one more favor I forgot to mention in my original post if you don't mind. Could I get you make it where when the note is copied over it will merge cells A:J and center the note? I really appreciate the help. The macro is great and saves me a ton of time each morning.
Click to expand...

I got it JLGWhiz. I just had to add the following line to the code:

Code: 
c.Offset(1, -4).Columns("A:J").Merge

Thanks for your help. I really do appreciate it.

THRASHER
 
Upvote 0
JLGWhiz,

on this code that you hooked me up with I have a question. I'm going to post part of the spreadsheet after I run the code so maybe you can follow my question. Everything works great in the code except one thing. When I have different numbers in column E but column A has the same date it will only copy the notes over for the bottom date. Row 1 and 2 have the same number in column E and should insert a space and copy the note but does not. It only copies the not I have under the 3rd row. Any ideas on how to fix this?

[TABLE="width: 1199"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] 6/9/14[/TD]
[TD] 5/22/14[/TD]
[TD]23A01610[/TD]
[TD]2.5"8RD 300D BOP W/1.5"HSN RAM[/TD]
[TD]0001463652[/TD]
[TD]110632[/TD]
[TD]5[/TD]
[TD]ROD LIFT CONSULTING LLC[/TD]
[/TR]
[TR]
[TD] 6/9/14[/TD]
[TD] 5/22/14[/TD]
[TD]34100100[/TD]
[TD]2"B29 CHOKE W/CARBIDE TRIM[/TD]
[TD]0001463652[/TD]
[TD]110632[/TD]
[TD]11[/TD]
[TD]ROD LIFT CONSULTING LLC[/TD]
[/TR]
[TR]
[TD] 6/9/14[/TD]
[TD] 5/23/14[/TD]
[TD]29813161[/TD]
[TD]2.5"8RD PCSB W/1.5"SOFT[/TD]
[TD]0001463758[/TD]
[TD]78817[/TD]
[TD]3[/TD]
[TD]MIDWAY -MC JUNKIN APPALACHIAN[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Please post the code you are currently using.
 
Upvote 0
JLGWhiz said:
Please post the code you are currently using.
Click to expand...

Code: 
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range, fLoc As RangeDim fAdr As String
Set sh1 = Sheets("Back Orders")
Set sh2 = Sheets("BO Save")
lr = sh1.Cells(Rows.Count, 5).End(xlUp).Row
Set rng = sh1.Range("E7:E" & lr)
    For Each c In rng
        Set fLoc = sh2.Range("E:E").Find(c.Value, , xlValues)
            If Not fLoc Is Nothing Then
                fAdr = fLoc.Address
                Do
                    If Trim(c.Offset(0, -4).Value) = Trim(fLoc.Offset(0, -4).Value) Then
                        If fLoc.Offset(1, 0) = "" And fLoc.Offset(1, -4) <> "" Then
                            c.Offset(1, 0).EntireRow.Insert
                            fLoc.Offset(1, -4).Copy c.Offset(1, -4)
                            c.Offset(1, -4).Columns("A:J").Merge
                        End If
                        Exit Do
                    End If
                    fLoc = sh2.Range("E:E").FindNext(fLoc)
                Loop While fAdr <> fLoc.Address
            End If
    Next
 
Upvote 0
The criteria is:
1. If there is a Match for a value in column E of sheet 1 to a value in column E of sheet 2 AND
2. If the value in column A on the source row of sheet 1 matches the value of column A on the destination row of sheet 2 AND
3. The cell under the Column E value in the destination sheet IS blank and the cell under the Column A value ISNOT blank THEN
4. The value in Column A of the sheet 1 will be merged into A:J of sheet 1.
So if conditions in 1 - 3 above are not met, it will not merge the cells. I don't know which sheet you are showing in the illustration, I assume it is sheet 1, but can't tell for sure. Not that it really matters. The point is that the conditions control whether the merger will occur. Now, if the conditions are being met, and it is not merging, then it could be timing, since merging cells that contain data might be a little slow. You could insert this snippet right after your merge command to find out.
Code: 
s = Timer + 0.5
Do
 DoEvents
Loop While s > Timer
That will give you a half second delay which should be enough for the merge to complete. If it is the criteria in 1 - 3 that is causing the problem, don't bother with the delay snippet, it would just add time to your procedure.
You can also step through the procedure using the F8 key to see if it is skipping any of the lines and what the conditions are when it does so. Stepping through the code is essential in troubleshooting and debugging.
BTW, when you have a problem different than your original post, you should start a new thread. Many responders are looking at the number of replies on a posting to determine if they want to look at it. When they see a post that was started days ago with several replies, they might ignore it. The new post will get you responses from a larger variety of programmers, most of which are much better than me.

this might also help
Code: 
With sh1
c.Offset(1, -4).Columns("A:J").Merge
End With
 
Last edited:
Upvote 0
JLGWhiz said:
The criteria is:
1. If there is a Match for a value in column E of sheet 1 to a value in column E of sheet 2 AND
2. If the value in column A on the source row of sheet 1 matches the value of column A on the destination row of sheet 2 AND
3. The cell under the Column E value in the destination sheet IS blank and the cell under the Column A value ISNOT blank THEN
4. The value in Column A of the sheet 1 will be merged into A:J of sheet 1.
So if conditions in 1 - 3 above are not met, it will not merge the cells. I don't know which sheet you are showing in the illustration, I assume it is sheet 1, but can't tell for sure. Not that it really matters. The point is that the conditions control whether the merger will occur. Now, if the conditions are being met, and it is not merging, then it could be timing, since merging cells that contain data might be a little slow. You could insert this snippet right after your merge command to find out.
Code: 
s = Timer + 0.5
Do
 DoEvents
Loop While s > Timer
That will give you a half second delay which should be enough for the merge to complete. If it is the criteria in 1 - 3 that is causing the problem, don't bother with the delay snippet, it would just add time to your procedure.
You can also step through the procedure using the F8 key to see if it is skipping any of the lines and what the conditions are when it does so. Stepping through the code is essential in troubleshooting and debugging.
BTW, when you have a problem different than your original post, you should start a new thread. Many responders are looking at the number of replies on a posting to determine if they want to look at it. When they see a post that was started days ago with several replies, they might ignore it. The new post will get you responses from a larger variety of programmers, most of which are much better than me.

this might also help
Code: 
With sh1
c.Offset(1, -4).Columns("A:J").Merge
End With
Click to expand...

Thanks for the advice. Next time I will know to create a new thread.

And you were correct, this is from sh1

As for the cell merge, even if I take that line out I get the same result. It is skipping over some for some reason. Below is a little bigger piece of the spreadsheet showing where it skipped. As you cane see, after the 1st 3 rows it should have added a not and after the last grouping that match in column E. All the others as you can see work fine. That is what I can not seem to figure. Everything looks correct to me but it will skip every time I run it in the same places.

[TABLE="width: 1363"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] 5/30/14[/TD]
[TD] 3/27/14[/TD]
[TD]12049401[/TD]
[TD]5/8"-1"SET OF BUNA-N RAMS(2)[/TD]
[TD]0001457885[/TD]
[TD]T00103577[/TD]
[TD]36[/TD]
[TD]NATIONAL DRILLING SERVICES CO, LLC[/TD]
[TD]$1,846.80[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD] 5/30/14[/TD]
[TD] 3/27/14[/TD]
[TD]12049402[/TD]
[TD]1.12" SET OF BUNA-N RAMS (2)[/TD]
[TD]0001457885[/TD]
[TD]T00103577[/TD]
[TD]32[/TD]
[TD]NATIONAL DRILLING SERVICES CO, LLC[/TD]
[TD]$1,641.60[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD] 5/30/14[/TD]
[TD] 3/27/14[/TD]
[TD]12049403[/TD]
[TD]1.25" SET OF BUNA-N RAMS (2)[/TD]
[TD]0001457885[/TD]
[TD]T00103577[/TD]
[TD]12[/TD]
[TD]NATIONAL DRILLING SERVICES CO, LLC[/TD]
[TD]$615.60[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD] 6/2/14[/TD]
[TD] 5/22/14[/TD]
[TD]21203006[/TD]
[TD]1.5"FIGURE 3 HINGE CLAMP[/TD]
[TD]0001463603[/TD]
[TD]71803[/TD]
[TD]150[/TD]
[TD]LUFKIN MIDDLE EAST - FREE ZONE[/TD]
[TD]$18,990.00[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Credit Hold[/TD]
[/TR]
[TR]
[TD] 6/5/14[/TD]
[TD] 5/29/14[/TD]
[TD]37210305[/TD]
[TD]1"2000# 210 BV-1PC[/TD]
[TD]0001464300[/TD]
[TD]66602[/TD]
[TD]50[/TD]
[TD]KEN MILLER SUPPLY, INC[/TD]
[TD]$1,080.00[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Due 6-2 EMC -> ABC[/TD]
[/TR]
[TR]
[TD] 6/6/14[/TD]
[TD] 4/15/14[/TD]
[TD]11600001[/TD]
[TD]1"SET OF SOFT CONE PKG[/TD]
[TD]0001460039[/TD]
[TD]78600[/TD]
[TD]100[/TD]
[TD]MIDCONTINENT EQUIPMENT PTE[/TD]
[TD]$1,260.00[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Top 11600101 (-238) and btm 11600211 (-46) Due 6-13 Diversified[/TD]
[/TR]
[TR]
[TD] 6/6/14[/TD]
[TD] 4/29/14[/TD]
[TD]11600017[/TD]
[TD]1.12"SET-"HERCULES GOLD" CONE[/TD]
[TD]0001461392[/TD]
[TD]62100[/TD]
[TD]28[/TD]
[TD]JL BRYAN EQUIPMENT[/TD]
[TD]$358.40[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Top Due 6-6 Diverified (-82)[/TD]
[/TR]
[TR]
[TD] 6/6/14[/TD]
[TD] 5/5/14[/TD]
[TD]11104261[/TD]
[TD]3"8V DPSB W/1.5"SOFT & LUB[/TD]
[TD]0001461864[/TD]
[TD]86057[/TD]
[TD]30[/TD]
[TD]NATIONAL OILWELL EGYPT[/TD]
[TD]$6,876.00[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD] 6/6/14[/TD]
[TD] 5/5/14[/TD]
[TD]MTR-DPSB[/TD]
[TD]MTR SET F/HERCULES DPSB ST. BX[/TD]
[TD]0001461864[/TD]
[TD]86057[/TD]
[TD]1[/TD]
[TD]NATIONAL OILWELL EGYPT[/TD]
[TD]$50.00[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD] 6/6/14[/TD]
[TD] 5/5/14[/TD]
[TD]11600061[/TD]
[TD]1.5"SET OF SOFT CONE PKG[/TD]
[TD]0001461864[/TD]
[TD]86057[/TD]
[TD]10[/TD]
[TD]NATIONAL OILWELL EGYPT[/TD]
[TD]$117.00[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD] 6/6/14[/TD]
[TD] 5/5/14[/TD]
[TD]13252225[/TD]
[TD]3"8V MALE*3"8V*3"8V**1/2"** CT[/TD]
[TD]0001461864[/TD]
[TD]86057[/TD]
[TD]30[/TD]
[TD]NATIONAL OILWELL EGYPT[/TD]
[TD]$3,795.00[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD] 6/6/14[/TD]
[TD] 5/5/14[/TD]
[TD]MTR-TEE[/TD]
[TD]MTR F/HERCULES TEE[/TD]
[TD]0001461864[/TD]
[TD]86057[/TD]
[TD]1[/TD]
[TD]NATIONAL OILWELL EGYPT[/TD]
[TD]$50.00[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD] 6/6/14[/TD]
[TD] 5/5/14[/TD]
[TD]29814261[/TD]
[TD]3"8V PCSB W/1.5"SOFT[/TD]
[TD]0001461864[/TD]
[TD]86057[/TD]
[TD]1[/TD]
[TD]NATIONAL OILWELL EGYPT[/TD]
[TD]$1,524.05[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD] 6/6/14[/TD]
[TD] 5/5/14[/TD]
[TD]MTR-PCSB[/TD]
[TD]MTR SET F/HERCULES PCSB ST. BX[/TD]
[TD]0001461864[/TD]
[TD]86057[/TD]
[TD]1[/TD]
[TD]NATIONAL OILWELL EGYPT[/TD]
[TD]$50.00[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD] 6/6/14[/TD]
[TD] 5/5/14[/TD]
[TD]12041064[/TD]
[TD]3"8V BOP W/1.5"BUNA-N RAMS[/TD]
[TD]0001461864[/TD]
[TD]86057[/TD]
[TD]5[/TD]
[TD]NATIONAL OILWELL EGYPT[/TD]
[TD]$1,799.00[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD] 6/6/14[/TD]
[TD] 5/5/14[/TD]
[TD]MTR-BOP[/TD]
[TD]MTR SET F/HERCULES BOP[/TD]
[TD]0001461864[/TD]
[TD]86057[/TD]
[TD]1[/TD]
[TD]NATIONAL OILWELL EGYPT[/TD]
[TD]$50.00[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD] 6/6/14[/TD]
[TD] 5/5/14[/TD]
[TD]23012600[/TD]
[TD]3"8V 200P BOP W/1.5"BN RAMS[/TD]
[TD]0001461864[/TD]
[TD]86057[/TD]
[TD]5[/TD]
[TD]NATIONAL OILWELL EGYPT[/TD]
[TD]$1,984.50[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD] 6/6/14[/TD]
[TD] 5/5/14[/TD]
[TD]MTR-BOP[/TD]
[TD]MTR SET F/HERCULES BOP[/TD]
[TD]0001461864[/TD]
[TD]86057[/TD]
[TD]1[/TD]
[TD]NATIONAL OILWELL EGYPT[/TD]
[TD]$50.00[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD] 6/9/14[/TD]
[TD] 5/12/14[/TD]
[TD]37200103[/TD]
[TD]1/2"2000# 200 BV-2PC[/TD]
[TD]0001462433[/TD]
[TD]86038[/TD]
[TD]38[/TD]
[TD]DNOW LP - CANADIAN,TX[/TD]
[TD]$638.40[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="colspan: 10"]Due 6-2 EMC -> ABC[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
copy last column for each sheet and the ID for each adjacent cells
Replies
0
Views
142
tubrak
T
T
  • Question Question
copy the first two columns and the last column from sheet to next sheet based on current month
Replies
2
Views
332
tubrak
T
A
  • Question Question
copy amount before TOTAL row for specific column based on matching names in column with sheet name
Replies
0
Views
286
Alaa mg
A
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
930
paulkc
P
K
  • Solved
is it possible copy data invoice information from sheet based on sheet name like this
Replies
4
Views
484
KalilMe
K

Forum statistics

Threads
1,224,607
Messages
6,179,871
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top