VBA Summarize Data into Separate Worksheet

999HelpPlease

New Member
Joined
Jul 16, 2014
Messages
35
I have a worksheet that consists of a complete database and I want to create a summarized worksheet. The summarized worksheet will be based on the State that is selected from a drop down list. Here is a sample of the database. So if in the summarized worksheet I selected the state FL, I would have three rows of information listed one under the other. My database is quite large and this is just a reference. Can you let me know what the programming would be to write this? Thank you for your help.[TABLE="width: 500"]
<tbody>[TR]
[TD]Plan[/TD]
[TD]State[/TD]
[TD]Type[/TD]
[TD]Code[/TD]
[TD]Model[/TD]
[TD]Discount[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Federal[/TD]
[TD]FL[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Federal[/TD]
[TD]AK[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]AK[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Local[/TD]
[TD]FL[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Local[/TD]
[TD]OR[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]91[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]FL[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
This will use a worksheet called Sheets(2) to list the selected state. The location of the dropdown is not specified as a cell or a stand alone combobox so this code will assume cell A1 of sheets(1) is where the dropdown list resides. The selected rows will be listed on sheets(2).
Code:
Sub getState()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets(1) 'Edit Sheet name
Set sh2 = Sheets(2) 'Edit sheet name
sh2.Cells.Clear
sh1.UsedRange.AutoFilter 2, sh1.Range("A1").Value, VisibleDropDown:=False
sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Copy sh2.Range("A2")
End Sub
You can adjust to suit your purposes.
 
Last edited:
Upvote 0
It didn't work when I adjusted it. Here are more details. My database worksheet is called "GroupHealth" my worksheet that I want the summarized details to appear is called "Contracts". The state choice is on "B3" of "Contracts". The information from the database starts on "A2" of GroupHealth and should start to list out on "A14" of Contracts. When I tried to run it and make various adjustments I got various results. If you can help me out with what I am missing that would be great. Thank you for your help.
 
Upvote 0
Assuming you do not want to accumulate the different selections on sheets("Contracts"), the 'Clear' statement would get rid of the previously selected data. See if this modified version works for you.
Code:
Sub getState2()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("GroupHealth") 'Edit Sheet name
Set sh2 = Sheets("Contracts") 'Edit sheet name
sh2.Range("A14", sh2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)).EntireRow.Clear
sh1.UsedRange.AutoFilter 2, sh2.Range("B3").Value, VisibleDropDown:=False
sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub
 
Last edited:
Upvote 0
I tried this and what happens is the header row from GroupHealth copies over to Contracts and in GroupHealth it filters for the state. I don't want GroupHealth to change. I want the filtered information to list out on Contracts. We are getting much closer. Any suggestions? Thanks for your help.
 
Upvote 0
I assumed that row 2 of GroupHealth would be the first row with state symbols as illustrated in the OP. If that is true then it should not be copying the header row onto Contracts because it starts the copy range on row 2. If it is not row 2 then change the cell reference in red font below to designate the beginning row. I added in a line to turn the autofilter off so all your original data will appear on GroupHealth when the macro completes.

Code:
Sub getState2()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("GroupHealth") 'Edit Sheet name
Set sh2 = Sheets("Contracts") 'Edit sheet name
sh2.Range("A14", sh2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)).EntireRow.Clear
sh1.UsedRange.AutoFilter 2, sh2.Range("B3").Value, VisibleDropDown:=False
sh1.Range("[COLOR=#b22222]A2[/COLOR]", sh1.Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
sh1.UsedRange.AutoFilter
End Sub
 
Upvote 0
I ran the macro and the only information that comes over to the Contracts worksheet is the Header Line which isn't a problem. Just no other detail gets listed below it. Its not copying the information from GroupHealth to Contracts.
 
Upvote 0
I cannot duplicate the condition in my test set up. I have two sheets, 'GroupHealth' and 'Contracts'. 'GroupHealth' starts on row one with headers and row 2 is the start of data for states. Column B lists two digit state codes beginning in cell B2. 'Contracts' has 13 rows of header and other information, including cell B3 where the dropdown for the states is located. When i run the macro on my test set up, it copies the filtered state data and posts it to 'Contracts' beginning on row 14, without the header data. Try deleting the code from code module, then copy the code from this thread into your standard code module1 and run it again to see if you get the same results.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,189
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top