Office 365 - Select Dynamic Sheet Names in Array

[JILBO]

Hi,

This Site I becoming my Fav!!!

Ok I've a workbook on which I select specific worksheets, however the Sheets Named "Step x" there could be 2 or maybe 50. But if it starts with Step I want to select it!

Can anyone point me towards how select this dynamic range of sheets?

With Sheets(Array("BOM", "Pre Operations - QA", "Step1", "Step2", "Step3", “Step4”, ....,I.e, “Step50”, "Post Operations"))

Thanks in Advance

[SamT]

Dim Shts as Variant
Shts = Array("BOM", "Pre Operations - QA", "Step1", "Step2", "Step2", "Post Operations")
For s = LBound(shts) to UBound(Shts)
Sheets(Shts(s)).Select

Array of Arrays
Dim allShts, Shts1, Shts2, Shts3, Sheeters
Dim Sheeter as Worksheet
Shts1 = Array("BOM", "Pre Operations - QA", "Step1", "Step2", "Step2", "Post Operations")
Shts2 = Array("BOM", "Pre Operations - QA", "Step1", "Step2", "Step2")
Shts3 = Array("BOM", "Pre Operations - QA", "Step1", "Step2")
AllShts = Array(Shts1,Shts2,Shts3)

For s = LBound(allShts) to UBound(allShts)
Sheeters = allShts(s)
For ss = Lbound(Sheeters) to Ubound(Sheeters)
Sheets(Sheeters(ss).Select
Next
Next
The LBound/UBound of Array() Arrays generally depends on the Option Base of the Module

[JILBO]

Hi
many thanks for responding, I just read back my message and it wasn’t clear to be honest !

so will the VBA above select all worksheets starting with the name Step, as well as the others? Sometimes there could be few called times or other times up to 50...

[Paul_Hossler]

Why is it necessary to select all those sheets all at one time?

Why not just do them one at a time?


Easiest way to select the Stepx and some others would be something like this



Option Explicit


Sub SelectBunch()
Dim ary() As String
Dim ws As Worksheet
Dim n As Long

ReDim ary(1 To Worksheets.Count)

n = 0

For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 4) = "Step" Then
n = n + 1
ary(n) = ws.Name
Else
Select Case ws.Name
Case "BOM", "Preoperations"
n = n + 1
ary(n) = ws.Name
End Select
End If
Next

ReDim Preserve ary(1 To n)

Sheets(ary).Select


End Sub

[SamT]

so will the VBA above select all worksheets starting with the name Step
The VBA word "Select" doesn't fit in that sentence.

What do you have and what are you trying to accomplish? Use only words found in VBA.

[snb]

Using many sheets ( >10) is never a good idea nor necessary after rethinking the structure of the project.

[JILBO]

Ok, Thanks for the responses.

Once the workbook is complete I want to copy sheets 'BOM', 'Pre Operations - QA', 'Post Operations' and any sheet beginning with 'Step' into a new workbook.

Appreciating the possibility of 50 sheets is never a good idea, but the workbook is a build manual (images/callouts etc) and having sheets works well especially on a tablet.

[JILBO]

Why is it necessary to select all those sheets all at one time?

Why not just do them one at a time?


Easiest way to select the Stepx and some others would be something like this



Option Explicit


Sub SelectBunch()
Dim ary() As String
Dim ws As Worksheet
Dim n As Long

ReDim ary(1 To Worksheets.Count)

n = 0

For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 4) = "Step" Then
n = n + 1
ary(n) = ws.Name
Else
Select Case ws.Name
Case "BOM", "Preoperations"
n = n + 1
ary(n) = ws.Name
End Select
End If
Next

ReDim Preserve ary(1 To n)

Sheets(ary).Select


End Sub




Thanks Paul, going to look at this now!

[JILBO]

Hi Paul,

Using you code im trying to copy selected sheets into a new workbook/'s (cycles through table)...it selects them but still shows the sht I didn't want

ReDim ary(1 ToWorksheets.Count)

Application.ScreenUpdating= False

n = 0

For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 4) = "Step"Then
n = n + 1
ary(n) = ws.Name
Else
Select Case ws.Name
Case "BOM", "PreOperations-QA", "Post Operations"
n = n + 1
ary(n) = ws.Name
End Select
End If
Next

ReDim Preserve ary(1 To n)

With Sheets(Array(ary))

For Each rw InRange("VRNTBL").ListObject.DataBodyRange.Rows

'.Copy


ActiveWorkbook.SaveAsFilename:="https://RandomSharePoint.com//DevTest/" &rw.Cells(2).Value & "/" & rw.Cells(3).Value &".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

Next rw

End With

Application.ScreenUpdating= True
End Sub

[SamT]

Ok, Thanks for the responses.

Once the workbook is complete I want to copy sheets 'BOM', 'Pre Operations - QA', 'Post Operations' and any sheet beginning with 'Step' into a new workbook.

Appreciating the possibility of 50 sheets is never a good idea, but the workbook is a build manual (images/callouts etc) and having sheets works well especially on a tablet.

As I understand it, you are trying to build a list of sheets to copy... For that, I think a Collection might be the answer. You can use For Each to iterate thru a Collection


Public NewBookSheets As Collection

Private Sub Primary()
With NewBookSheets
.Add Sheets("BOM")
.Add Sheets("Pre Operations - QA")
.Add Sheets("Post Operations")
End With
End Sub

Private SomeOtherSub()
NewBookSheets.Add Sheets("Some Other Sheet")
'Etc
End Sub

Private YetAnotherSub()
NewBookSheets.Add Sheets("YetAnother Sheet")
'Etc
End Sub

Sub Final()
Dim Sht As Worksheet
For Each Sht in NewBookSheets
Sht.Copy NewWorkbook
Next
ClearNewBookSheets
End Sub

Sub ClearNewBookSheets()
For i = NewBookSheets.Count to 1 Step -1
NewBookSheets(i).Delete
Next i
End sub

[Paul_Hossler]

Using you code im trying to copy selected sheets into a new workbook/'s (cycles through table)...it selects them but still shows the sht I didn't want

But is it selected? Shouldn't be

Are you trying to make each WS a separate workbook or just a new workbook with the StepN and other sheets in it




Option Explicit


Dim wb1 As Workbook
Dim n As Long


Sub CopyBunch()
Dim ws As Worksheet

n = 0

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 4) = "Step" Then
Call CopySheet(ws)
Else
Select Case UCase(ws.Name)
Case "BOM", "PREOPS", "POSTOPS" '<<<<<<<<<<<<<<<<<< UPPER CASE
Call CopySheet(ws)
End Select
End If
Next

wb1.Activate


Application.ScreenUpdating = False

MsgBox n & " Worksheets copied"
End Sub


Private Sub CopySheet(ws As Worksheet)
n = n + 1
If n = 1 Then
ws.Copy
Set wb1 = ActiveWorkbook
Else
ws.Copy After:=wb1.Sheets(n - 1)
End If
End Sub

[JILBO]

Yes I'm trying to copy the workbook as a new workbook into SharePoint....maybe rather than looping through Step +1, I should just delete the worksheets I don't want to copy.

[Paul_Hossler]

I'm pretty sure that's what the macro in #11 does, except for saving to SharePoint

Did you try it?

[JILBO]

I've tried but I'm just a beginner so am struggling somewhat!

How do you get I t run Ref Option Explicit?

[Paul_Hossler]

"Option Explicit" at the top of a module just required that all variables be explictly Dim-ed


i.e.


Dim N as long
Dim ws as Worksheet


Some people prefer not to use it, so any variable that is used is (sort of) 'self Dim-ing' as a Variant variable type (probably not accurate description)

I prefer to use it

So if you just copied this into the module ...


ActiveWorkbook.SaveAsFilename:="https://RandomSharePoint.com//DevTest/" &rw.Cells(2).Value & "/" & rw.Cells(3).Value &".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

... the rw would error out until you added


Dim rw As Long

[JILBO]

Thanks Again Paul...Ok that makes sense now!