Macro/Formula to Select Rows of data and output to new Worksheets(?)

[HoustonREFinance]

Hello, I am using excel 2007.

I am working with a project whereby I am trying to understand how to create a form button macro that will select the applicable rows of data that is particular to a Project (my input worksheets contains multiple 'projects', like below, Projects 1, 2, 3, etc., so I'd probably need a button for each project, or maybe one button could handle all Projects (?), and then 'Output '(copy, paste, link, etc.) the applicable rows (where the "Include?" Column =1, 0 to exclude) into a new worksheet. That may be straightforward enough, but I do have a second condition/issue that you might have some advice upon. Example below:

My Inputs Worksheet (User inputs are in Blue)
[TABLE="class: grid, width: 975"]
<colgroup><col span="3"><col><col><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Include?[/TD]
[TD]Project[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Include?[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Title[/TD]
[TD] [/TD]
[TD]Hotel [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]Apartments[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD] [/TD]
[TD]H[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Variable 1[/TD]
[TD]Variable 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]Variable 1[/TD]
[TD]Variable 2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Budget Item[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total [/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Land[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Purchase[/TD]
[TD] [/TD]
[TD="align: right"]20,000[/TD]
[TD="align: right"]$20.00[/TD]
[TD] [/TD]
[TD="align: right"]$400,000[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD="align: right"]30,000[/TD]
[TD="align: right"]$20.00[/TD]
[TD] [/TD]
[TD="align: right"]$600,000[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]Closing Cost[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.00%[/TD]
[TD] [/TD]
[TD="align: right"]$0[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD="align: right"]3.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$18,000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Total (#2)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$400,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$618,000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]Shared Costs[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Roads[/TD]
[TD] [/TD]
[TD="align: right"]20,000[/TD]
[TD="align: right"]$10.00[/TD]
[TD] [/TD]
[TD="align: right"]$200,000[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$0.00[/TD]
[TD] [/TD]
[TD="align: right"]$0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Total (#2)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$200,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Building Cost[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Tower[/TD]
[TD] [/TD]
[TD="align: right"]150,000[/TD]
[TD="align: right"]$300.00[/TD]
[TD] [/TD]
[TD="align: right"]$45,000,000[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD="align: right"]150,000[/TD]
[TD="align: right"]$125.00[/TD]
[TD] [/TD]
[TD="align: right"]$18,750,000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Parking[/TD]
[TD] [/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]$15,000[/TD]
[TD] [/TD]
[TD="align: right"]$5,250,000[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]$10,000[/TD]
[TD] [/TD]
[TD="align: right"]$3,000,000[/TD]
[/TR]
[TR]
[TD] 1[/TD]
[TD]Total (#3)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$50,250,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$21,750,000[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

So the 'Output' worksheet from the macro function would result in a separate worksheets for Project 1 and Project 2. I can see two macro form buttons on this page, that basically select the columns in the first row that are equal to the Project 1, or 2, etc, and then select the rows that Include the variable "1" along the left side of each project entry page.

The Output worksheets could just copy everything, ignoring the "include" condition, but I'd just need a follow up function that would group and collapse, or 'hide' the rows that do not have applicable data on the Output Worksheet for Print Report purposes (which could be a separate form button macro question on its own). That would be fine as well.

The second condition is that the Output sheets would be integrated into another set of worksheets that each perform calculations based on the values in the Inputs worksheets. So those worksheets would need to be able to identify and 'link' to the appropriate values so as not to create the #Ref error. So for instance, for Project 1, the Output worksheet would be integrated into a series of four other worksheets, where by they would be trying to reference the values associated with Total #1, Total #2 and Total #3. I am not that familiar with Named Ranges, but I sense this would be cumbersome since I'll have so many projects referencing the same Totals, but within their separate "Project" worksheets.

I know this is a long question, and may be answered in a couple of different parts, but any help would be appreciated! (as my current process for integrating these into these different projects is very time consuming and I know very inefficient!)

Thanks,

 

[thatmichaelguy]

I started working on an idea of achieving this but didn't get very far and ran out of free time
Hopefully this helps you or the next person who tries to write something though!

Best of luck

Sub project_example()
        
    Dim FindString As String
    Dim Rng As Range
    Dim WS As Worksheet
    
    FindString = InputBox(Prompt:="What is the name of the Project?", _
        Title:="PROJECT NAME", Default:="Project Name here")
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range("A1:Z100")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                WS.Name = FindString
                Set WS = Sheets.Add(After:=Sheets(Worksheets.Count))
                'ran out of time to write anything to select the data,
                'it shouldnt be too hard to do based on the FindString location though
            Else
                MsgBox "Project Not Found."
            End If
        End With
    End If
End Sub