Exporting to New WB and Adding Sheets

[rrsloman]

Hello all,

I am using Windows XP and Excel 2003.

Here is what I am attempting to do:

I have a report with a dropdown menu. The dropdown menu allows users to select accounts to display financial data. I have an export button. When this button is pressed it opens up a new workbook and pastes a valued out version of the report to it.

I need this process to "loop" so that when other accounts are selected they are saved in the same workbook on a different sheet.

Any idea on how to do this? The code that I have so far is below (is working w/o any loop).

Dim groupname As String
Dim Mywb As Workbook
Windows("Care.xls").Activate
ActiveWorkbook.Sheets("Reports").Select
ActiveSheet.Range("d1").Select
groupname = ActiveCell.Value
ActiveSheet.Range("a1:ao138").Select
Selection.Copy
Set Mywb = Workbooks.Add
Sheets("Sheet1").Name = groupname
ActiveWorkbook.Sheets(groupname).Select
ActiveSheet.Range("a1:ao138").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.Zoom = 75
Application.CutCopyMode = False
ActiveWindow.DisplayGridlines = False
Range("D9").Select