Hello,
First thanks to anyone who can help out with this macro/code. I've been searching the web for a while and trying to watch YouTube videos and messing with formulas, but alas this is slightly outside my realm to get together without spending another couple of months understanding all of the various lines of code to make it work. I am hoping I can get some help on this part of the code to speed up the process.
I've been working on a new estimating template for my company which I've made great progress on - it takes a CSV output from a PDF editing program and uses all that information to generate quotes (for construction).
But before I finish up with that I have been tasked with creating a tool to help manage some of our existing projects and in order to do it I need to import information from old workbooks (multiple sheets) into multiple worksheets in a newly created workbook.
Here is all the pertinent information:
1. Every existing project is an individual .xls file, so I will need the macro to start off by being able to click a button to open file explorer and select the correct .xls file to import data from. The naming structure for projects is "ORDER 20125.xls", "ORDER 20126.xls", etc. The problem is that every project is in it's own uniquely named job folder so this has to be a manual file selection process.
2. The first bits of information I need to import are from a worksheet named "TOP SHEET". I would like to put this information on a worksheet named "Status" in the new workbook. The "Status" worksheet is already existing so it does not need to be created.
I need to pull the following cells from the "TOP SHEET" to "Status":
cell M1 --> cell D5
cell M3 --> cell D6
cell I13 --> cell D7
concatenate cell I8:I11 --> cell D8
cell C8 --> cell D9
cell C11 --> cell D10
cell C12 --> cell D11
cell C13 --> cell G11
cell C14 --> cell K11
cell L17 --> cell D12
cell G17 --> cell D13
cell N9 --> cell D14
cell N10 --> cell G14
cell N11 --> cell K14
3. Other worksheets to import the information from the original workbook are named "Item 1", "Item 2", "Item 3", etc. There are multiple ranges on each worksheet, and each range is pasted into a differently named worksheet in the new workbook. All of these new worksheet names will be existing, so the VBA only needs to import to these specifically named worksheets and not create new worksheets.
The order workbook is populated with worksheet templates up to "Item 20", but I only want to import the items that are filled out. I think the best way to check for what Item's are active is to check for $ value in cell N6 which has the total. If there is a $ value in N6, it should be imported. If there is 0 value in N6, there is nothing to import for that item.
4. There are multiple ranges of data I need from each "Item #" worksheet. The ranges need to be copied as value (not formula) so the amounts can stay intact.
The first table range is A6:N16 - this needs to be imported into a worksheet named "OrderRaw" starting in cell A2.
There are multiple rows that are blank, I'm hoping those can be filtered out afterwards with another macro to delete blank rows. I want to keep one blank row in between the different ranges that get imported. After the range is copied, I need the "Item #" (cell A6) in column A filled in for every row with data pertaining to that Item.
The second range is E84:N95 - this needs to be imported into a worksheet named "SpecialMaterials" starting in cell B2.
There are multiple rows that are blank, I'm hoping those can be filtered out afterwards with another macro to delete blank rows. I want to keep one blank row in between the different ranges that get imported. After the range is copied, I need the "Item #" (cell A6) in column A filled in for every row with data pertaining to that Item.
The third range is E100:N107 - this needs to be imported into a worksheet named "LumpSum" starting in cell B2.
There are multiple rows that are blank, I'm hoping those can be filtered out afterwards with another macro to delete blank rows. I want to keep one blank row in between the different ranges that get imported. After the range is copied, I need the "Item #" (cell A6) in column A filled in for every row with data pertaining to that Item.
Here are some photos of the raw Item information now, and an example of what I am hoping it can get formatted into. I only imported two items, but it would just continue on like this until no more items left to import.
Existing Order, Special Material, and Lump Sum information layout:
What I am hoping the new "OrderRaw", "SpecialMaterials", and "LumpSum" worksheets looks like after importing:
5. This same import process needs to happen for another set of worksheets, but instead of pasting into three separate worksheets, all of the tables would be pasted into one worksheet. The existing worksheets the information needs to be pulled from are named "RFCO#1 Quote"", "RFCO #2 Quote", "RFCO #3 Quote", etc. The same check function can be applied to import only worksheets that have information filled out - so check for value in cell N6. If there is a $ value, import the worksheet. If N6 $ value is 0, do not import the worksheet.
All of the previous table ranges to copy are the exact same as the "Item #" worksheets - A6:N16, E84:N95, E100:N107 - these ranges need to be imported into an existing worksheet named "RFCORaw" starting in cell A2.
Once again the ranges need to be copied as values not formulas. I need the ranges pulled from E84:N95 and E100:N107 to be copied in alignment with the A6:N16 ranges so formatting stays correct.
All of the ranges need to have the "RFCO #" (cell A6) applied in Column A.
For range E84:N95 values, I would like to populate column B with the label from cell B83
For range E100:N107 values, I would like to populate column B with the label from cell B99
Once again I don't want any of the extra blank rows in the Special Materials or Lump Sum tables, so do not import any information if the row is blank. The best way to check for this is if there is no value in column N, then that particular line item does not need to be imported (caution: this does not hold true for importing the "Item #" worksheets, there are line items in column K that have no value in column N, but still need to imported into the row for these worksheets).
I would like one blank row between each range that gets copied.
Here is an example of what I am hoping the data looks like after it is imported into the new worksheet:
I think that just about covers it. I know this is a very big request so I'm not expecting any help. I'm going to continue trying to figure out the best way to approach this myself in the mean time. Any help is greatly and genuinely appreciated.
Kurt
First thanks to anyone who can help out with this macro/code. I've been searching the web for a while and trying to watch YouTube videos and messing with formulas, but alas this is slightly outside my realm to get together without spending another couple of months understanding all of the various lines of code to make it work. I am hoping I can get some help on this part of the code to speed up the process.
I've been working on a new estimating template for my company which I've made great progress on - it takes a CSV output from a PDF editing program and uses all that information to generate quotes (for construction).
But before I finish up with that I have been tasked with creating a tool to help manage some of our existing projects and in order to do it I need to import information from old workbooks (multiple sheets) into multiple worksheets in a newly created workbook.
Here is all the pertinent information:
1. Every existing project is an individual .xls file, so I will need the macro to start off by being able to click a button to open file explorer and select the correct .xls file to import data from. The naming structure for projects is "ORDER 20125.xls", "ORDER 20126.xls", etc. The problem is that every project is in it's own uniquely named job folder so this has to be a manual file selection process.
2. The first bits of information I need to import are from a worksheet named "TOP SHEET". I would like to put this information on a worksheet named "Status" in the new workbook. The "Status" worksheet is already existing so it does not need to be created.
I need to pull the following cells from the "TOP SHEET" to "Status":
cell M1 --> cell D5
cell M3 --> cell D6
cell I13 --> cell D7
concatenate cell I8:I11 --> cell D8
cell C8 --> cell D9
cell C11 --> cell D10
cell C12 --> cell D11
cell C13 --> cell G11
cell C14 --> cell K11
cell L17 --> cell D12
cell G17 --> cell D13
cell N9 --> cell D14
cell N10 --> cell G14
cell N11 --> cell K14
3. Other worksheets to import the information from the original workbook are named "Item 1", "Item 2", "Item 3", etc. There are multiple ranges on each worksheet, and each range is pasted into a differently named worksheet in the new workbook. All of these new worksheet names will be existing, so the VBA only needs to import to these specifically named worksheets and not create new worksheets.
The order workbook is populated with worksheet templates up to "Item 20", but I only want to import the items that are filled out. I think the best way to check for what Item's are active is to check for $ value in cell N6 which has the total. If there is a $ value in N6, it should be imported. If there is 0 value in N6, there is nothing to import for that item.
4. There are multiple ranges of data I need from each "Item #" worksheet. The ranges need to be copied as value (not formula) so the amounts can stay intact.
The first table range is A6:N16 - this needs to be imported into a worksheet named "OrderRaw" starting in cell A2.
There are multiple rows that are blank, I'm hoping those can be filtered out afterwards with another macro to delete blank rows. I want to keep one blank row in between the different ranges that get imported. After the range is copied, I need the "Item #" (cell A6) in column A filled in for every row with data pertaining to that Item.
The second range is E84:N95 - this needs to be imported into a worksheet named "SpecialMaterials" starting in cell B2.
There are multiple rows that are blank, I'm hoping those can be filtered out afterwards with another macro to delete blank rows. I want to keep one blank row in between the different ranges that get imported. After the range is copied, I need the "Item #" (cell A6) in column A filled in for every row with data pertaining to that Item.
The third range is E100:N107 - this needs to be imported into a worksheet named "LumpSum" starting in cell B2.
There are multiple rows that are blank, I'm hoping those can be filtered out afterwards with another macro to delete blank rows. I want to keep one blank row in between the different ranges that get imported. After the range is copied, I need the "Item #" (cell A6) in column A filled in for every row with data pertaining to that Item.
Here are some photos of the raw Item information now, and an example of what I am hoping it can get formatted into. I only imported two items, but it would just continue on like this until no more items left to import.
Existing Order, Special Material, and Lump Sum information layout:
What I am hoping the new "OrderRaw", "SpecialMaterials", and "LumpSum" worksheets looks like after importing:
5. This same import process needs to happen for another set of worksheets, but instead of pasting into three separate worksheets, all of the tables would be pasted into one worksheet. The existing worksheets the information needs to be pulled from are named "RFCO#1 Quote"", "RFCO #2 Quote", "RFCO #3 Quote", etc. The same check function can be applied to import only worksheets that have information filled out - so check for value in cell N6. If there is a $ value, import the worksheet. If N6 $ value is 0, do not import the worksheet.
All of the previous table ranges to copy are the exact same as the "Item #" worksheets - A6:N16, E84:N95, E100:N107 - these ranges need to be imported into an existing worksheet named "RFCORaw" starting in cell A2.
Once again the ranges need to be copied as values not formulas. I need the ranges pulled from E84:N95 and E100:N107 to be copied in alignment with the A6:N16 ranges so formatting stays correct.
All of the ranges need to have the "RFCO #" (cell A6) applied in Column A.
For range E84:N95 values, I would like to populate column B with the label from cell B83
For range E100:N107 values, I would like to populate column B with the label from cell B99
Once again I don't want any of the extra blank rows in the Special Materials or Lump Sum tables, so do not import any information if the row is blank. The best way to check for this is if there is no value in column N, then that particular line item does not need to be imported (caution: this does not hold true for importing the "Item #" worksheets, there are line items in column K that have no value in column N, but still need to imported into the row for these worksheets).
I would like one blank row between each range that gets copied.
Here is an example of what I am hoping the data looks like after it is imported into the new worksheet:
I think that just about covers it. I know this is a very big request so I'm not expecting any help. I'm going to continue trying to figure out the best way to approach this myself in the mean time. Any help is greatly and genuinely appreciated.
Kurt
