View Full Version : Office 365 - Select Dynamic Sheet Names in Array
JILBO
05-25-2021, 10:59 AM
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
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
05-25-2021, 02:00 PM
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
05-25-2021, 02:18 PM
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
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.
Using many sheets ( >10) is never a good idea nor necessary after rethinking the structure of the project.
JILBO
05-26-2021, 04:35 AM
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
05-26-2021, 04:36 AM
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
05-26-2021, 06:58 AM
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
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
05-26-2021, 08:02 AM
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
05-26-2021, 08:22 AM
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
05-26-2021, 08:31 AM
I'm pretty sure that's what the macro in #11 does, except for saving to SharePoint
Did you try it?
JILBO
05-26-2021, 08:51 AM
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
05-26-2021, 02:14 PM
"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
05-27-2021, 04:25 AM
Thanks Again Paul...Ok that makes sense now!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.