PDA

View Full Version : Solved: Worksheet Array Selection



CaptRon
10-21-2010, 09:32 AM
I need a little help, please. I have a workbook with 10 worksheets in it. It has the capacity to permit the user to click a button to copy additional specific sheets to the workbook up to 10 additional sheets.

The worksheets 1 - 7, and the last sheet are static, that is they remain in there place and sequence or order. The added sheets are always placed between sheet7 and the last sheet.

I need to be able to select sheets 7 to the last sheet and all sheets between them to copy to a new workbook via VBA. The number of sheets could vary.

Can someone help me with some code on this? I think I need to create a some kind of array (from this sheet:to that sheet) but I don't know how.

Thanks,

Ron

mdmackillop
10-21-2010, 10:28 AM
Hi Ron,
Give this a try
Sub Macro1()
Dim arr()
Dim i, j
ReDim arr(Sheets.Count - 7)
For i = 7 To Sheets.Count
arr(j) = Sheets(i).Name
j = j + 1
Next

Sheets(arr).Copy
End Sub

CaptRon
10-21-2010, 07:32 PM
Hi Ron,
Give this a try
Sub Macro1()
Dim arr()
Dim i, j
ReDim arr(Sheets.Count - 7)
For i = 7 To Sheets.Count
arr(j) = Sheets(i).Name
j = j + 1
Next

Sheets(arr).Copy
End Sub

Worked perfectly! Just what I was needing. Thanks so much.

Ron