How do I set up a master sheet in a workbook that compiles data from the other sheets?

I have a workbook with multiple sheets that track inputted data and what their status is (i.e. Open or Closed). I would like to make a master sheet that would import data from each of these sheets automatically. In particular, I want the sheet to only import and display the "open" files. I also only need to apply this to 5 of the worksheets in my workbook.

I've attached a modified version of my workbook so that you can see what I'm trying to accomplish. Ultimately, the master sheet would look identical to my other sheets just without the closed or canceled items.

Thanks for helping me with my problem.

Please Login or Register  to view this content.

you can also just define your data in the other sheets and type in the defined name on your master sheet. highlight the rows or columns you need data from then right click and hit define name type what you want to call it. when on master sheet just type whatever you called it and the data will be there. its easier than doing columns or rows formulas because it can be both or just one, really whatever you need.

This code is great and is almost a perfect fit for my application. I'm played with it and modified it slightly. There are two things that I would like it to do for me and cant quiet figure it out. The first is I would like it to import the data in ascending order, basically start with 2012 at the top and go to the newest additions at the bottom. The other thing is when the "open" is changed to "closed" on the other sheets it removes that line from the master. Are there a couple of lines that I can add to make this work?

Originally Posted by rcm

Please Login or Register  to view this content.

This code is great and is almost a perfect fit for my application. I'm played with it and modified it slightly. There are two things that I would like it to do for me and cant quiet figure it out. The first is I would like it to import the data in ascending order, basically start with 2012 at the top and go to the newest additions at the bottom. The other thing is when the "open" is changed to "closed" on the other sheets it removes that line from the master. Are there a couple of lines that I can add to make this work?

Originally Posted by jonnyboy26

you can also just define your data in the other sheets and type in the defined name on your master sheet. highlight the rows or columns you need data from then right click and hit define name type what you want to call it. when on master sheet just type whatever you called it and the data will be there. its easier than doing columns or rows formulas because it can be both or just one, really whatever you need.

I had started to go through this process but it was becoming quiet tedious simply due to the size of my actual workbook and number of definitions I was having to put in. Thanks for the response.

Originally Posted by newdoverman

Welcome to the forum.

Here is a similar question and I supplied a formula solution. In this case, 6 worksheets were combined into a master and any additions to the sub sheets would automatically update the master worksheet.
http://www.excelforum.com/excel-gene...ml#post4299858

Take a look at the workbook uploaded and you should be able to adapt it to suit your data.

Thanks for the reply. I went to the original post and copied the formulas that you suggested. I'm trying those in my workbook to see which works better for me, this or the VBA.

could you send the modified version?

Originally Posted by rcm

could you send the modified version?

The only thing pertinent in the modification at this point is the rows. I changed from 2 to 3 to account for my header when I put it on the new sheet.

Sub openjobs()

Set mst = Sheets("CAF Open Master")

r3 = mst.Range("A" & Rows.Count).End(xlUp).Row + 1
mst.Range("3:" & r3).ClearContents
r3 = 3

For s = 2 To Sheets.Count
Set Shin = Sheets(s)
rmax = Shin.Range("A" & Rows.Count).End(xlUp).Row
For r = 3 To rmax
If Shin.Cells(r, 10) = "Open" Then
Shin.Select
Rows(r & ":" & r).Select
Selection.Copy
mst.Select
Rows(r3 & ":" & r3).Select

ActiveSheet.Paste
r3 = r3 + 1
End If

Next r
Next s

mst.Select
Cells.Select
Cells.EntireColumn.AutoFit
End Sub

Forgive me for being a complete VBA novice. And Forgive me if I'm asking for to much here. And I promise I have actually attempted to modify and add to the code you wrote to try and achieve what I'm about to ask for.
First, How can I get this to only scan certain sheets? I have other sheets in this workbook that no longer have any "open" listings but it still scans them.
Second, I can't get the sheet to sort ascending because of something having to do with merged cells and I have done everything it has asked me from making them the same size to unmerging. The only way at this point to get it to fill the data in the order I want is to rearrange my sheets.
Third, I am putting in a total cell at the bottom and I want it to continue to move up or down as jobs are added or closed and removed.
Lastly, can I add a button of some kind on the master sheet so that I can run my macro so that it up dates with out having to run it in ALT F11?

Again, I appreciate your assistance.

1. Only scan certain sheets

One way is to rename those sheets (the macro could do it after the condition is met) and modify the macro to only scan the sheets that meet a certain pattern in the names.

2.Arrange sheets in ascending order by date.

How about sorting the jobs after they are inserted in the master file

3. Total cell at the bottom.

The most efficient way is to have the total row at the beginning. The moving total can be done too.

4. Add a button

Considered it done

Originally Posted by rcm

1. Only scan certain sheets

One way is to rename those sheets (the macro could do it after the condition is met) and modify the macro to only scan the sheets that meet a certain pattern in the names.

2.Arrange sheets in ascending order by date.

How about sorting the jobs after they are inserted in the master file

3. Total cell at the bottom.

The most efficient way is to have the total row at the beginning. The moving total can be done too.

4. Add a button

Considered it done

That is awesome, I look forward to seeing the new code and implementing it. Thanks in advance!

try this version, most of the requirement are there..

I supressed the anoying screen updating in this version

this version sorry

Originally Posted by rcm

this version sorry

That is nice. The button makes it really nice to run and suppressing the update annoyingness is brilliant. So I tried adding an "Open" line in 2016 but it didn't pick it up when I ran it. Is that because the code reads no opens?

Originally Posted by asupharris

That is nice. The button makes it really nice to run and suppressing the update annoyingness is brilliant. So I tried adding an "Open" line in 2016 but it didn't pick it up when I ran it. Is that because the code reads no opens?

Something else that I noticed, when I apply my header to the master, if it is below the button it gets erased and if I put it above the button the code errors out saying it cant do that to a merged cell.

So how about not bother with the exclution. Have the macro check all of them anyway. Otherwise, it would require to write another macro to monitor the event of inputting and open line.

Yeah, I can live with that. It doesn't take that much longer to check them. In the actual workbook the years go all the way back to 2003 and there are no open lines until 2012 but 2016 and future years will be filling up with Open lines.

Got a compile error, Next without for. Not sure what that means.

My mistake, I commented (by adding a ' to the line) the wrong END IF .

Unfortunately it was still doing the same thing. So I played with it a little and now it seems to work. So what I did, (shot in the dark going by your comment), I removed the ' on the END IF above Next r and added it to the END IF above Next s. So it looks like this.
End If

Next r

' If opn = 0 Then Sheets(s).Name = Left(Sheets(s).Name, 8) & "noopens"
' End If
Next s

Hopefully that doesn't mess up anything. I was able to add a line on 2016, hit the button on the master and it added it, I then went back and closed it and it took it away. I'm going to try moving the button up to the A1 position and adding my header to rows 2 and 3 and see if it works or if it erases it.

Unfortunately it was still doing the same thing. So I played with it a little and now it seems to work. So what I did, (shot in the dark going by your comment), I removed the ' on the END IF above Next r and added it to the END IF above Next s. So it looks like this.
End If

Next r

' If opn = 0 Then Sheets(s).Name = Left(Sheets(s).Name, 8) & "noopens"
' End If
Next s

Hopefully that doesn't mess up anything. I was able to add a line on 2016, hit the button on the master and it added it, I then went back and closed it and it took it away. I'm going to try moving the button up to the A1 position and adding my header to rows 2 and 3 and see if it works or if it erases it.

I went through the motions and this version seems to work.

Remember it is a brute force macro.
It clears the MASTER and then sweeps all tabs and all lines looking for OPENS

Ok, I'm at a loss as to what I am doing wrong. I tried applying the script to the real workbook and it doesn't want to work. Here is the workbook that I am trying to apply it to.

Sorry for the late reply, got sidelined on a couple of other projects. Looks awesome, so is there a way to remove the closed items from the master sheet and add the button back in?

Originally Posted by newdoverman

If you are referring to the workbook that I uploaded, you can select all the data including headers then on the Data tab click on the filter button. That will add filters to the columns. click on the filter button on the Current Status column and uncheck "Closed".

Very cool, I applied that and it looks about like I want. I tried playing with it and seeing if adding new open ones on the 2016 sheet would update on the master and it didn't. Is there something that I need to apply to it to have it do that?

Sweet, That is great. Thanks for the help. Now I need to go through all of the formula and learn what you did so that I can apply it in the future to other documents.

Thanks, I will do that. Thanks again to everyone who helped me out on this. Everything that came through was very beneficial!