VBA to import various ranges of cell information on multiple worksheets and import into new workbook

[metrokurt]

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

 

[mumps]

Re: Need help with custom VBA to import various ranges of cell information on multiple worksheets and import into new workbook *long*

That's quite the task! I think it would be difficult to test any possible solutions without having a copy of your destination workbook and copies of 2 or 3 source workbooks. Perhaps you could upload a copy of each file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. It would help if you could summarize in a list format what exactly you want to do. If the workbooks contains confidential information, you could replace it with generic data. I can't promise a solution but I would be able to have a closer look and so would other Forum members.

 

[metrokurt]

Re: Need help with custom VBA to import various ranges of cell information on multiple worksheets and import into new workbook *long*

I know ... I know... which is why I was not expecting any replies, so thank you very much for reaching out!

I understand. I'll have to modify some of the existing workbooks with generic data, but I will upload and share some sample source files and the new sheet tomorrow when I get back into the office.

Thank you,
Kurt

 

[metrokurt]

Re: Need help with custom VBA to import various ranges of cell information on multiple worksheets and import into new workbook *long*

Here are the two excel files. One simulates the new order sheet. One is the old order sheet stripped down to just the "Item 1", "Item 2", etc. pages that I need to import data from.

I can figure out the "TOP SHEET" cell pulling code myself so I will remove that part of the request.

So to condense my request:

1. Need VBA code to open file explorer to select which old order workbook to import the data from. This will be a button to click. I don't want to import all of the old order into the new workbook, I just want to use this file picker to designate which file to import the necessary data from.

2. Need VBA code to search old order workbook for active "Item 1", "Item 2", "Item 3", "Item 4", etc. pages and copy three different table ranges from every active "Item #" worksheet to three separate worksheets in the new order workbook
a. see original post for information on how to determine active items and what the desired ranges & new sheet names are. If item is not active, no need to import.

3. Need VBA code to do the same thing as above except the sheet names are "RFCO#1 Quote", "RFCO#2 Quote", "RFCO#3 Quote", etc.
a. see original post for information on how to determine active items and what the desired ranges & new sheet names are. If item is not active, no need to import.

4. Need VBA code to eliminate any blank rows, but need to keep one blank row between the pasted values for each range.

This code can be written with variables that I can insert later, just need to annotate it as such. Any help at all, even pointing me in other directions, is appreciated.

Here is a link to sample of old order workbook to import: https://www.dropbox.com/s/ifcx31ry1jgdf1g/OldOrderSampleItems.xlsx?dl=0

Here is a link to sample of new order workbook to receive information: https://www.dropbox.com/s/zz7ebvp4zgyss2g/NewOrderSample.xlsx?dl=0

I have "example" pages in the new order sheet. This is what I am hoping the end result looks like. I can apply all the column formatting, etc. afterwards. I just need the information in the right cells.

Thank you,
Kurt

 

[mumps]

Re: Need help with custom VBA to import various ranges of cell information on multiple worksheets and import into new workbook *long*

See if this macro does what you want.

Sub CopyRanges()
    Application.ScreenUpdating = False
    Dim lastrow As Long, lastrow2 As Long
    Dim flder As FileDialog, FileName As String, FileChosen As Integer
    Dim wkbSource As Workbook, wkbDest As Workbook, ws As Worksheet
    Set wkbDest = ThisWorkbook
    Set flder = Application.FileDialog(msoFileDialogFilePicker)
    flder.Title = "Please Select an Excel File."
    flder.Filters.Add "Excel Macros Files", "*.xlsx"
    FileChosen = flder.Show
    FileName = flder.SelectedItems(1)
    Set wkbSource = Workbooks.Open(FileName)
    With wkbSource
        For Each ws In .Sheets
            If ws.Name Like "Item*" And ws.Range("N6").Value > 0 Then
                lastrow = ws.Range("A6:N16").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                ws.Range("A7:A" & lastrow) = ws.Range("A6")
                ws.Range("A6:N16").Copy
                wkbDest.Sheets("OrderRaw").Cells(wkbDest.Sheets("OrderRaw").Rows.count, "A").End(xlUp).Offset(2, 0).PasteSpecial xlPasteValues
                
                ws.Range("D84:D107") = ws.Range("A6")
                lastrow = ws.Range("K84:K95").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                ws.Range("D84:N" & lastrow).Copy
                wkbDest.Sheets("SpecialMaterials").Cells(wkbDest.Sheets("SpecialMaterials").Rows.count, "A").End(xlUp).Offset(2, 0).PasteSpecial xlPasteValues
                
                lastrow = ws.Range("L100:L108").Cells.Find("Total /w No MU", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                ws.Range("K100:K" & lastrow - 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
                lastrow = ws.Range("L100:L108").Cells.Find("Total /w No MU", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                ws.Range("D100:N" & lastrow - 1).Copy
                wkbDest.Sheets("LumpSum").Cells(wkbDest.Sheets("LumpSum").Rows.count, "A").End(xlUp).Offset(2, 0).PasteSpecial xlPasteValues
            ElseIf ws.Name Like "RFCO*" And ws.Range("N6").Value > 0 Then
                lastrow = ws.Range("A6:N16").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                ws.Range("A7:A" & lastrow) = ws.Range("A6")
                ws.Range("A6:N16").Copy
                wkbDest.Sheets("RFCORaw").Cells(wkbDest.Sheets("RFCORaw").Rows.count, "A").End(xlUp).Offset(2, 0).PasteSpecial xlPasteValues
                
                ws.Range("D84:D107") = ws.Range("A6")
                lastrow = ws.Range("K84:K95").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                ws.Range("D84:D" & lastrow).Copy
                lastrow2 = wkbDest.Sheets("RFCORaw").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                wkbDest.Sheets("RFCORaw").Cells(lastrow2 + 2, 1).PasteSpecial xlPasteValues
                
                ws.Range("E84:N" & lastrow).Copy
                wkbDest.Sheets("RFCORaw").Cells(lastrow2 + 2, 5).PasteSpecial xlPasteValues
                wkbDest.Sheets("RFCORaw").Range("B" & lastrow2 + 2 & ":B" & wkbDest.Sheets("RFCORaw").Cells(wkbDest.Sheets("RFCORaw").Rows.count, 1).End(xlUp).Row) = ws.Range("B83")
                
                lastrow = ws.Range("L100:L108").Cells.Find("Total /w No MU", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                ws.Range("K100:K" & lastrow - 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
                lastrow = ws.Range("L100:L108").Cells.Find("Total /w No MU", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                ws.Range("D100:D" & lastrow - 1).Copy
                lastrow2 = wkbDest.Sheets("RFCORaw").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                wkbDest.Sheets("RFCORaw").Cells(lastrow2 + 2, 1).PasteSpecial xlPasteValues
                ws.Range("E100:N" & lastrow - 1).Copy
                wkbDest.Sheets("RFCORaw").Cells(lastrow2 + 2, 5).PasteSpecial xlPasteValues
                wkbDest.Sheets("RFCORaw").Range("B" & lastrow2 + 2 & ":B" & wkbDest.Sheets("RFCORaw").Cells(wkbDest.Sheets("RFCORaw").Rows.count, 1).End(xlUp).Row) = ws.Range("B99")
            End If
        Next ws
        .Close savechanges:=False
    End With
    For Each ws In Sheets(Array("OrderRaw", "SpecialMaterials", "LumpSum", "RFCORaw"))
        ws.Rows(2).EntireRow.Delete
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[metrokurt]

Re: Need help with custom VBA to import various ranges of cell information on multiple worksheets and import into new workbook *long*

How can I buy you a digital donut my friend? Seems to be working perfectly. It also gave me great insight as to how to solve future problems so I very much appreciate all of the time you took to help me. Please PM me your e-mail address so I can send something deserving your way.

 

[mumps]

Re: Need help with custom VBA to import various ranges of cell information on multiple worksheets and import into new workbook *long*

Thank you for the kind words. I'm glad everything worked out for you.

 

[metrokurt]

Re: Need help with custom VBA to import various ranges of cell information on multiple worksheets and import into new workbook *long*

Ahh it was almost perfect

I was running through some of our orders testing everything out and noticed some of the RFCO# Quote's were not being picked up.

This was because the change order was a deduct to the overall contract, so a negative number.

Originally I said to check the N6 value and if it was above 0, to copy the ranges on that worksheet.
Unfortunately I gave bad information, I did not think ahead to this scenario of a negative change order amount.

I thought all I needed to change was the N6 value check. Instead of "> 0" I thought "<> 0" would work.

But now it only copies the table ranges on the worksheets with a negative N6 value and does not copy the worksheet if N6 is a positive value.

Also, don't be stubborn and gimme your e-mail! I have so many more questions and help needed as I work on this tool!

 

[metrokurt]

Re: Need help with custom VBA to import various ranges of cell information on multiple worksheets and import into new workbook *long*

Oops... I was right. My code change to <>0 works. It just didn't work the first time because the specific order I was importing & testing had an error in it. Fixed that error and tried to import the order and worked like a charm.

Happy Holidays.

 

[mumps]

Re: Need help with custom VBA to import various ranges of cell information on multiple worksheets and import into new workbook *long*

Glad it worked out. Merry Christmas and happy New Year to you as well.