Copying from only select worksheets within multiple workbooks

[liketohear]

Hi,

I've got some code that is looping through multiple workbooks (with multiple worksheets) to copy data however it's pulling ALL the worksheets within these workbooks and i'd like to limit it the copying to specific ones. Is there a way to list the worksheets that i only want the code to copy from? (similar to SQL where you would use the IN ('XYZ','ABC') code).

Do Until Value = ""
If Value = "." Or Value = ".." Then
Else
If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
On Error Resume Next
Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
If Err.Number > 0 Then
Else
On Error GoTo 0
For Each sht In ActiveWorkbook.Worksheets

'If sht.Range("D37:J52") <> "" Then
Lrow = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
sht.Range("D37:J52").Copy Destination:=WS.Range("A" & Lrow)
'End If

Next sht

End If
Workbooks(Value).Close False
On Error GoTo 0
End If
End If
Value = Dir
Loop

Thanks!

 

[JoeMo]

You can adapt this to limit which sheets your code will act upon;

Sub OnlyActOnCertainSheetsInWb()
Dim CertainSheets As Variant, Sht As Worksheet
CertainSheets = Array("Sheet1", "Sheet2", "Sheet4") 'Change sheet names to suit
For Each Sht In Sheets
    For i = LBound(CertainSheets) To UBound(CertainSheets)
        If Sht.Name = CertainSheets(i) Then
            'do something to this sheet
        End If
    Next i
Next Sht


End Sub

 

[liketohear]

Thank you JoeMo. Worked Perfectly!

 

[JoeMo]

Thank you JoeMo. Worked Perfectly!

You are welcome - thanks for the reply.