An array with variable value - VBA

Question

In the code below, we can put Sheet1 - Sheet4 in selection mode and copy. But the point here is that the number of Sheets varies. Each time the file is changed, the number of Sheets is low or high. I just want to copy Sheet1 .... n, not all Sheets. (Every Sheet with the name of the "Sheet". How can this code be corrected for this issue?

Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Copy

What is your goal ?? Do you want to copy ALL the sheets in a workbook ??? — Gary's Student
– Gary's Student, Commented Oct 28, 2018 at 16:11
The text of the question was corrected. I hope you understand what I mean. — First Last
– First Last, Commented Oct 28, 2018 at 16:21

DisplayName · Accepted Answer · 2018-10-28 16:58:23Z

3

Sub m()
    Dim nSht As Long
    ReDim shts(1 To Worksheets.Count) As String

    Dim sht As Worksheet
    For Each sht In Worksheets
        If sht.Name Like "Sheet*" Then
            nSht = nSht + 1
            shts(nSht) = sht.Name
        End If
    Next

    If nSht > 0 Then
        ReDim Preserve shts(1 To nSht)
        Worksheets(shts).Copy
    End If

just for the record, here's the first solution spoiling

Dim sht As Worksheet

For Each sht In Worksheets
    If sht.Name Like "Sheet*" Then
        If Not ActiveSheet.Name Like "Sheet*" Then sht.Activate
        sht.Select False
    End If
Next
ThisWorkbook.Windows(1).SelectedSheets.Copy

edited Oct 28, 2018 at 16:58

answered Oct 28, 2018 at 16:27

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

First Last Over a year ago

I tested your code. If you are on sheets other than Sheet (e.g. S), then all the sheets are copied. Please try and report the result.

DisplayName Over a year ago

Actually there's the behavior you reported. It could be managed but it'd spoil the first solution "style". So see edited code for a more conventional solution

DisplayName Over a year ago

you are welcome. you can see edited answer for the first solution amended to cope with the case you noticed

TinMan Over a year ago

@DisplayName nice solution. I forgot about Worksheet.Select False. I miss read the OP's post.

Collectives™ on Stack Overflow

An array with variable value - VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related