Hi everyone, new to the community, but it seems like the best place to ask for help on the web.
Hope someone here can help me out, or at least point me in the right direction of a similar example.
I believe the programming logic I wrote is sound. I’m using Excel 2003, not sure how this effects my situation but I have pretty much lost all of my VB syntax knowledge.
Source material is a large workbook with tons of data and multiple sheets; I just want to create a single sheet having each SKU associated with a specific cost. I’m pretty sure a macro would be the best way to approach this, as sometimes the Row location varies based on the sheet. The SKUs are always in Row 1, but sometimes the Row labeled "Net Landed Cost" can be in a different row (However, it's always in Column A).
I'm not sure how time-consuming this macro would take to write but I think it would be pretty simple for someone who is as familiar with the syntax as I used to be.
Here is what I need to do:
1. Create a new workbook, or sheet if it is easier. We’ll call it “Z”
- a. Label A1 = “SKU”
- b. Label B1 = “Net Landed Cost”
2. For each sheet “N” in workbook search Column A for the first occurrence of “Net Landed Cost” to determine the Row “X” we will be extrapolating data from. When there are no more sheets “N” remaining, the macro stops.
3. I suppose I need to make a loop “n” to move across Row(“X”) horizontally starting at column B (as column A will be labeled “Net Landed Cost”)
- IF Cell at (Column location n in loop, Row(“X”)) has a value greater than or equal to 0, copy the data from this cell into sheet “Z”, to the next available empty row in Column B. This is the cost I need. Then copy the data from Cell=(Column location n in loop, row 1), into sheet “Z”, to the next available empty row in Column A. (These should both be in the same row, eg Matching SKUs with the cost).
- IF Cell at (Column location n in loop, Row(“X”)) is blank, move to next column)
- WHEN no further columns in Row(“X”) have data (e.g. end of the worksheet horizontally), move to the next sheet “N”.
4. Here are some concerns I have that may or may not be issues in writing this macro:
- Ensuring that the Row “X” is determined by the first instance of “Net Landed Cost” as “Net Landed Cost” will appear multiple times in Column A of each sheet.
- Row “X” (which has my costs), is not consistent between sheets, however my SKUs will always be in the cell that is the first Row in the column that we are on in the Loop. (e.g. if Row “X” is 11, And we are at n=Column AB in the loop, the Cost will be in CELL(AB11) and the SKU will be in CELL(AB1).
- Ensuring that the loop moves on to the next sheet after completing Row “X”. I do not want the data from further occurrences of “Net Landed Cost,” or I will not be able to attain the correct information.
- Ensuring that the loop for Row “X” moves across columns all the way to the last column with data, sometimes there are multiple blank columns in a row.
- Ensuring that when copying data over to sheet “Z” (place 2>b>i in outline), both SKU and cost are copied to the same row, and that the loop then properly moves to the next row.
Bookmarks