This group has been and will continue to be an excellent resource for new VBA
users such as me. I am this close to finalizing my VBA code and am having a
little difficulty. I suspect that using an array will be the answer, but I
am not at all comfortable with arrays in VBA or the main Excel interface.

I am using a listbox to allow the user to choose one or more regions of data
to add to a particular sheet. The following code accomplishes everything I
need, except for when I need to examine the second, third, etc. choices of a
multiple selection.

I need regionName2 to correspond with the second, third, etc. choices
mentioned above, calling CopyMoreDataToCAPSData regionName2. Right now, the
remaining items only call CopyDataToCAPSData regionName, which replaces the
data in CAPSData. Where am I going wrong???

-------------------------------------------------
Private Sub CommandButton2_Click()

Dim regionName As String
Dim regionName2 As String

For i = 0 To Change_Region.ListBox1.ListCount - 1
If Change_Region.ListBox1.Selected(i) = True Then
tempnum = WorksheetFunction.Match(Change_Region.ListBox1.Lis t(i),
Range("Regions"), 0)
regionId = Range("RegionID").Item(tempnum)
If (Change_Region.ListBox1.Selected(i) = True) 1 Then
regionName = Change_Region.ListBox1.List(i)
MsgBox regionName
CopyDataToCAPSData regionName
regionName2 = Change_Region.ListBox1.List(i + 1)
MsgBox regionName2
CopyMoreDataToCAPSData regionName2
ElseIf (Change_Region.ListBox1.Selected(i) = True) = 0 Then Exit Sub
Else
regionName = Change_Region.ListBox1.List(i)
MsgBox regionName
CopyDataToCAPSData regionName
End If
Change_Region.Hide
End If
Next i
End Sub
-------------------------------------------------
Sub CopyDataToCAPSData(sheetName As String)
Application.ScreenUpdating = False
Sheets("CAPSDATA").Activate
Range(Range("A2"), ActiveCell.SpecialCells(xlCellTypeLastCell)).Clear
Sheets(sheetName).Select
Range("A2").Activate
Range(Range("A2"), ActiveCell.SpecialCells(xlCellTypeLastCell)).Copy
Worksheets("CAPSDATA").Range("A2")
Sheets("CAPSDATA").Select
Range("A1").Activate
Application.ScreenUpdating = True
End Sub
-------------------------------------------------
Sub CopyMoreDataToCAPSData(sheetName As String)
Application.ScreenUpdating = False
Sheets(sheetName).Select
Range("A2").Activate
Range(Range("A2"), ActiveCell.SpecialCells(xlCellTypeLastCell)).Copy
Sheets("CAPSDATA").Activate
Sheets("CAPSDATA").Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.Paste
Sheets("CAPSDATA").Activate
Sheets("CAPSDATA").Range("A1").Select
Application.ScreenUpdating = True
End Sub
-------------------------------------------------