In this post we’re going to explore how to loop through various objects in Excel using VBA. Using these snippets of code you’ll be able to loop through sheets, open workbooks, files in a folder and named ranges in a workbook.

Loop through all sheets in an active workbook

In this snippet of code we loop through all the sheets in the active workbook. We could use this “shell” code to do something like get all the sheet names in a workbook or perform some action on each sheet etc.

Sub LoopThroughSheets()

	Dim ws As Worksheet

	For Each ws In ActiveWorkbook.Worksheets
		'Type your code here for stuff 
		'you want to do on each sheet. 
	Next ws

End Sub

Loop through all open workbooks

In this snippet of code we loop through all currently open workbooks.

Sub LoopThroughOpenWorkbooks() 
    
	Dim wb As Workbook
	 
	For Each wb In Workbooks
		'Type your code here for stuff 
		'you want to with each workbook. 
	Next wb
     
End Sub

Loop through all files in a given folder

In this snippet of code we loop through all files in a given folder. We could use this to open all xls files in a folder and perform some action in each file then save and close the file.

Sub LoopFilesInFolder()

Dim FilePath As String
Dim FileName As String
Dim ExtensionType As String

'The file path of the folder that contains the
'files you want to loop through.
FilePath = "C:\YOUR FOLDER\"

'You can select the type of files here by
'entering an extension.
'Can be left as an empty string.
ExtensionType = ".xls"

'Target Path with Ending Extention
FileName = Dir(FilePath & "*" & ExtensionType)

'Loop through each file in the selected folder
Do While FileName <> ""
	'Type your code here for stuff 
	'you want to with each workbook. 

	'Get the next file
	FileName = Dir
Loop

End Sub

Loop through all named ranges in the current workbook

In this snippet of code we loop through all named ranges in the current workbook.

Sub LoopThroughNamedRanges()

Dim nm As Name

For Each nm In ThisWorkbook.Names
	'Type your code here for stuff 
	'you want to with each named range. 
Next nm

End Sub

Loop through all comments in a workbook

In this snippet of code we loop through all comments in a workbook.

Sub LoopThroughComments()

Dim WS As Worksheet
Dim Cmnt As Comment

For Each WS In ActiveWorkbook.Worksheets
    For Each Cmnt In WS.Comments
        'Type your code here for stuff 
	'you want to with each named range.
    Next Cmnt
Next WS

End Sub