Loop sheets using Array

[rajrajsha]

Team, I am trying to loop through an array of specific worksheets with in workbook which are in folder. sheet names may vary as per the request i have. I want the code with dynamic sheet names.
Ex: if i want to loop through only 2 sheet.. let say i want to loop Sheets "Data" and "Matter", code should pull only Data sheet and Matter sheet from all workbooks from a folder to a master file one by one. Next time i want to pull 3 sheets data as per my request and the Sheet names will vary according to my request. So i want to make sheet names list dynamic.
Below is my code which i found..
Thanks in advance and im sorry i haven't explained it as needed.

Sub Getdata()
Dim wName As Variant, vName As Variant
Dim Wbk As Workbook 'Set workbook variable don't use ActiveWorkbook
Dim ws As Worksheet

'get folder path
'loop through all xl files in folder

wName = Array("Sheet1", "Sheet3", "Sheet5")
    Set Wbk = ThisWorkbook
    For Each vName In wName
        Set ws = Nothing
        On Error Resume Next
        Set ws = Wbk.Worksheets(vName)
        On Error GoTo 0
        If Not (ws Is Nothing) Then
            With ws
    'my work goes here
            End With
    End If: Next

End Sub

 

[dmt32]

Hi,

Rather than hard code the sheet names in your code, you can pass them as an argument

Try this update to your code

Sub Getdata(ParamArray wName() As Variant)
    Dim Wbk         As Workbook
    Dim ws          As Worksheet
    Dim i            As Integer
  
    Set Wbk = ThisWorkbook
  
    On Error Resume Next
    For i = 0 To UBound(wName)
        Set ws = Wbk.Worksheets(wName(i))
        If Not ws Is Nothing Then
            With ws
              
                'my work goes here
            End With
          
        End If
        Set ws = Nothing
    Next
    On Error GoTo 0
End Sub

You call your code by specifying which sheets you want to process

examples

Getdata "Data", "Matter"

Getdata "Data", "Matter", "AnotherSheet"

you just add sheet names as required

Dave

 

[rajrajsha]

Hi dmt32, Thanks for the immediate reply, if in case i have only one sheet to pull. then that will show error. in that case how approach. As per you code i need to edit the code and update the sheet names when ever required. Am i correct ...

 

[dmt32]

Hi dmt32, Thanks for the immediate reply, if in case i have only one sheet to pull. then that will show error. in that case how approach. As per you code i need to edit the code and update the sheet names when ever required. Am i correct ...

No edit should be needed, you just pass the sheet name required

example

 Getdata "Data"

Dave

 

[rajrajsha]

Hi dmt32, your code is working fine, but if the sheet what im searching is not there in source workbook then it is moving out of loop. Can you please modify the code, to first check the work sheet is present in the source workbook then Proceed next, else it should give comment "Sheet not found".

Thanks a lot

 

[dmt32]

The code structure follows largely the code published & the on error resume next statement I took you included for very scenario you describe

You can include Msgbox prompt if required - see if update does what you want.

Sub Getdata(ParamArray wName() As Variant)
    Dim Wbk         As Workbook
    Dim ws          As Worksheet
    Dim i           As Integer
    
    If IsMissing(wName) Then Exit Sub
    
    Set Wbk = ThisWorkbook
    
    On Error Resume Next
    For i = 0 To UBound(wName)
        Set ws = Wbk.Worksheets(wName(i))
        If Not ws Is Nothing Then
            With ws
                MsgBox ws.Name
                'my work goes here
            End With
            
         Else
            MsgBox wName(i) & Chr(10) & "Sheet Not Found", 48, "Not Found"
            'optional - exit sub if required or delete line if want to continue
            Exit Sub
        End If
        Set ws = Nothing
    Next
    On Error GoTo 0
End Sub

 

[rajrajsha]

dmt32, thank you soo much, My apologies for giving you trouble in this. That was the code i needed. I marked it as the best solution. Thanks a lot. have a nice day.

 

[dmt32]

welcome glad resolved

Appreciate feedback

Dave