Make arrary to export multiple sheets to other workbook Hi, I\'d like to export

Question

Make arrary to export multiple sheets to other workbook Hi, I'd like to export multiple sheets to another workbook. I want to make dynamic list for worksheets name , so users can insert sheet name or delete sheet name to export. I want to have this list of sheet names in column A in 'SelectSheet' sheet. Then, copy these sheet name and paste in the new workbook. For example, Set v = ThisWorkbook.Sheets(Array("Date","Time", Year") I got above, but I want to decide which sheet I want to export. I want to make dynamic list for this Array and export these sheets in Array. Is this possible??

Thanks for help !!

Explanation / Answer

Hi..

Dim wsArray() As Variant

Public Sub defineArray()
    For i = 4 To Range("A" & Rows.Count).End(xlUp).Row
        ReDim Preserve wsArray(i)
        wsArray(i) = Range("A" & i).Value
    Next i
End Sub


Public Sub delWS()

    On Error Resume Next
    For i = 4 To UBound(wsArray())
        Application.DisplayAlerts = False
        Sheets(wsArray(i)).Delete
        Application.DisplayAlerts = True
    Next i
    On Error GoTo 0

End Sub


Public Sub moveWS()

    On Error Resume Next
    For i = 4 To UBound(wsArray())
        Workbooks("Book1").Activate
        Workbooks("Book1").Sheets(wsArray(i)).Move after:=Workbooks("Book.xlsx").Sheets(1)
    Next i
    On Error GoTo 0

End Sub

or

try this

assuming that data is in range A1:A10 or A1:K1 or A1:C5,....

try this sample

Sub Create_Array_01()

'Dec 17, 2014

Dim rng As Range

Set rng = [A1:A10]

Dim x As Long, i As Long
x = WorksheetFunction.CountA(rng)

If x = 0 Then MsgBox "empty cells": Exit Sub

Dim v As Variant

ReDim v(1 To x)
i = 1
For Each r In rng.SpecialCells(xlCellTypeConstants) '<< non empty cells
v(i) = r.Value
i = i + 1
Next
MsgBox Join(v, ",")
End Sub

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.