I have 2 workbooks that I will copy paste into a third (Macro-enabled) sheet for comparisons. Each contains the week/months inventory. Excel examples at the end
There are 15 columns with column A being the key (computer name). I want to take February's inventory and compare it to January's (then Feb with Mar, etc). The inventory comes in as a one sheet workbook and this is what I would like to have the macro-enabled workbook do for me. BOM will be a constant sheet in the workbook (can change but very infrequent). Column A is the only columns data that is relevant in this macro.
1. Pull in the data from workbook 1 to sheet1 rename sheet to old (Jan)
2. Pull in the data from workbook 2 to sheet2 rename sheet to new (Feb)
3. Remove all lines that, in column A from sheet 2 that start with either CT or DR
3. "BOM" will be a constant sheet in the workbook listing 300+ computers to auto remove from "new" (sheet 1 was done last month)
4. Have the data in "BOM" which only has data in the A column remove the matching devices in column A of "new" and the 14 other columns to the right of the matched column.
6. Clean out all blank spaces on "new" (moving cells up)
7. Using "Old" column A to find missing computers in column A of "New" -- Place this total in F2 of "BOM"
8. Using "New" column A to find missing computers in column A of "Old" -- Place this total in G2 of "BOM"
9. Count the rows in "New" and place this number is H2 of "BOM"
Brief example of what I am looking for:
BOM:
<tbody>
</tbody>
Old:
<tbody>
</tbody>
New:
<tbody>
</tbody>
F2 = 62 computers removed
G2 = 55 computers added
H2 = 2245 computers on sheet "New"
****** id="cke_pastebin" style="position: absolute; top: 559.5px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
<tbody>
</tbody>
I hope this is easy to understand. Sheet "New" will then be removed from the workbook and saved as it's own workbook to the desktop with the the title "workstation_count-RU2-YYYYMMDD"
I have no idea where to start and would appreciate any assistance you can provide. Maybe this would be easier with a formula instead of a macro. I'm going to try to work on this tonight, but I wanted to get it on paper before I started.
Thank you in advance
There are 15 columns with column A being the key (computer name). I want to take February's inventory and compare it to January's (then Feb with Mar, etc). The inventory comes in as a one sheet workbook and this is what I would like to have the macro-enabled workbook do for me. BOM will be a constant sheet in the workbook (can change but very infrequent). Column A is the only columns data that is relevant in this macro.
1. Pull in the data from workbook 1 to sheet1 rename sheet to old (Jan)
2. Pull in the data from workbook 2 to sheet2 rename sheet to new (Feb)
3. Remove all lines that, in column A from sheet 2 that start with either CT or DR
3. "BOM" will be a constant sheet in the workbook listing 300+ computers to auto remove from "new" (sheet 1 was done last month)
4. Have the data in "BOM" which only has data in the A column remove the matching devices in column A of "new" and the 14 other columns to the right of the matched column.
6. Clean out all blank spaces on "new" (moving cells up)
7. Using "Old" column A to find missing computers in column A of "New" -- Place this total in F2 of "BOM"
8. Using "New" column A to find missing computers in column A of "Old" -- Place this total in G2 of "BOM"
9. Count the rows in "New" and place this number is H2 of "BOM"
Brief example of what I am looking for:
BOM:
| LT88865 |
| LT88868 |
| PC88844 |
| PC88848 |
| PCR33333 |
<tbody>
</tbody>
Old:
| LT23422 | Win10 | sdaf | asdf | adsf |
| LT23424 | Win10 | adfs | sdfs | sasdf |
| LT23432 | Win10 | asdfasd | adfasd | adsfad |
| PC44323 | Win7 | dsdfs | sdfe | sasdf |
| PC44345 | Win8.1 | sdaf | asdf | adsf |
| PCR33243 | Win2012 | sdaf | adfasd | adsfad |
<tbody>
</tbody>
New:
| LT23422 | Win10 | sdaf | asdf | adsf |
| LT23444 | Win10 | sdaf | dsfds | sdfad |
| LT23445 | Win7 | asdf | sdfsada | sdgfsa |
| PC44322 | Win18 | asldf | sdaf | safd |
| PC44345 | Win8.1 | sdaf | asdf | adsf |
| LTC33456 | Win10 | asdf | asdf | asdfa |
<tbody>
</tbody>
F2 = 62 computers removed
G2 = 55 computers added
H2 = 2245 computers on sheet "New"
****** id="cke_pastebin" style="position: absolute; top: 559.5px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
| Win10 |
<tbody>
</tbody>
I hope this is easy to understand. Sheet "New" will then be removed from the workbook and saved as it's own workbook to the desktop with the the title "workstation_count-RU2-YYYYMMDD"
I have no idea where to start and would appreciate any assistance you can provide. Maybe this would be easier with a formula instead of a macro. I'm going to try to work on this tonight, but I wanted to get it on paper before I started.
Thank you in advance
