VBA Code to loop between sheets

Hi Folks these are some of the codes which might be useful when working with worksheets in a workbooks. Many times we end up adding too many sheets and would think it would be good if there was a way that excel provided more than 3 sheets everytime we create a new workbook. You can change this default property of excel. Go to Start/File—> Options—> Include this many sheets. By default its 3 change it to whatever number you need. Save the settings and next time you create a new workbook, it will have the desired number of sheets.

If wanted to do in with VBA the code to do the same is Application.SheetsInNewWorkbook = 5

VBA code to add a sheet at the end of all active sheets would be as below.

sub macro1()
Sheets.Add after:=Sheets(Sheets.Count)
End sub

This will add a sheet and the 2nd part will count the number of sheets already in workbook and will place it AFTER that count. So the new sheet will be added at the end of all active sheets. And then we give it a name “Details”

To run this code go to Visual Basic Editor (From Excel hit ALT+F11). Create a new module( ALT+I+M) , paste the code and run it by pressing function key 5 (F5).

If you would want all the sheet names in a particular range then the below code should be of much use. Written with less complex stuff and easy to understand.

Sub listsheets()
Dim myrange As Range, st As Worksheet
Set myrange = Application.InputBox(“Select a cell where you want the list populated?”, “Select”, , , , , , 8)
For Each st In ActiveWorkbook.Sheets
ActiveCell.Value = st.Name
ActiveCell.Offset(1, 0).Select
Next st
End Sub

To run this code go to Visual Basic Editor (From Excel hit ALT+F11). Create a new module( ALT+I+M) , paste the code and run it by pressing function key 5 (F5).

What this code does is it lets the user make the selection of range where he wants the list to be generated, the 3rd line in the code does it. Then we select that range what user has provided in the 4th line. And the next four lines is to run a loop to get all sheet names. Activecell.offset(1,0).select is used to move to next cell after entering a sheet name.

More on working with sheets would be creating a index summary with sheet names and hyperlinks on each sheet name, consolidating data from all sheets into one sheet, creating a search box in sheet1 so that when a criteria entered will filter data from all sheets and gets to Sheet1. All such requirements are done using the above logic.

Please add or let me know if you have any comments/suggestions. And do contact me for training on excel or any excel related automation on info@techflames.com


Tags: vba classes in hyderabad, excel, macros, excel hyderabad, msoffice training in hyderabad

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>