Sheets(Array).Copy Dynamically

**jonn** · 09-14-2008, 07:06 AM

Hi all,

i've hit a bit of a mental wall (been programming too many hours striaght) and cannot figure out how to do the following.

I have a sheet array sh_array(3)
of which all the slots may not contain data, but i want to turn it into the statement

Sheets(Array("sheet1", "sheet2", etc, etc).Copy

however as all the items in the sh_array may not be occupied i cannot simply do this

Sheets(sh_array(0), sh_array(1), sh_array(2), sh_array(3)).Copy

as it hits the subscription out of range if one of the items is a null.

so say only sh_array() slots 0 and 1 contain data

how do i dynamically turn it into the statement

Sheets(sh_array(0), sh_array(1)).Copy

as so forth if more/less elements of the sh_array are full.

I thought about a if statement testing the sh_array length but there must be a better way to do it.

Thanks

Jon

**Andy Pope** · 09-14-2008, 07:08 AM

Why not create a new temporary array with only valid sheet names?

**jonn** · 09-14-2008, 07:12 AM

i could but as i want the sheets selected and copied together how would i produce the statement even if i knew the exact array size. this is the dynamic statement i'm after:

Sheets(New_Array(0), ..... , to New_Array(i)).Copy

without running a loop statement? (which would only copy the sheets individually anyway.

for i = 0 to New_Array.Length

Sheets(New_Array(i)).copy

Next i

**Andy Pope** · 09-14-2008, 07:20 AM

Something like this. which simply selects sheet1 and sheet3 in the active workbook.

Sub x()

    Dim sh_array(2)
    Dim shtTemp As Object
    Dim sh_NewArray()
    
    sh_array(0) = "Sheet1"
    sh_array(1) = ""
    sh_array(2) = "Sheet3"

    sh_NewArray = CompressArray(sh_array)
    
    Sheets(sh_NewArray).Select
    For Each shtTemp In ActiveWindow.SelectedSheets
        MsgBox shtTemp.Name
    Next
    
End Sub


Function CompressArray(Old()) As Variant()
    Dim lngIndex As Long
    Dim lngNitems As Long
    Dim vntTemp()
    
    For lngIndex = LBound(Old) To UBound(Old)
        If Len(Old(lngIndex)) > 0 Then
            ReDim Preserve vntTemp(lngNitems)
            vntTemp(lngNitems) = Old(lngIndex)
            lngNitems = lngNitems + 1
        End If
    Next
    CompressArray = vntTemp
    
End Function