VBA Summarize Data into Separate Worksheet

[999HelpPlease]

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.

Plan	State	Type	Code	Model	Discount	Number
Federal	FL	1	1	1	2	3
Federal	AK	2	3	4	6	9
State	AK	5	6	5	5	6
Local	FL	1	2	3	5	6
Local	OR	2	2	8	91	1
State	FL	3	2	5	9	1

<tbody>
</tbody>

 

[JLGWhiz]

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).

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.

 

[999HelpPlease]

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.

 

[JLGWhiz]

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.

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

 

[999HelpPlease]

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.

 

[JLGWhiz]

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.

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

 

[999HelpPlease]

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.

 

[JLGWhiz]

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.

 

[999HelpPlease]

It worked thank you.

 

[JLGWhiz]

It worked thank you.

You're welcome,
Regards, JLG