Create new workbooks from each and every worksheet in your workbook | Excel VBA

In an earlier post I explained how to create a workbook from a particular sheet in Excel VBA. Today I will teach you how to create new workbooks from each and every worksheet of your workbook and how to save them to desired location. So here is the workbook I have.

Name of this workbook is “Original file.xlsm”. As you can see this workbook has three worksheets. So I’m going to create 3 separate workbooks from them and save them in folder where this “Original file.xlsm” is in. This is how the folder look like before run the macro. It has only one file.

So below is the full code to separate all sheet to different workbooks and save them.

Sub CreateWorkbooks() Dim WB As Workbook Dim WS As Worksheet For Each WS In Worksheets    WS.Copy    Set WB = ActiveWorkbook    WB.SaveAs ThisWorkbook.Path & "\" & WS.Name, FileFormat:=52    WB.Close Next WS End Sub

Here is the result after running above macro. As you can see three files have created with original sheet names.

Next I will explain about this code in detail. First we define our variables

Dim WB As Workbook Dim WS As Worksheet

Next we need to loop through all the sheets of our workbook.

For Each WS In Worksheets Next WS

As you can see there are few other lines between above two lines. So those commands will be executed for each and every worksheet. Following commands will create a new workbook from each sheet.

WS.Copy Set WB = ActiveWorkbook

Now let's take a closer look at the following line.

WB.SaveAs ThisWorkbook.Path & "\" & WS.Name, FileFormat:=52

What this line does is, it save each workbook in .xlsx format in the folder where we have our original file. ThisWorkbook.Path gives the location of the folder. And WS.Name set the name of the workbook. So here each file is saved with it’s original sheet name. And if you want to save the files in a different location you can replace the ThisWorkbook.Path with desired folder path. Here is an example.

WB.SaveAs "C:\Users\EVS\Documents\" & WS.Name, FileFormat:=52

Also FileFormat number specifies the format when saving the file. Here is the list of excel file formats.

XlFileFormat Enumeration (Excel)

And following line is used to close each newly created workbook.

WB.Close

How to Create a New Workbook from Existing Excel Sheet in Excel VBA

In this post I will share a quick tip with you. Sometimes we need to create a new workbook from existing worksheet in Excel VBA. This existing worksheet can be active worksheet or any other worksheet. Assume we have a workbook like this.

This workbook contains 3 sheets. Think we need to create a new workbook from “Sheet3”. We can do that using following code.

Sub CreateNewWorkbook() Dim WB As Workbook Dim WS As Worksheet Set WS = Worksheets("Sheet3") WS.Copy Set WB = ActiveWorkbook End Sub

If you run above macro you will notice a new workbook created from Sheet3 like this.

Now we can see two workbooks in taskbar. Our original workbook and the newly created one.

And if you want to create a new workbook from active sheet you can use below code.

Sub CreateNewWorkbookFromActiveSheet() Dim WB As Workbook Dim WS As Worksheet Set WS = ActiveSheet WS.Copy Set WB = ActiveWorkbook End Sub

If you want to learn how to create workbooks from each and every sheet of the workbook and save them to a desired folder, then check this post.

Create new workbooks from each and every worksheet in your workbook

How to Sum Values in Excel

There are many ways to sum values in Excel. If you want you can directly enter the values you want to sum like this.

Also you can select a cell and then type in the formula bar too. Because if the cell is small then you can’t see all the values you entered to the cell. But formula bar has lot more space. So you don’t need to resize cells when enter long formulas.

And if you want you can enter the cell addresses of the values you want to sum instead of directly entering the values. Using this method you can change the values in the cells and get the sum instantly without doing any change to the formula.

Actually you don’t need to type the cell addresses manually. You can select the cells using mouse while pressing + key from the keyboard.

Also you can use inbuilt function in Excel to sum values in ranges. If this is a continuous range you can use something like this.

Here again, you don’t need to type the ranges manually. You can first type =Sum(
Then you can select the range using mouse. So the range will be inserted automatically to the formula. Finally you can close the bracket and press enter.

And if it is not a continuous range you can use a formula like this.

Paste Clipboard Content to Excel Sheet Using VBA

Have you ever needed to paste clipboard content to an excel sheet. Content in the clipboard may have copied from a web page, software, word file or text file etc. And content may be in any form.  It may be a text, table, image and so forth. So in this post I will teach you how to do this using VBA.

Method 1

In this method, first you need to add reference to Microsoft Forms 2.0 Object Library. To do that go to VBA editor and click Tools menu and then click on References.

Then put a tick to Microsoft Forms 2.0 Object Library. However you will notice that Excel application automatically add reference to that library if you add form to your project.

Then add this code to a module. In this code, clipboard content is assigned to the SText variable. So at the end you can paste it to the place where you need. In this example content is pasted to the B2 cell of the activesheet.

Sub PasteToExcelFromClipboard() Dim DataObj As MSForms.DataObject Set DataObj = New MSForms.DataObject DataObj.GetFromClipboard SText = DataObj.GetText(1) ActiveSheet.Range("B2").Value = SText End Sub

However this method will not work if you have an image in the clipboard. So if you want to deal with images then you can use this second method.

Method 2

Actually in this method we are using a very simple theory. We know that Ctrl + v is the shortcut keys to paste anything to the Excel sheet. So instead of doing this manually we can give that command through VBA like this.

Sub PasteToExcelFromClipboard_SendKeyMethod() activesheet. Range("B2").Select SendKeys "^v" End Sub

First we select the B2 cell of the active sheet. Then we give the paste command using SendKeys method.  Using this method you can even paste images, shapes and tables from the clipboard too.

How to Set the Location, Width and Height of an Inserted Image in Excel VBA

In this post I will explain how to set location, height and width of an inserted image. So this is the image I’m going to insert to the Excel sheet.

And this is the “Details” tab of the “Properties” window of that image. As you can see height and width of the image are 1000 and 1500 pixels respectively.

So if we need we can easily insert the image using following code.

Sub InsertPicture_Example1() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath) End Sub

Then picture will be inserted at active cell like this.

But sometimes we need to insert the image to a specific location of the sheet. And we may need to change the width and height to suit with the available space in the Excel sheet. So we can use following properties to change the location and size of the image to suit with our requirements.

LockAspectRatio
Height
Width
Top
Left

LockAspectRatio Controls the width: height ratio of the inserted image.  So if it is true, width: height ratio of the inserted image will be equals to the width: height ratio of the original image. If it is false, ratio of the inserted image will be different.  Height and Width defines the height and width of the inserted image respectively.  Top define vertical location of the top left corner of the image. And Left defines horizontal location of the top left corner of the image. We can give the location using row numbers and column numbers.

So following code will insert the image to D2 cell.  And height will be 200 pixels.  As LockAspectRatio set to true, excel will automatically calculate the width to comply with original image.

Sub InsertPicture_Example2() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)     'Set the location, width and height With myPicture     .ShapeRange.LockAspectRatio = msoTrue     .Height = 200     .Top = Rows(2).Top     .Left = Columns(4).Left End With End Sub

Image will be inserted like this

And here is our next example.  In this example, image will be inserted to D2 cell of the active sheet.  But width: height ratio will not equal to the ratio of the original image because we have set LockAspectRatio to false. And we have given specific height and width.

Sub InsertPicture_Example3() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)     'Set the location, width and height With myPicture     .ShapeRange.LockAspectRatio = msoFalse     .Height = 200     .Width = 450     .Top = Rows(2).Top     .Left = Columns(4).Left End With End Sub

So the image will be inserted like this

Here is another example.  Actually there's a mistake in this code. But I’m putting it here to show you how Excel application works if we use properties incorrectly. In this code we have set LockAspectRatio to true but after that we have given both height and width values.

Sub InsertPicture_Example4() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)     'Set the location, width and height With myPicture     .ShapeRange.LockAspectRatio = msoTrue     .Height = 200     .Width = 450     .Top = Rows(2).Top     .Left = Columns(4).Left End With End Sub

So when the code is executed image size will be altered keeping the original width: height ratio. You will see how it works if you use debug -> step into method.

At the end image will be inserted with width of 450 pixels. And height will be altered to comply with original ratio. So the image will inserted like this.

In above examples we gave height and width in pixels.  But sometimes we need to assign the width and height in centimeters. We can use Application.CentimetersToPoints to do that. So the following code will insert the image to D2 cell of the active sheet. Height of the image will be 5cm. And width of the image will be 7.1cm

Sub InsertPicture_Example5() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)     'Set the location, width and height With myPicture     .ShapeRange.LockAspectRatio = msoFalse     .Height = Application.CentimetersToPoints(5)     .Width = Application.CentimetersToPoints(7.1)     .Top = Rows(2).Top     .Left = Columns(4).Left End With End Sub

So if you print the Excel sheet you will notice that image will printed in given size.

How to Pause for Specific Amount of Time in Excel VBA

In this post I will teach you how to pause or delay macro for specific amount of time.  This will be very useful specially when you developing web scraping applications. So I will explain you how to use this technique using a sample web scraping application.

So when you click the button in above application, program will update the share price of each and every company and then it will wait 10 minutes before refresh the share prices again. So the process will continue until the user save the file and close it. However if you need you can put a stop button to stop the process as well. Now let’s see how to do this pausing/waiting part.  So this code should be added just before you go to the next loop. So the structure of the whole code should look like this

Loop start    Code to extract data from web    Code to pause the program  Loop end

So now I will explain you how to do this pausing part step by step. In this example I’m going to pause the program for 10 minutes.

First we need two variables to Store time.

Dim CurrentTime As Date Dim EndingTime As Date

Then assign current time to the first variable.

CurrentTime = Now

Then we assign time we want to resume the program to the second variable.

EndingTime = Now + TimeValue("00:10:00")

Here you can change the time to control the gap between two loops. Format is hh:mm:ss
in this example I have set 10 minutes delay between two loops. Next you can use do until loop to pause the program until the time we need to resume it.

Do Until CurrentTime >= EndingTime     Loop

Inside that Do Until Loop you need to use following line to give the control to the user and for other programs.

DoEvents

And also within the loop you need to update first variable to current time. Then only program can track whether current time is similar or higher than resume time.

CurrentTime = Now()

So here is the full code to pause the program for 10 minutes

'------------------------------- 'Wait for 10 minutes '------------------------------- Dim CurrentTime As Date Dim EndingTime As Date CurrentTime = Now EndingTime = Now + TimeValue("00:10:00") Do Until CurrentTime >= EndingTime     DoEvents     CurrentTime = Now() Loop

There are various methods to pause VBA programs. But there is an advantage of using this method.  Because in this method during the delay time user gets the control to do any changes to the excel file. Because in some other methods user can't do any work in excel while it is waiting.  This method specially useful for web scraping applications. Because user can analyse the results during delay time.  But if you don't want to give control to the user while program is paused then you can use techniques described in this post. They are quite straightforward.

Sleep and Wait Functions

Want to know how to extract data from websites automatically? Read this.

Web Scraping

An Excel VBA Macro to Save a Workbook Automatically When Closing

Whenever you try to close an Excel file, Excel application ask you whether you want to save the changes or not.

But for some applications we need to automatically save the changes when we close the file without showing above message. So in this post I will show you how to do that using VBA. First open an Excel file. If it is a .xlsx file then save it as .xlsm file. If this is a new excel workbook, first you need to save it manually as .xlsm file. Then go to the developer tab and click on the Visual Basic icon.

If you don't have developer tab this post will explain you how to show it.

How to show the Developer tab

Also you can use shortcut keys to open the VBA editor. Press Alt+F11

Then double click on ThisWorkbook module.

You will see two drop downs above the coding area. They have default values as (General) and (Declarations).

From the first drop down select “Workbook”. When you select that value second dropdown will be populated with new list. Then select “BeforeClose” from that second dropdown. New subroutine will be created like this.

Private Sub Workbook_BeforeClose(Cancel As Boolean) End Sub

Add this code between private sub and end sub

ActiveWorkbook.Save

so the final code should look like this.

Private Sub Workbook_BeforeClose(Cancel As Boolean) ActiveWorkbook.Save End Sub

Now do some changes to the excel file and close it. Excel application will silently close the file without showing any message. Next time when you open the file you will notice that the changes are saved.

Create and Format a Table

Advance VBA applications consist of various functions. Report generating is one of them.  Because lots of applications need to generate various kinds of reports for the user.  These reports contains data in various formats such as tables, charts etc. So when we create reports we often need to create tables dynamically. Because in developing Stages we don't know where the table will start and end because they often depend on the data entered by the user. So in this post I will explain you how to develop a subroutine to create and format a table.  So then you can call from anywhere in your main program. We are creating this table on a excel sheet.  I will explain the steps one by one.

This subroutine need 5 parameters.  We need to input sheet name, start column, end column, starting row and end row.

Sub CreateAndFormatTable(WS As Worksheet, StartCol As String, EndCol As String, StartRow As Long, EndRow As Long)

Assume that you call subroutine as follows in main program.

Dim WS_2 As Worksheet Set WS_2 = Worksheets("Sheet2") Call CreateAndFormatTable(WS_2, "B", "F", 3, 17)

So it will create a table in range B3:F17.
We can use With WS to make it easier to write and read the code.

With WS End with

First we need to select the range.

.Range(StartCol & StartRow & ":" & EndCol & EndRow).Select

Next we need to add borders.

'--------------------------------------- 'Add borders '--------------------------------------- Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft)     .LineStyle = xlContinuous     .ColorIndex = 0     .TintAndShade = 0     .Weight = xlThin End With With Selection.Borders(xlEdgeTop)     .LineStyle = xlContinuous     .ColorIndex = 0     .TintAndShade = 0     .Weight = xlThin End With With Selection.Borders(xlEdgeBottom)     .LineStyle = xlContinuous     .ColorIndex = 0     .TintAndShade = 0     .Weight = xlThin End With With Selection.Borders(xlEdgeRight)     .LineStyle = xlContinuous     .ColorIndex = 0     .TintAndShade = 0     .Weight = xlThin End With With Selection.Borders(xlInsideVertical)     .LineStyle = xlContinuous     .ColorIndex = 0     .TintAndShade = 0     .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal)     .LineStyle = xlContinuous     .ColorIndex = 0     .TintAndShade = 0     .Weight = xlThin End With

 

Then colour the header row

'--------------------------------------- 'Background Color of headers '--------------------------------------- .Range(StartCol & StartRow & ":" & EndCol & StartRow).Select With Selection.Interior     .Pattern = xlSolid     .PatternColorIndex = xlAutomatic     .Color = 12611584     .TintAndShade = 0     .PatternTintAndShade = 0 End With

 

You can change the colours in above code to suit with your requirements. Now we can color rest of the area. I will color every other row in light blue color. But you can change it to color you prefer.

'----------------------------------------- 'Background color of other area '----------------------------------------- For i = StartRow + 2 To EndRow Step 2     .Range(StartCol & i & ":" & EndCol & i).Select     With Selection.Interior         .Pattern = xlSolid         .PatternColorIndex = xlAutomatic         .ThemeColor = xlThemeColorAccent1         .TintAndShade = 0.799981688894314         .PatternTintAndShade = 0     End With Next i

Next we need to set the font name and font size. I will choose “Arial” and size 11.

'----------------------------------------- 'All table font name and size '----------------------------------------- .Range(StartCol & StartRow & ":" & EndCol & EndRow).Select With Selection.Font     .Name = "Arial"     .Size = 11     .Strikethrough = False     .Superscript = False     .Subscript = False     .OutlineFont = False     .Shadow = False     .Underline = xlUnderlineStyleNone     .ThemeColor = xlThemeColorLight1     .TintAndShade = 0     .ThemeFont = xlThemeFontNone End With

So if you have data in the range, it will look like this now.

Now we can set the header font color and also I will make the font bold. I will choose the white color for header fonts.

'--------------------------------------------- 'Set header font color and make the font bold '--------------------------------------------- .Range(StartCol & StartRow & ":" & EndCol & StartRow).Select With Selection.Font     .ThemeColor = xlThemeColorDark1     .TintAndShade = 0 End With Selection.Font.Bold = True

You will get following result if you have data in the range.

So now our subroutine is complete. We can call the subroutine whenever we need.  To make your life easier I will put the whole code of the subroutine below.

Sub CreateAndFormatTable(WS As Worksheet, StartCol As String, EndCol As String, StartRow As Long, EndRow As Long) With WS     Dim i As Long     .Range(StartCol & StartRow & ":" & EndCol & EndRow).Select         '---------------------------------------     'Add borders     '---------------------------------------     Selection.Borders(xlDiagonalDown).LineStyle = xlNone     Selection.Borders(xlDiagonalUp).LineStyle = xlNone     With Selection.Borders(xlEdgeLeft)         .LineStyle = xlContinuous         .ColorIndex = 0         .TintAndShade = 0         .Weight = xlThin     End With     With Selection.Borders(xlEdgeTop)         .LineStyle = xlContinuous         .ColorIndex = 0         .TintAndShade = 0         .Weight = xlThin     End With     With Selection.Borders(xlEdgeBottom)         .LineStyle = xlContinuous         .ColorIndex = 0         .TintAndShade = 0         .Weight = xlThin     End With     With Selection.Borders(xlEdgeRight)         .LineStyle = xlContinuous         .ColorIndex = 0         .TintAndShade = 0         .Weight = xlThin     End With     With Selection.Borders(xlInsideVertical)         .LineStyle = xlContinuous         .ColorIndex = 0         .TintAndShade = 0         .Weight = xlThin     End With     With Selection.Borders(xlInsideHorizontal)         .LineStyle = xlContinuous         .ColorIndex = 0         .TintAndShade = 0         .Weight = xlThin     End With     '---------------------------------------     'Background Color of headers     '---------------------------------------     .Range(StartCol & StartRow & ":" & EndCol & StartRow).Select     With Selection.Interior         .Pattern = xlSolid         .PatternColorIndex = xlAutomatic         .Color = 12611584         .TintAndShade = 0         .PatternTintAndShade = 0     End With     '-----------------------------------------     'Background color of other area     '-----------------------------------------     For i = StartRow + 2 To EndRow Step 2         .Range(StartCol & i & ":" & EndCol & i).Select         With Selection.Interior             .Pattern = xlSolid             .PatternColorIndex = xlAutomatic             .ThemeColor = xlThemeColorAccent1             .TintAndShade = 0.799981688894314             .PatternTintAndShade = 0         End With     Next i     '-----------------------------------------     'All table font name and size     '-----------------------------------------     .Range(StartCol & StartRow & ":" & EndCol & EndRow).Select     With Selection.Font         .Name = "Arial"         .Size = 11         .Strikethrough = False         .Superscript = False         .Subscript = False         .OutlineFont = False         .Shadow = False         .Underline = xlUnderlineStyleNone         .ThemeColor = xlThemeColorLight1         .TintAndShade = 0         .ThemeFont = xlThemeFontNone     End With     '---------------------------------------------     'Set header font color and make the font bold     '---------------------------------------------     .Range(StartCol & StartRow & ":" & EndCol & StartRow).Select     With Selection.Font         .ThemeColor = xlThemeColorDark1         .TintAndShade = 0     End With     Selection.Font.Bold = True         .Range("A2").Select End With End Sub

You can call the subroutine as follows in main program.

Dim WS_2 As Worksheet Set WS_2 = Worksheets("Sheet2") Call CreateAndFormatTable(WS_2, "B", "F", 3, 17)

Create Lines With VBA

Did you know that you can do graphic related things using vba.  Yes you can develop codes to create or modify shapes available in Microsoft Excel. So let's see how we can create a  straight line. What you need to do is you need to give x and y coordinates of the start and end of the line. Consider that the upper left corner of the document as (0,0) point. And you need to give the locations in points.

Sub DrawLine_Example1() ActiveSheet.Shapes.AddLine 0, 0, 250, 250 End Sub

If you run above macro you will get following result.

So the first two values are x and y coordinates of the starting point respectively.   and next two values are x and y coordinates of end point. And here is another example

Sub DrawLine_Example2() ActiveSheet.Shapes.AddLine 20, 100, 100, 45 End Sub

 
It will create line like this.

Now let's look at little advanced scenario. Assume you need to create a line between following two points.

So how we do that because we don't know exact x,y coordinates of those two points. Fortunately we can give start and end points from cell locations as well. So we can create the line using following code.

Sub DrawLine_Example3() Dim BeginX As Long Dim BeginY As Long Dim EndX As Long Dim EndY As Long With Range("B5")    BeginX = .Left + .Width    BeginY = .Top + .Height / 2 End With With Range("G9")    EndX = .Left    EndY = .Top + .Height / 2 End With ActiveSheet.Shapes.AddLine BeginX, BeginY, EndX, EndY End Sub

In the above code starting point is given by following part.

With Range("B5")    BeginX = .Left + .Width    BeginY = .Top + .Height / 2 End With

And end point is given by these 4 lines.

With Range("G9")    EndX = .Left    EndY = .Top + .Height / 2 End With

Also you can replace

With Range("B5")    BeginX = .Left + .Width    BeginY = .Top + .Height / 2 End With

With following code. Then you don’t need width for the calculations.

With Range("C5")    BeginX = .Left    BeginY = .Top + .Height / 2 End With

With VBA you can not only create lines but also create any other shapes you need.

How To Remove PERSONAL.XLSB file

Today I will show you how you can delete your PERSONAL.XLSB file which is also known as Personal Macro Workbook.

To do this, first we need to show hidden folders. Because this PERSONAL.XLSB file is located inside a hidden folder. So please follow below steps if you need to delete your PERSONAL.XLSB file.

Open the Windows Explorer.

Then click on the view menu and put tick to “Hidden items”

Go to the following file path. C:\Users\User 1\AppData\Roaming\Microsoft\Excel\XLSTART

Please note that “User 1” of above path should be replaced with the username of your computer.

Then delete the PERSONAL.XLSB. You will need to close the Excel application before delete this file if the app is in open state. This is how you can delete PERSONAL.XLSB file in Windows 10. If you are using different Windows version, steps will little different.

XLSB File

This post is about .XLSB file format. I will explain you few basic things about this file type and how to open this kind of files.

A file with .xlsb file format is call as Excel binary workbook file. So the data stored in this file in binary format. Like XLSM files these XLSB files can have macros in it. So you need to be very careful when opening this kind of file type unless you received it from trusted source.

Lot of other Excel file formats store data in XML like format. As XLSB file type store data in binary format, it is much faster to read from and write to these files. So if you have a file with mass amount of data, then it is advisable to convert it to the .XLSB format.

You can convert it like this.

Open your excel file.

Click on “File” menu.

Click “Save as”

Browse the folder you need to save your file.

Then give appropriate name to the file and select file format as .XLSB

Click “Save”

How to open .XLSB file

You can simply open these files using Microsoft Office Excel application. If you don’t have Excel application then you can use OpenOffice Calc or LibreOffice Calc too.

Concatenate using VBA

Today I'm going to explain you how to concatenate values in VBA. Excel application already have an inbuilt function to concatenate values in cells. What it does is, it just concatenate values in each cell. It is a very helpfull inbuilt function. But we often need more custom concatenations than that. So I will explain you how you can use VBA to concatenate values in cells according to your requirements.

This is an example worksheet.

Assume we need all the words in column A to E of each row to show up in column F with a comma and space between them.

Here is the VBA code which can accomplish that.

Sub ConcatenationExample() Dim WS As Worksheet Set WS = ActiveSheet 'find last row Lastrow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row Dim result As String For i = 1 To Lastrow     For j = 1 To 5         If WS.Cells(i, j) <> "" Then             If result <> "" Then                 result = result & ", " & WS.Cells(i, j)             Else                 result = result & WS.Cells(i, j)             End If         End If     Next j     WS.Cells(i, 23) = result     result = "" Next i End Sub

You will get following result after running above macro

Next I will explain you the code step by step.

Define the sheet variable.

Dim WS As Worksheet

assign active sheet to worksheet variable.

Set WS = ActiveSheet

Find the last row of the sheet

'find last row Lastrow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Define a variable to hold result in each loop

Dim result As String

Loop through rows

For i = 1 To Lastrow     Next i

Loop through columns from A to E

For j = 1 To 5         Next j

Check whether cell is not empty

If WS.Cells(i, j) <> "" Then             End If

We don't need comma at beginning. So we check result string to identify whether we going to concatenate first value or not.

If result <> "" Then     result = result & ", " & WS.Cells(i, j) Else     result = result & WS.Cells(i, j) End If

Write result to column F of each row

WS.Cells(i, 6) = result

Clear the result variable for next loop.

result = ""

Reference Dynamic Userform Name

If you want to reference a one particular  userform you can do it in a very simple way.  What you can do is you can just use the name of the  userform.  For an example,  think you have a userform call UserForm1. If you want to show  this   userform  you can simply use following code.

Sub ShowForm() UserForm1.Show End Sub

But sometimes we need to reference userforms dynamically.  For an example assume you have  several userforms.  You need to show a different forms depending on different conditions.  And there is a text box where program generate the name of the userform you need to display. That means you need to show the userform which is shown in this text box. Then we can't use above simple method.  Because we need to reference userforms  dynamically. So we need a more dynamic solution like below. Here txtDisplayFormName refers to name of the textbox where program displays the name of the userform  we need to show.

Dim formName As String formName = txtDisplayFormName.Value Dim DForm As Object Set DForm = UserForms.Add(formName) DForm.Show

How to Find Out Bit Version of Excel

In this post I will explain you how to find out bit version of excel. First, open the Excel application.

Choose blank workbook. So new workbook will be created. then click on the file menu.

Then click “Account”.

Next click the “About Excel” button.

Another window will open showing the details of your office version. Bit version will shown at top.