Excel VBA - Search a master document for data in column B of other worksheet, then update row or add new row if new value. Repeat for entire list

[Lisa Harris]

Hi,

I am in desperate need of help as unable to find anything that works or examples online that I can modify for my purpose.

I have a 'workflow' document, which is essentially a brief sheet, so a new one used each time a new brief comes in listing all items and requirements. I currently have this set up so when the macro button is pressed, a summary of that brief is populated into one row on another tab and then submitted / feeds into a master 'WIP' workbook and has enabled a 'master' live document for the team to manage the workload. If the brief submitted is an update it checks the WIP for the unique 'job number' to see if the job already exists, and if it does, pastes over the 'foundrow' with the updated row of information and if not, goes to next available row and pastes. This all works brilliantly. (Fyi - the workflow 'brief' sheet is set up in the macro so it can be saved as any file name and the Macro still works, as this will always be saved as the briefs unique name and number)

However, I now need to do a similar task, but this time to go on to each of the managers individual WIPs (PM WIP), but instead of just a summary row, need to add all items / SKU's (rows ) from the brief sheet (likely to be between 1 and 50 starting at row 2) and this is where I am having trouble. I can't find code that works through the list of items (called SKU's - the individual items unique reference) in the column B of the workflow, search for them all in the PM WIP column B, and then paste the relevant row A:AD from A of the found row, or next blank row if not found in the master PM WIP. My macro only works when specifying one cell, so in this case the first cell ("B2") and copying a specific row, so in this case the first row ("A2:AD2") and pasting it accordingly in to PM WIP. I have tried applying the 'looping' and 'offset' rules, but cannot find a way for these rules to work.
Essentially, I am trying to build a live document for the PM's (PM WIP) listing ALL SKU's / items they have on their schedule, which gets added to or updated each time a new or updated brief sheet is 'submitted'.To confirm, the PM WIP available rows start from row 3, and the Workflow is tab 'PMs own WIP' and data starts from row 2.


My code that works for just one row is:

Sub PMLoopCopyPaste()


Dim sSheetName As String

sSheetName = ActiveWorkbook.Name

' this has allowed my workbook "workflow" to be saved as any filename and the macro still work, as the workflow will be saved as a different brief name each time


Workbooks.Open Filename:= _
"\location of file\PM WIP example.xlsm" _
, UpdateLinks:=0
'have now opened the PM WIP workbook I need to search and paste rows into

Windows(sSheetName).Activate

' now going back and activating the initial workflow workbook

Sheets("PMs own WIP").Select

' this has told it which tab to go on to




SKU = Range("B2").Value ' this is giving the 'SKU' in cell B2 a "name" to be used later in my search criteria. However, need to find a way without limiting it to first row of data only. Want it to search for first one, then when done, move on to next, etc, until all searched for , updated or added accordingly. This doesn't work..... SKU = Range("B2:B").Value


Range("A2:AD2").Select 'defining the first row that is to be copied - however I need to know a way to select the row depending on the SKU we are looking for - won't work unless I specify an exact row as well as column


Selection.Copy

Windows("PM WIP example.xlsm").Activate

Sheets("PMs own WIP").Select

' Asking to revert back to the PM WIP workbook and sheet


If Range("B3:B" & Rows.Count).Find(SKU) Is Nothing Then

Range("A" & Rows.Count).End(xlUp).Offset(1).Select

'asks it to search for SKU on WIP, and if it does not exist already, then to go to next available row

Else

Foundrow = Range("B3:B" & Rows.Count).Find(SKU).ROW
Range("A" & Foundrow).Select


'if the SKU does exist then tells it to select the cell with that SKU starting a column A


End If

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Pastes the inital row selection depending where the active cell is - so column A of new row or updating the "foundrow"


Range("A3").Select

'then tells it to go back to cell A3 so not left at bottom of a spreadsheet


Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close

'tells it to save "PM WIP example" and then close it down


Sheets("PMs Own WIP").Select
Range("B2").Select
Sheets("Workflow Brief").Select
Range("A3").Select
ActiveWorkbook.Save

'tells it to go back to the workflow sheet and select workflow brief tab

MsgBox "The PM WIP has successfully been updated.", vbInformation + vbOKOnly, "PM Update Complete"

'provides a message box to confirm to user the update to WIP has happened


End Sub

 

[Lisa Harris]

Any help would be much appreciated! Thanking you in advance, and apologies if this is something really simple! I feel I've tried everything, pestered everyone at work that may know and googled every forum. Thanks all, Lisa

 

[steve the fish]

Hi Lisa. Its a bit of an essay you have written there. Could you be a bit more concise with your problem? It may be helpful to show some sample data.

 

[Lisa Harris]

Hi Steve - thanks so much for the speedy reply and yes I have waffled - so sorry! Hope this helps / bit clearer. All new to this I'm afraid, hence the clumsiness. Many thanks for looking at this for me! Lisa

Attempt #2:

this is a snapshot of work book 1 - the Workflow brief sheet, tab 3, showing only columns A:F:

A	B	C	D	E	F
R1 Campaign	Client Ordering Unit_SKU	Element Name	Height (mm)	Width (mm)	Depth (3D)
R2 test_test__V1	123456	item 1	500	100	2
R3 test_test__V1	234567	item 2	500	100	2
R4 test_test__V1	345678	item 3	500	100	2
R5 test_test__V1	456789	item 4	500	100	2

<tbody>
</tbody><colgroup><col><col><col><col span="3"></colgroup>

Want to search for ALL of the 'SKU' references in column B in this workbook, in the following workbook also in column B....
If the SKU is already there, paste the relevant row A:AD over the top of the existing row (for example below SKU 234567 already exists, so if submitted again, row would be updated). If it does not already exist, i.e. the other 3 SKU's in my example, paste their relevant row A:AD into next available row (so begin pasting in R4 in the example given below).....


Workbook 2 - the PM WIP, tab 1:

	A	B	C	D	E	F
R1	-	-	-	-	-	-
R2	Campaign	Client Ordering Unit_SKU	Element Name	Height (mm)	Width (mm)	Depth (3D)
R3	test_test__V1	234567	item 2	500	100	2
R4
R5
R6
R7
R8
R9

<tbody>
</tbody><colgroup><col><col><col><col><col span="3"></colgroup>

 

[steve the fish]

I haven't been able to test this so try it on a copy of your workbook!!!!

Set wb1 = ThisWorkbook 'Workbook 1
Set wb2 = Workbooks.Open(Filename:="\location of file\PM WIP example.xlsm") 'Workbook 2
Set ws1 = wb1.Sheets("Sheet1") 'change to whatever sheet called
Set ws2 = wb2.Sheets("Sheet1") 'change to whatever sheet called
lr = ws1.Range("B" & Rows.Count).End(xlUp).Row 'lastrow of data to get number of loops required
If lr = 1 Then Exit Sub 'exit if no data
For i = 2 To lr
    myVal = ws1.Range("B" & i).Value
    If Len(myVal) > 0 Then 'checks cell not empty
        lr2 = ws2.Range("B" & Rows.Count).End(xlUp).Row 'lastrow of target worksheet
        With ws2.Range("B2:B" & lr2)
            Set c = .Find(myVal, LookIn:=xlValues, LookAt:=xlWhole)
            If Not c Is Nothing Then
                ws1.Rows(i).Copy
                ws2.Rows(c.Row).PasteSpecial xlPasteValues
            Else
                ws1.Rows(i).Copy
                ws2.Rows(lr2 + 1).PasteSpecial xlPasteValues
            End If
        End With
    End If
Next

 

[Lisa Harris]

Steve!!! You Legend!!!! that has worked perfectly! (checked many times and ways!)....and I am in utter shock this has finally been achieved! Thank you so much....and for being so quick.

The only slight thing, is it adds in a line of '0' due to the 'empty rows' being populated by a formula result. Is there any way of getting it to ignore these defaulted rows and only search for the rows populated with a valid 'SKU' number? Not to worry if not - you have done more than enough! Thank you so much

Lisa

Campaign	Client Ordering Unit_SKU	Element Name	Height (mm)	Width (mm)	Depth (3D)
FW16_Lisa A test_Global Prototyping_V3	123456	item 1	100	100	2
FW16_Lisa A test_Global Prototyping_V3	234567	item 2	100	100	2
FW16_Lisa A test_Global Prototyping_V3	345678	item 3	100	100	2
FW16_Lisa A test_Global Prototyping_V3	456789	item 4	100	100	2
FW16_Lisa A test_Global Prototyping_V3	567890	item 5	100	100	2
FALSE	0	0	0	0	0
FW16_Lisa A test_Global Prototyping_V3	654321	item 1	210	297	2
FW16_Lisa A test_Global Prototyping_V3	543210	item 2	210	297	2
FW16_Lisa A test_Global Prototyping_V3	432109	item 3	210	297	2
FW16_Lisa A test_Global Prototyping_V3	321098	item 4	210	297	2
FW16_Lisa A test_Global Prototyping_V3	210987	item 5	210	297	2

<tbody>
</tbody><colgroup><col><col><col><col span="3"></colgroup>

 

[steve the fish]

Maybe this will fix that?

If myVal > 0 Then

rather than

If Len(myVal) > 0 Then

 

[Lisa Harris]

Once again....PERFECT! Thank you so so much Steve - what a fantastic service! Have a great day

Lisa

 

[Lisa Harris]

Hi Super Steve, apologies to ask a third question! But how do I limit the copy and paste to columns A:AD?

I have free type fields from AE:AJ in WB2 that need to remain when row is updated, hence the need to limit the columns. Have tried a few options but keep getting debug errors.


Many thanks in advance

Lisa