Any tips on how to modify this VBA to select all sheets in a workbook?

[zeke79]

This is some VBA from another thread here that would be very helpful to me if it were able to select all sheets in a workbook. I didnt want to hijack the other thread with this question so I posted a new thread. Any help would be appreciated.

Private Sub test()
Dim fso As Object, FolDir As Object, FileNm As Object, Cnt As Integer
On Error GoTo erfix
Set fso = CreateObject("scripting.filesystemobject")
Set FolDir = fso.GetFolder("D:\Data\FYP")
Application.ScreenUpdating = False
For Each FileNm In FolDir.Files
If FileNm.Name Like "*" & ".xls" & "*" Then
workbooks.Open filename:=FileNm
Cnt = Cnt + 1
ThisWorkbook.Sheets("Sheet1").Range("A" & Cnt).Value = _
workbooks(FileNm.Name).Sheets("sheet1").Range("F" & 7)
workbooks(FileNm.Name).Close SaveChanges:=False
End If
Next FileNm
Application.ScreenUpdating = True
Set FolDir = Nothing
Set fso = Nothing
Exit Sub
erfix:
On Error GoTo 0
MsgBox "Error"
Application.ScreenUpdating = True
Set FolDir = Nothing
Set fso = Nothing
End Sub

 

[Joe4]

If you want to make changes on all the sheets, you will need to loop through them.
See here for details on how to do that: https://www.mrexcel.com/forum/excel-questions/294534-vba-loop-through-worksheets.html#post1445110

 

[zeke79]

Thanks Joe. I will do some ready and experimenting. VBA seems overwhelming just starting out for sure!

 

[zeke79]

*reading*