Lisa Harris
New Member
- Joined
- Sep 19, 2016
- Messages
- 17
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
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
