hellscheshirecat
New Member
- Joined
- Jul 24, 2012
- Messages
- 12
Hi All!
I'm not really sure what to call the code I am hoping for but I will do my very best to describe what I'm looking to do and HOPEFULLY, someone can help me figure it out
I have several reports of inventory data that I want to combine automatically by running a macro, so that buyers have the data they need on hand. I don't want my employees to have to mess with formulas and having to drag values.
Right now my workbook (file name MrExcelHelp.xlsm) has four sheets in it "Master2","On Hand","Jan 14","RJan 14"... but when complete there will be many many more sheets with data from additional months (all of 2014 and will have 2015 data added)
Master2 is where I want to gather all the data into
On Hand has a report of what inventory is in stock based on SKU
Jan 14 has the sales data for the month of January 2014 based on ASIN
RJan 14 is the record of what the warehouse received during January 2014
My main reasons for not just using formulas are:
1) fear that someone will mess them up without realizing it and then place orders based on bad data *this is a big one*
2) the number of items will change every time the "On Hand" sheet is updated (daily)
3) likewise, during the month we are currently in that months sheets will change regularly
The Master Sheet has 8 columns (for now) and the headers are in row 2 (I have instructions in row 1) It looks like this:
<tbody>
</tbody>
I have macros to pull the data from On Hand into Master 2, and a macro to run them all at once:
It's not the most elegant but it's what I was able to piece together. I do have one issue with this... IF for example the first time the macro was ran there were 30 rows of data, if there are less rows (lets say 20) the next time the macro is run it will replace the data in rows 1-20 but leave rows 20-30... I'd like it to delete them.
The data for Column G on Master2 needs to come from Sheet "Jan 14", column E, header is in row 1 (Units Ordered). The connecting data for this in column A (Asin).
The "Jan 14" sheet looks like this:
<tbody>
</tbody>
So If I were to use a formula, on Sheet "Master2", in cell G3 I would write =IFERROR(VLOOKUP(B3,'Jan 14'!$A$1:$E$17,5,FALSE),"0") and then copy it down to all the rows that had data.
Lastly, The data for Column H on Master2 needs to come from Sheet "RJan 14". The data I want to pull is from Column E (quantity), the connecting data is in column C (sku)... BUT... the skus may repeat on multiple rows (meaning they may have been recieved into the warehouse more than once in a month) so I would want the values combined. (ex. SKU1... 6 peices were recieved jan 10th, and 4 peices were recieved Jan 20th, so I would want the returned value for SKU1 to be 10)
"RJan14" sheet looks like this:
<tbody>
</tbody>
If I were using a formula I would use =IFERROR(VLOOKUP(A3,RJan14!C2:G24,3,FALSE),"0"), then copy it down but that wouldn't account for the duplicate values.
So... what I think I need is two macros, one to pull the data from sheet "Jan 14" and one to pull the data from sheet "RJan 14" and combine the values... or one crazy macro that combines that with the macros I already have.
It would be great too if you could help me understand how to do it myself. Thank you so very, very much for reading all that. I hope someone can help me!
I'm not really sure what to call the code I am hoping for but I will do my very best to describe what I'm looking to do and HOPEFULLY, someone can help me figure it out
I have several reports of inventory data that I want to combine automatically by running a macro, so that buyers have the data they need on hand. I don't want my employees to have to mess with formulas and having to drag values.Right now my workbook (file name MrExcelHelp.xlsm) has four sheets in it "Master2","On Hand","Jan 14","RJan 14"... but when complete there will be many many more sheets with data from additional months (all of 2014 and will have 2015 data added)
Master2 is where I want to gather all the data into
On Hand has a report of what inventory is in stock based on SKU
Jan 14 has the sales data for the month of January 2014 based on ASIN
RJan 14 is the record of what the warehouse received during January 2014
My main reasons for not just using formulas are:
1) fear that someone will mess them up without realizing it and then place orders based on bad data *this is a big one*
2) the number of items will change every time the "On Hand" sheet is updated (daily)
3) likewise, during the month we are currently in that months sheets will change regularly
The Master Sheet has 8 columns (for now) and the headers are in row 2 (I have instructions in row 1) It looks like this:
| A | B | C | D | E | F | G | H | |
| 1 | ITEMS IN RED ARE DUPLICATES | YELLOW IS IN STOCK | GREEN HAS QTY SOLD | |||||
| 2 | sku | asin | product-name | condition | your-price | FBAINV | JAN2014 | REC JAN2014 |
<tbody>
</tbody>
I have macros to pull the data from On Hand into Master 2, and a macro to run them all at once:
Code:
Sub runallmacros()
skusfromonhand
asinsfromonhand
titlefromonhand
conditionfromonhand
pricefromonhand
qtyfromonhand
End Sub
Sub skusfromonhand()
Dim lastRow As Long
lastRow = Sheets("On Hand").Range("A" & Rows.Count).Row
Sheets("Master2").Range("A3:A" & lastRow).Value = Sheets("On Hand").Range("A2:A" & lastRow).Value
End Sub
Sub asinsfromonhand()
Dim lastRow As Long
lastRow = Sheets("On Hand").Range("C" & Rows.Count).Row
Sheets("Master2").Range("B3:B" & lastRow).Value = Sheets("On Hand").Range("C2:C" & lastRow).Value
End Sub
Sub titlefromonhand()
Dim lastRow As Long
lastRow = Sheets("On Hand").Range("D" & Rows.Count).Row
Sheets("Master2").Range("C3:C" & lastRow).Value = Sheets("On Hand").Range("D2:D" & lastRow).Value
End Sub
Sub conditionfromonhand()
Dim lastRow As Long
lastRow = Sheets("On Hand").Range("E" & Rows.Count).Row
Sheets("Master2").Range("D3:D" & lastRow).Value = Sheets("On Hand").Range("E2:E" & lastRow).Value
End Sub
Sub pricefromonhand()
Dim lastRow As Long
lastRow = Sheets("On Hand").Range("F" & Rows.Count).Row
Sheets("Master2").Range("E3:E" & lastRow).Value = Sheets("On Hand").Range("F2:F" & lastRow).Value
End Sub
Sub qtyfromonhand()
Dim lastRow As Long
lastRow = Sheets("On Hand").Range("K" & Rows.Count).Row
Sheets("Master2").Range("F3:F" & lastRow).Value = Sheets("On Hand").Range("K2:K" & lastRow).Value
End SubIt's not the most elegant but it's what I was able to piece together. I do have one issue with this... IF for example the first time the macro was ran there were 30 rows of data, if there are less rows (lets say 20) the next time the macro is run it will replace the data in rows 1-20 but leave rows 20-30... I'd like it to delete them.
The data for Column G on Master2 needs to come from Sheet "Jan 14", column E, header is in row 1 (Units Ordered). The connecting data for this in column A (Asin).
The "Jan 14" sheet looks like this:
| A | B | C | D | E | |
| 1 | Asin | Title | SKU | Buy Box Percentage | Units Ordered |
<tbody>
</tbody>
So If I were to use a formula, on Sheet "Master2", in cell G3 I would write =IFERROR(VLOOKUP(B3,'Jan 14'!$A$1:$E$17,5,FALSE),"0") and then copy it down to all the rows that had data.
Lastly, The data for Column H on Master2 needs to come from Sheet "RJan 14". The data I want to pull is from Column E (quantity), the connecting data is in column C (sku)... BUT... the skus may repeat on multiple rows (meaning they may have been recieved into the warehouse more than once in a month) so I would want the values combined. (ex. SKU1... 6 peices were recieved jan 10th, and 4 peices were recieved Jan 20th, so I would want the returned value for SKU1 to be 10)
"RJan14" sheet looks like this:
| A | B | C | D | E | F | G | |
| 1 | received-date | fnsku | sku | product-name | quantity | fba-shipment-id | fulfillment-center-id |
<tbody>
</tbody>
If I were using a formula I would use =IFERROR(VLOOKUP(A3,RJan14!C2:G24,3,FALSE),"0"), then copy it down but that wouldn't account for the duplicate values.
So... what I think I need is two macros, one to pull the data from sheet "Jan 14" and one to pull the data from sheet "RJan 14" and combine the values... or one crazy macro that combines that with the macros I already have.
It would be great too if you could help me understand how to do it myself. Thank you so very, very much for reading all that. I hope someone can help me!
