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)
Activesheet.name=”Details”
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)
myrange.Select
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

Thanks

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

Protect Excel Sheet Dynamically

Hi All

I have been busy lately in training and automating some excel reporting stuff. Last week I was asked by one of my clients to protect a sheet dynamically. So that already entered information cannot be changed but should allow new rows to be entered. And when closed the newly added row should also be locked when re-open. Its difficult to explain the entire stuff in text here thus I recorded a small tutorial to explain it quickly as I felt it will be useful for many who might need it. Don’t mind the background noise for this one.

Bye.

Shortcut to Highlight Cells

I started on a role to reconcile accounts and it involved manual reconciliation which could not be automated. Thus I use to check 2 sheets with an account number and if amounts match up in the 2 sheets then would have to highlight it with a color. But being keyboard savy it was a pain in excel to use the mouse and choose a color from the toolbar. Rather I wanted a keyboard shortcut which when pressed will highlight any cell and if pressed again will remove the highlight. After learning a little VBA I was able to make my own shortcut. Below is how I did it:

Open the visual basic editor by hitting ALT+F11 from excel. Create a new module (ALT+I+M) and add the below code.

Sub markcolor()
If Selection.Interior.ColorIndex = 6 Then
Selection.Interior.ColorIndex = 0
Else
Selection.Interior.ColorIndex = 6
End If
End Sub

Now switch back to excel and hit ALT+F8 which should bring the macro dialog box with the macro “markcolor” in the list. Just select it and click on “Options” which prompts to enter an alphabet for shortcut key. I enter the alphabet q in the box. And say Run.

That is it now go back to excel and when you hit CTRL+q the cell gets highlighted with yellow color and if pressed again it removes the color.

Note:

1) This shortcut will only work in the excel workbook where you have added the module. For advance users who want to have it available in the entire excel applications irrespective of any file selected, this code should be written in personal.xlsb file.

2) The color code for yellow is 6. If you need any other color then play around by changing this number.

Attaching the file with the code for reference. Happy Learning

 

 

Creating Distribution List with VBA Excel

In the last post in Excel Category I talked about text functions and I posted a situation to create a distribution list from list of email ids in a column. There is no formula in excel which you can use to create a distribution list. But good news is that you can create one using VBA. Below is the code which would give a formula in excel to use for distribution list.

There are many ways to get this function written but below is one of the easiest way to understand the code. Paste the below code in any vba module and use it as a normal formula in excel.

********************

Function listcat(src_range As Range, diff As String) As String
‘src_range is range variable to allow range selection and diff is variable to store ‘differentiators
Dim final As String
‘variable called final to store the distribution list
Dim c As Range
For Each c In src_range
final = final & diff & c.Value
Next c
‘finally taking the clean distribution list to listcat
listcat = Right(final, Len(final) – 1)
End Function

*******************

That is it go back to excel and use this like a formula in excel, for ex: =listcat(B2:B9,”;”) where B2:B9 is the list of email ids which needs to be added and “;” is the separator.

Attached is the file for your reference with the code and formula hope you will find it useful.

Note: These days you can directly copy the entire values in the column and paste in outlook/other mail clients which will automatically create a list. But if in situations it doesn’t this is the best way.

 

 

Remove empty rows from selection using VBA.

Often I come across this situation where I have a few empty cells in between. Like Consider a blank sheet and then you paste some values into it in Column “A”.  Like in A1: January, A2: February, A3,A4&A5 are blanks, then A6: March, A7,A8 are blank, A9: April, A10: May, A11,A12 and A13 are blanks….. and so on…

Imagine this sequence runs down to 80 or 100 rows then its a pain to delete the blank rows in between. A trendy excel user might use the paste special technique of skip blanks and accomplish this. But a more easier way is a single line of code that helps remove all blanks rows. The code goes like this.

sub deleteblanksrows()

Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End sub

Now copy this code and paste it in a module (click ALT+F11 to go to vba editor and from Insert menu click Module-then paste this in the blank white space on right).

Go back to excel and select the selection which contains blank rows say in our case above it would be range A1:A13. Go back to VBA editor by hitting ALT+F11 and then keep the cursor on the 2nd line of the code and hit f5 to run the code.

That is it switch back to excel and you will see all the blank rows are gone.

I work with unformatted  data a lot and I use this code quite often so I have placed this on the ribbon of my excel 2010.

Hope you will also benefit from this VBA snippet.