Find value in one column and return value from same row but different column in another tab

Vickie Hartley

New Member
Joined
Aug 19, 2015
Messages
13
I work for a non-profit community housing agency and created a workbook that has multiple tabs. One of the tabs is a spreadsheet that contains 22 columns (scope assumptions). On another tab I want to pull data from this large spreadsheet. There are usually at least a 100 rows, each room in a house has multiple lines and tasks with sub-totals by room. The spreadsheets primary purpose is to communicate to the construction crew what needs to be done in each room and what the budget is. Here is an example of the primary spreadsheet.

LocationTaskDescriptionLabor HoursLabor CostsMaterialsSubsOH,G&A, ProfitContract Amount
Front PorchDemolitionDemo garbage, existing windows, steps3905060200
Front PorchElectricalProperly install wire mold at outlet close to ceiling and exterior light16571236
Sub-Total9050165131436
Living RoomElectricalFurnish and install new light switch and cover plate502171
Living RoomDoors & WindowsFurnish and install new trim and windows (3)8300300257857
Sub-Total830030050278928
*******ElectricalFurnish and install new 240V outlet for range and 120V outlet for fridge500214714
*******Plumbing******* sink - Furnish and install new PVC drain, vent and pex supply lines with shut off valves and all sundries for complete install10504501500
Sub-Total00015506642214

<tbody>
</tbody>

A second, third and fourth spreadsheet is needed to communicate to the sub-contractors what needs to be done. One spreadsheet each for Electrical, Plumbing, and HVAC. Ideally I want to create formulas that will look for every occurrence of a given task (i.e. "Electrical") in column B from the primary spreadsheet and return the corresponding location, description, and cost for each occurrence. For example:

Electrical Scope
LocationDescriptionBudget
Front PorchProperly install wire mold at outlet close to ceiling and exterior light236
Living RoomFurnish and install new light switch and cover plate71
*******Furnish and install new 240V outlet for range and 120V outlet for fridge714

<tbody>
</tbody>


Can you please give me some guidance and/or answers?

I often use Mr. Excel to solve my Excel problems by looking for similar problems or using the books I have purchased but this time, I could not find a solution so I posted my first request for help. I have been using this site for about 3 years.

I have tried a multitude of nested formulas (search, if, lookup, index, etc) to get this to work.
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
If you're comfortable with a vba solution, you might give this a try...

Code:
Sub CreateSubcontractorSheets()

Dim LastCol As Long
Dim LastRow As Long
Dim Subcontractor As String
Dim i As Long
Dim j As Long
Dim ws As Worksheet

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'''''   Deletes existing Electrical, Plumbing and HVAC worksheets
For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "Electrical" Or ws.Name = "Plumbing" Or ws.Name = "HVAC" Then ws.Delete
Next

'''''   Establishes the range extents of the Primary worksheet; turns on AutoFilter
Sheets("Primary").Activate
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
ThisWorkbook.Sheets("Primary").Range(Cells(1, 1), Cells(1, LastCol)).AutoFilter

'''''   Filters the Primary worksheet to each Subcontractor, then copies/pastes to a new worksheet
For i = 1 To 3
    If i = 1 Then Subcontractor = "Electrical"
    If i = 2 Then Subcontractor = "Plumbing"
    If i = 3 Then Subcontractor = "HVAC"
    
    Sheets("Primary").Range(Cells(1, 1), Cells(LastRow, LastCol)).AutoFilter Field:=2, Criteria1:=Subcontractor
    Sheets("Primary").Range(Cells(1, 1), Cells(LastRow, LastCol)).Copy
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Subcontractor
    ActiveSheet.Paste
    '''''   Deletes non-required columns
    For j = LastCol To 1 Step -1
        If Cells(1, j).Value <> "Location" And Cells(1, j).Value <> "Description" And Cells(1, j).Value <> "Contract Amount" And Cells(1, j).Value <> "Budget" Then Cells(1, j).EntireColumn.Delete
        If Cells(1, j).Value = "Contract Amount" Then Cells(1, j).Value = "Budget"
    Next j
    Cells.Columns.AutoFit
    
    Sheets("Primary").Activate
    Application.CutCopyMode = False
Next i

'''''   Turns off AutoFilter
ThisWorkbook.Sheets("Primary").Range(Cells(1, 1), Cells(1, LastCol)).AutoFilter

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
Comments are embedded in the code.

Cheers,

tonyyy
 

Vickie Hartley

New Member
Joined
Aug 19, 2015
Messages
13
I have never created or used a vba - have only heard about them. Will do some research on how this is done and will post a follow-up. Thanks for the help.
 

Vickie Hartley

New Member
Joined
Aug 19, 2015
Messages
13
I have researched a three sites and read multiple tutorials but when I tried to run the code I got an error 'runtime 1004. I'm sure it has to do with range but not familiar with code language to cipher out the problem. I did determine that sheet name correction needed to be done, which I did. Will keep trying but I have already spent 3.5 hours and have to get back to other tasks. If anyone can give me a hint on my error, I would really appreciate it.
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
It sounds like you've already changed "Primary" to match your sheet name.

When your get the Runtime 1004 error, which line of code is highlighted?
 

Vickie Hartley

New Member
Joined
Aug 19, 2015
Messages
13
That's the trouble - no highlighted or red font color on any words. Sorry I couldn't answer your question.
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
If you have a little time, you can try stepping through the code...

Open the Visual Basic Editor and place your cursor anywhere in the module with the code. Pressing the F8 key will highlight a line of code; pressing F8 again will execute that line and highlight the next. You can continue this until the entire program is run, or more likely in this case, an error is generated. And you'll know which line of code is causing the error.
 

Vickie Hartley

New Member
Joined
Aug 19, 2015
Messages
13
ThisWorkbook.Sheets("Original scope assumptions").Range(Cells(1, 1), Cells(1, LastCol)).AutoFilter

This line triggered the error 1004
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
Is the "Original scope assumptions" worksheet protected?
 

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top