How to Save a Range Selection to Text File in Excel

This post will guide you how to Copy a Range of Selection to a Text file in Excel. How do I copy/paste a range from the current worksheet to a notepad text file with VBA Macro in Excel 2013/2016/2019/365.

Assuming that you have a list of data in range B1:B5 in your worksheet, and you wish to copy this range to another worksheet or a notepad text file, and save it as a .txt or xlsx. You can copy the selection to the clipboard by manually, open up a Notebook file or what ever text application you use, or you can open an blank worksheet, then paste it in there and save as the file. This method can work well, but it is not a good way. You can use an Excel VBA Macro to accomplish it quickly. And this post will show you how to use an Excel VBA code to save a range of selection to a text file.

1. Save a Range Selection as Text File

You can use an Excel VBA macro to save the selection as a text file without copying and pasting to a new file. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

export each sheet to csv2

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Sub SaveSelectionAsTextFile()
    Dim myFolder As String
    'Save Range as Text File.
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range to be copied", "SaveSelectionAsTextFile", myRange.Address, Type:=8)
    ActiveSheet.Activate
    myRange.Copy
    'This temporarily adds a sheet named "Test."
    Sheets.Add.Name = "Test"
    Sheets("Test").Select
    ActiveSheet.Paste
    'Ask user for folder to save text file to.
    myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
    'Save selected data as text file in users selected folder.
    ActiveWorkbook.SaveAs Filename:=myFolder, FileFormat:=xlText, CreateBackup:=False
    'Remove temporary sheet.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    'Indicate save action.
    MsgBox "Text File: " & myFolder & "Saved!"
    'Go to top of sheet.
    Range("A1").Select
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

save range selection to text file2

Step6: Select one range to be copied. Click OK button.

save range selection to text file3

Step7: enter one filename in File name text box in the Save As dialog box. Click Save button.

save range selection to text file4

Step8: open the newly created text file test1112.txt in your local disk.

save range selection to text file5

2. Video: Save a Range Selection to Text File in Excel

This video will demonstrate how to use VBA code to save a range selection to a text file in Excel.

How to Change Multiple Pivot Table Fields in Excel

This post will guide you how to change the format of all pivot table fields at once in Excel. How do I change multiple pivot table fields from the default sum of function to the count of function at once with VBA in Excel.

Assuming that you have created a pivot table with your data. And as the default setting for numbers is to sum the data for all numeric fields and I want to change the count of function to sum of function. You can change each of columns using the value field setting option or you can also use an VBA macro to change all fields at once.

1. Change Multiple Pivot Table Fields by Manually

If you want to change multiple pivot table fields, you can change the function in the Value Fields Settings, just do the following steps:

Step1: select one filed in your pivot table, and right click on it, and then choose Value Fields Settings from the dropdown menu list. And the Value Fields Settings dialog will open.

change multiple pivot table fields1

Step2: select Count function in the Summarize value field by list box, and click Ok button.

change multiple pivot table fields2

Step3: you would see that the sum function has been changed to count function for your selected field.

change multiple pivot table fields3

Step4: you need to repeat the above steps to change other’s fields for your pivot table.

2. Change Multiple Pivot Table Fields by VBA

You can also convert all the fields in a selected pivot table to the count function or other functions with VBA Macro in Excel. Just do the following steps to run the macro:

Step1: select your pivot table that you want to convert fields

Step2: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step3: then the “Visual Basic Editor” window will appear.

Step4: click “Insert” ->”Module” to create a new module.

export each sheet to csv2

Step5: paste the below VBA code  into the code window. Then clicking “Save” button.

change multiple pivot table fields4
Public Sub PivotFieldsToCount()
    Dim pf As PivotField
    With Selection.PivotTable
        .ManualUpdate = True
        For Each pf In .DataFields
            With pf
                .Function = xlCount
                .NumberFormat = "#,##0"
            End With
        Next pf
         .ManualUpdate = False
     End With
 End Sub

Step6: back to the current worksheet, then run the above excel macro. Click Run button.

change multiple pivot table fields5

Step7: Let’s see the result:

change multiple pivot table fields6

How to Extract Bold Text from A List in Excel

In our daily work we may mark some texts in bold to make them to be noticed clearly. And if we want to extract them from original list, we need to know the ways to find them and then move them to another range. This free tutorial will introduce you two ways to extract bold texts, the first one is to extract bold texts via Find and Replace function, the second one is to edit VBA Macro.

Precondition:

See screenshot below. Some texts are marked in bold.

How to Extract Bold Text from A List in Excel1

1. Extract Bold Text via Find and Replace Function

Step1: Press Ctrl+F to trigger Find and Replace dialog.

How to Extract Bold Text from A List in Excel2

Step2: Click ‘Options>>’ button to get more settings, then click Format.

How to Extract Bold Text from A List in Excel3

Step3: On Find Format window, click Font tab, under Font style select Bold, then click OK.

How to Extract Bold Text from A List in Excel4

Step4: After clicking OK, go back to Find and Replace dialog, click Find All to load all matched values. All bold texts are displayed, you can drag the scrollbar to check all of them.

How to Extract Bold Text from A List in Excel5

Step5: Press Ctrl+A on one of the loaded results, then all values are selected.

How to Extract Bold Text from A List in Excel6

At the same time, bold text cells are also selected on original table.

How to Extract Bold Text from A List in Excel7

Step6: Close Find and Replace dialog. Then you can copy them and paste them to another range.

How to Extract Bold Text from A List in Excel8

2. Extract Bold Text via VBA Macro

Step1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

How to Extract Bold Text from A List in Excel9

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic. You can also press Alt + F11 keys simultaneously to open it.

How to Extract Bold Text from A List in Excel10

Step2: In Microsoft Visual Basic for Applications window, click Insert->Module, enter below code in Module1. In this step we define a new function ‘ExtractBold’.

Function ExtractBold(SelectRng As Range)
    If SelectRng.Font.Bold Then
        ExtractBold = SelectRng.Value
    Else
        ExtractBold = ""
    End If
End Function

Step3: Save the macro. And then quit Microsoft Visual Basic for Applications.

Step4: In C2 apply the new function ‘ExtractBold’ we just created . Enter formula =ExtractBold(A2).

How to Extract Bold Text from A List in Excel11

Step5: Press Enter and verify that nothing is returned, that’s because ‘NPS001’ is not in bold.

How to Extract Bold Text from A List in Excel12

Step6: Drag the fill handle down to the end. Verify that bold texts in original table are extracted. But they are not displayed in bold after extracting.

How to Extract Bold Text from A List in Excel13

Step7: Apply ExtractBold function in D column to extract bold texts from column B. Now all bold texts are extracted. You can select all of them and mark them in bold after extracting them per your demand.

How to Extract Bold Text from A List in Excel14

3. Video: Extract Bold Text from A List in Excel

This video will show you how to extract bold text from a list in Excel using both the Find and Replace feature and VBA code.

How to Hide or Show Objects in Excel

This post will guide you how to hide all objects in your worksheet in Excel. How do I show all hidden objects in your current worksheets with VBA Macro in Excel.

If you want to hide all objects in your current worksheet, such as: pictures, comments, charts, Textbox, etc. You can use Selection pane feature or write down an Excel VBA Macro to show or hide all objects. Let’s see the following introduction.

1. Hide or Show All Objects with Selection Pane Feature

If you want to hide or unhide all objects in the current worksheet, you can use the Selection pane feature to achieve the result. Just do the following steps:

Step1: go to HOME tab, click Find & Select command under Editing group. And select Selection Pane … menu from the drop down menu list. And the Selection pane will be shown.

hide show objects1

Step2: you should see that all of the objects are listed in the Selection pane. And You can click Hide All button to hide all objects. Or you can click Show All button to show all hidden objects.

hide show objects2
hide show objects3

Step3: if you only want to hide or show specific objects in Excel, you just need to click its eye button, or click eye button again to show it.

hide show objects4

2. Hide or Show All Objects with VBA

You can also use an Excel VBA macro to achieve the same result of hiding or showing all objects in the current worksheet. Here are the steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

hide show objects5
Sub HideAllObjects()
    Dim ob As Shape
    For Each ob In ActiveSheet.Shapes
        ob.Visible = False
    Next
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

hide show objects6

All objects in the current worksheet would be hidden.

If you want to show all hidden objects with VBA code, you can use the following VBA Macro:

Sub ShowAllObjects()
    Dim ob As Shape
    For Each ob In ActiveSheet.Shapes
        ob.Visible = True
    Next
End Sub

3. Video: How to Hide or Show Objects in Excel

In this video, you will learn how to hide or show objects in Excel using the selection pane feature as well as VBA code.

Automatically Change Properties of All Pictures (move and size with cells)

This post will guide you on how to automatically change properties of all pictures in Excel using VBA code. How do I automatically change all pictures to move a size with cells in your worksheet with VBA macro code in excel.

You can quickly modify the properties of multiple pictures at once using VBA Macro, instead of changing them one by one. This can be a huge time saver if you have a large number of pictures in your Excel file that need to be modified.

By default, after you inserted a picture into worksheet, it will keep the default properties of move and don’t size with cells. And you need to change its properties to move and size with cells by manually in your worksheet.

1. Automatically Change Properties of All Pictures

Let’s do the following steps to automatically change properties of all pictures:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Sub AutoMoveAndSizeWithCells()
    Dim xPic As Picture
    For Each xPic In ActiveSheet.Pictures
        'Modify the picture properties here
        xPic.Placement = xlMoveAndSize
    Next
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

auto change properties2

You will see the all pictures are changed to move and size with cells automatically after running the above vba code.

2. Video: Automatically Change Properties of All Pictures

This video will show you how to use VBA code in Excel to automatically change properties of all pictures, allowing you to quickly modify multiple pictures at once.

How to Open Multiple Hyperlinks at Once in Excel

This post will guide you how to open multiple links at once in Excel. How do I open multiple Hyperlinks with VBA Macro in Excel 2013/2016/2019/365.

 When dealing with large data sets, it can be time-consuming and frustrating to manually click on each hyperlink to access its contents. Excel provides several built-in features to simplify this process, such as the “Follow Hyperlink” function. However, this function only works for one hyperlink at a time. In this post, we will explain how to use VBA code to automate the process of opening multiple hyperlinks quickly in Excel.

1. Open Multiple Links at Once with VBA Code

Assuming that you have a list of data which contain multiple hyperlinks and you want to open those links at once in your current worksheet. How to do it quickly. I think you have to use the VBA Macro to quickly achieve it. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

export each sheet to csv2

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

open multiple hyperlinks2
Sub OpenMultipleLinks()
    On Error Resume Next
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Range", "OpenMultipleLinks", myRange.Address, Type:=8)
    For Each oneLink In myRange.Hyperlinks
        oneLink.Follow
    Next
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

open multiple hyperlinks1

Step6: please select one range that contain hyperlinks you want to open. click ok button.

open multiple hyperlinks3

Step7: you would see that all of hyperlinks have been opened in your default browser.

2. Video: Open Multiple Hyperlinks at Once with VBA Code

This video will demonstrate a quick and easy way to open multiple hyperlinks simultaneously in Excel using VBA code, saving you time and effort while increasing productivity.

How to Count the Number of Sheets in a Workbook

This post will guide you how to count the number of sheets in a workbook in Excel 2013/2016/2019/365. How do I count the number of worksheets in a workbook with VBA Macro in Excel.

While manually counting the sheets is possible, it can be a tedious and time-consuming task, especially in larger workbooks. Fortunately, Excel provides several built-in functions and features that can help simplify this task. Additionally, for those who are comfortable with using VBA Macros, we will also explore a quick and easy method to count the number of sheets using a simple VBA macro.

1. Count the Number of Sheets with Define Name

If you want to count the number of worksheets in a given workbook in Excel, you can use the Defined Name and a Formula to achieve it. Just do the following steps:

 Step1: go to Formula tab, click Define Name command under Defined Names group, and the New Name dialog will open.

count worksheet number1

 Step1: type one defined name in the Name text box, such as: countWorksheets, and then type the formula =GET.WORKBOOK(1)&T(NOW()) into the text box of Refers to. Click Ok button.

count worksheet number2

 Step3: Type the following formula based on the COUNTA function and the INDEX function to get the number of worksheets in the current workbook. And press Enter key in your keyboard, you will get the number of worksheets in your workbook.

=COUNTA(INDEX(CountWorksheets,0))
count worksheet number3

2. Count the Number of Sheets with VBA Macro

You can also use an Excel VBA Macro to get the number of worksheets in the current workbook. Just do the following steps:

 Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

 Step2: then the “Visual Basic Editor” window will appear.

 Step3: click “Insert” ->”Module” to create a new module.

convert column number to letter3

 Step4: paste the below VBA code into the code window. Then clicking “Save” button.

count worksheet number4
Sub CountWorkSheets()
    MsgBox "The number of worksheets in the current workbook is : " & Application.Sheets.Count
End Sub

 Step5: back to the current worksheet, then run the above excel macro. Click Run button.

count worksheet number5

 Step6: let’s see the result:

count worksheet number6

3. Video: Count the Number of Sheets

This video will guide you through a step-by-step process to count the number of sheets in an Excel workbook using VBA code, saving you time and effort while ensuring accuracy.

How to Save File based on Cell Value in Excel

This post will provide you with a step-by-step guide on how to save Excel workbook file with Cell value in Excel using VBA code in Excel 2013/2016/2019/365. Saving files based on specific criteria can be a tedious and time-consuming task, especially if you have to do it repeatedly. However, with VBA code, you can automate this process and save yourself a lot of time and effort.

1. Save File Based on Cell Value with VBA Code

Assuming that you have a request that save the current workbook file I am working as the value in Cell A1. For example, the value of Cell A1 is testWork, and you want to save the workbook as the name of testWork.

To Save file with Cell Value in Excel, you need to write down an Excel VBA Macro to achieve the result. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

export each sheet to csv2

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

save file based on cell value1
Sub FileNameAsCellContent()
    Dim FileName As String
    Dim Path As String
    Application.DisplayAlerts = False
    Path = "C:\test\"
    FileName = Range("A1").Value & ".xlsx"
    ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: Let’s see the result:

save file based on cell value2

2. Video: Save File based on Cell Value in Excel

This video will demonstrate how to save a file based on a cell value in Excel using VBA code.

3. Conclusion

Now you should have a good understanding of how to use VBA code to save files based on cell values, and you’ll be able to apply this knowledge to your own Excel projects.

How to Insert Cell Content into Header or Footer in Excel

This post will guide you how to insert the contents of a paricular into the header or footer cell in your active worksheet in Excel. How do I put a cell value into header or footer in all worksheets in your active workbook using VBA Macro in Excel.

You can easily add the number of pages, the current date and time or the name of current file, including the File Full path into the header or footer in your Excel. And there is no built-in command or function to add a cell value into the header or footer. How to do it. You can use an Excel VBA Macro to insert cell Content into a paricular worksheet or all worksheet in your workbook quickly.

1. Insert cell Content into Header or Footer in a Worksheet

To put a cell value into the header or footer in your current worksheet in Excel, and you can do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.
export each sheet to csv2

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

How to insert cell value into header or footer in Excel1
Sub InsertCellValueIntoHeader()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Single Cell that you want to put its into Header or Footer", "InsertCellValueIntoHeader", myRange.Address, Type:=8)
    Application.ActiveSheet.PageSetup.LeftHeader = myRange.Value
End Sub

Step5: back to the current worksheet, click on Macros button under Code group. then click Run button.

How to insert cell value into header or footer in Excel2

Step6: Select One Single Cell that you want to put its into Header or Footer. click on Ok button.

How to insert cell value into header or footer in Excel3

Step7: let’s see the last result:

How to insert cell value into header or footer in Excel4
How to insert cell value into header or footer in Excel6

If you want to add a cell value into the footer in your current worksheet, and you can use the following VBA Macro.

How to insert cell value into header or footer in Excel5
Sub InsertCellValueIntoFooter()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Single Cell that you want to put its into Header or Footer", "InsertCellValueIntoHeader", myRange.Address, Type:=8)
    Application.ActiveSheet.PageSetup.LeftFooter = myRange.Value
End Sub

If you need to add a cell value into the RightHeader or RightFooter, and you just need to change one code line(Application.ActiveSheet.PageSetup.LeftHeader or Application.ActiveSheet.PageSetup.LeftFooter ) in above VBA Function as below line:

Application.ActiveSheet.PageSetup.RigthHeader = myRange.Value

or

Application.ActiveSheet.PageSetup.RightFooter = myRange.Value

2. Insert Cell Content into Header or Footer in All Worksheets

You can also insert a cell value into the header or footer for all worksheets in your active workbook using a VBA Macro in Excel.  see belows:

How to insert cell value into header or footer in Excel7
Sub InsertCellValueIntoHeaderForAllSheets()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Single Cell that you want to put its into Header or Footer", "InsertCellValueIntoHeader", myRange.Address, Type:=8)
    For Each mysheet In Application.ActiveWorkbook.Worksheets
        mysheet.PageSetup.LeftHeader = myRange.Value
    Next
End Sub

If you need to insert cell content into footer for all worksheets, and you can use the following VBA Macro:

How to insert cell value into header or footer in Excel8
Sub InsertCellValueIntoFooterForAllSheets()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Single Cell that you want to put its into Header or Footer", "InsertCellValueIntoHeader", myRange.Address, Type:=8)
    For Each mysheet In Application.ActiveWorkbook.Worksheets
        mysheet.PageSetup.LeftFooter = myRange.Value
    Next
End Sub

3. Video: Insert Cell Content into Header or Footer in Excel

If you want to learn how to insert cell content into header or footer in Excel, this video will show you a simple and effective way using VBA code.

Replace Accented Characters

This post will guide you how to replace accented characters with regular letters in Excel. How do I use VBA Macro to replace accented characters in Excel. How to convert accented characters quickly with VBA Macro quickly in Excel.

1. Replace Accented Characters

Assuming that you have a very large text data which contains accented characters within words, and you want to replace each of these accented characters with regular characters in all cells. How to achieve it. You can use an Excel VBA Macro to achieve the result quickly. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub ReplaceAccentedChar()
Const sFm As String = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ" 
Const sTo As String = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy" 
Dim i As Long, ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    For i = 1 To Len(sFm)
        ws.Cells.Replace Mid(sFm, i, 1), Mid(sTo, i, 1), LookAt:=xlPart, MatchCase:=True
    Next i
Next ws
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

2. Video: Replace Accented Characters

If you want to learn how to replace accented characters with regular letters in Excel, this video will show you a simple and effective method using VBA code.

How to Save or Export Each Sheet to Separate CSV Files in Excel

This post will guide you how to save each worksheet to separate CSV files in your workbook in Excel. How do I save multiple or all worksheets to separate csv file in Excel 2013/2016. How to export all worksheets to separate txt files with VBA macro in Excel.

1. Export Each Sheet to Separate CSV Files

Assuming that you have a workbook that has four different worksheets, sheet1, sheet2, sheet3, and sheet4. And You want each worksheet as its own CSV file and we also want the CSV file name to take into account its Excel Source as well as the workbook where it originates. To achieve the result of exporting multiple worksheets separate csv files, you have to use VBA Macro to save all worksheets in the current workbook to separated csv files. Here are the steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

export each sheet to csv2

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Public Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook

      SaveToDirectory = "D:\tmp\"

      For Each WS In ThisWorkbook.Worksheets
          WS.SaveAs SaveToDirectory & WS.Name, xlCSV
      Next

 Application.DisplayAlerts = False
  ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
 Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub
export each sheet to csv1

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

export each sheet to csv3

Step6: you would see that all worksheet has been converted to a single CSV files in the specified directory. This example will save csv file into D:\tmp\ directory. You need to change the variable “SaveToDirectory ” as you need. or you can also create a temp directory called tmp in disk D partition.

export each sheet to csv4

2. Export Each Sheet to Separate Text Files

If you want only convert each worksheet to separate text files for your active workbook, you can also use an Excel VBA Macro to achieve the result. Just using the following VBA code:

Public Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook

      SaveToDirectory = "D:\tmp\"

      For Each WS In ThisWorkbook.Worksheets
          WS.SaveAs SaveToDirectory & WS.Name & ".txt"
      Next

 Application.DisplayAlerts = False
  ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=xlText
 Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub

Let’s see the result:

export each sheet to csv5

3. Conclusion

By using VBA code, you can create a custom macro that will iterate through each sheet in your workbook and save it as a separate CSV file. This code can be customized to suit your specific needs and can be easily modified to include additional functionality or to save the files in a different format.

Overall, if you frequently work with large Excel workbooks that contain multiple sheets, using VBA code to save each sheet as a separate CSV file is a great way to streamline your workflow and improve your productivity.

How To Set paste Values as Default Paste in Excel

This post will guide you how to set paste values as default paste in Excel. How do I set the default paste special in Excel to paste only values when using Ctrl+v keys. when you want to copy data from a selected range of cells and then pasted it to another range with Ctrl + V keys on your keybaord, and you only want to copy cell values and without including cell formats. How to do it. you can use an Excel VBA Macro to achive the result.

1. Set Paste Values as Default Paste in Excel

If you only want to set paste values as default paste in your worksheet, and you can use an Excel VBA Macro to accomplish it. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.
export each sheet to csv2

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

set paste values as default paste 1
Sub SetPasteDefaultasValue()
     Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Step5: back to the current worksheet, click on Macros button under Code group, and the Macro dialog will open. then click Options button in the Macro dialog box.

set paste values as default paste2

Step6: you need to enter v key under the Shortbut key section in Macro Options dialog box. click on Ok button to take effect for changes.

set paste values as default paste3

Step7: Closing the Macro dialog box. and try to copy a cell with formatting style, and then paste it to antoher cell. You would see that only values will be copied.

set paste values as default paste4
set paste values as default paste5

2. Video: Set paste Values as Default Paste in Excel

This video will show you how to set paste values as default paste in Excel using a VBA Code.

How to Deselect Cells from Selected Range with VBA

This post will guide you how to deselect cells from a selected range of cells in your worksheet. How do I quickly deselect cells from a selected range of cells with VBA code in Excel.

Normally, if you want to deselect a cell or multiple cells from a selection, you just need to hold down the CTRL key and click on the cells you want to deselect. If you want to unselect a range of selected cells, you need to hold down the CTRL key and drag the range you want to deselect. This post will explain that how to deselect cells from a selection with VBA Macro quickly in Excel.

1. Deselect Cells from Selected Range with VBA

You can use an Excel VBA Macro to quickly achieve the same result of deselecting one or multiple cells from a selected range of cells. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

deselect cells from selected range1
Sub DeSelectCells()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range:", "DeSelectCells", myRange.Address, Type:=8)
    Set DeleteRng = Application.InputBox("select one cell or range of cells that you want to deselect", "DeSelectCells", Type:=8)
    Dim LastRange As Range
    
    For Each myCell In myRange
        If Application.Intersect(myCell, DeleteRng) Is Nothing Then
            If LastRange Is Nothing Then
                Set LastRange = myCell
            Else
                Set LastRange = Application.Union(LastRange, myCell)
            End If
        End If
    Next

    LastRange.Select

End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

deselect cells from selected range2

Step6: Select one Range that you want to deselect cells, such s:A1:D7

deselect cells from selected range3

Step7: select one cell or range of cells that you want to deselect.

deselect cells from selected range4

Step8: Let’s see the last result:

deselect cells from selected range5

2. Video: Deselect Cells from Selected Range with VBA

This video will demonstrate you how to deselect cells from a selected range of cells with a VBA Macro in your worksheet.

Check If a Cell contain Image/Picture

This post will guide you how to check if a specific cell contains an image or picture in excel. Is it possible to check if a cell contains an image. This post will guide you how to define a User Defined Function to determine if an image exists in a specified cell. Or how to use VBA Macro code to check if a cell contains an image.

1. Check If a Cell contain Image Using User Defined Function

The simplest method is to create a user defined function to check if a cell contains an image file, and return 1 if image exists in cell. Otherwise, returns 0. You can follow these steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

check if a cell contain image2
Function CellImageCheck(CellToCheck As Range) As Integer
    ' Return 1 if image exists in cell, 0 if not
    Dim wShape As Shape
    For Each wShape In ActiveSheet.Shapes
        If wShape.TopLeftCell = CellToCheck Then
            CellImageCheck = 1
        Else
            CellImageCheck = 0
        End If
    Next wShape
End Function

Step5: back to the current worksheet, try to enter the below formula in Cell D6.

=CellImageCheck(B6)
check if a cell contain image1

2. Check If a Cell contain Image with VBA Macro

You can also use the following VBA code to check if a cell contains an image.  You just need to repeat the above 1-3 steps firstly, then continue to do the following steps:

Step1: paste the below VBA code into the code window. Then clicking “Save” button.

check if a cell contain image3
Sub CellImageCheck()
    Dim checkRange As Range
    Dim x As Shape
    Dim flag As Boolean
    On Error Resume Next
    Set checkRange = Application.InputBox("please enter one cell that you want to check", "CellImageCheck", Selection.Address, , , , , 8)
    If checkRange Is Nothing Then Exit Sub
        flag = False
        For Each x In ActiveSheet.Shapes
            If x.TopLeftCell.Address = checkRange.Address Then
                flag = True
            End If
        Next
    If flag Then
        MsgBox "Found an image!"
    Else
        MsgBox "No Image"
    End If
End Sub

Step2: back to the current worksheet, then run the above excel macro. Click Run button.

check if a cell contain image4

Step3: select one cell that you want to check, such as: B6

check if a cell contain image5

Step4: Let’s see the result:

check if a cell contain image6

3. Video: Check If a Cell contain Image in Excel

This video demonstrates how to use a User Defined Function with VBA Macro to check if a cell contains an image or picture in Excel.

How to remove non numeric characters from a cell

This post explains that how to remove non-numeric characters (numbers) from a text string in one cell in excel 2016/2019/365. How to remove non numeric characters from a cell containing text string with an excel formula. And how to remove alphanumeric characters from a cell with a user defined function in excel VBA.

1. Remove non numeric characters with an Excel Formula

If you want to remove non numeric characters from a text cell in excel, you can use the array formula:

{=TEXTJOIN("",TRUE,IFERROR(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)+0,""))}

Let’s see how the above formula works:

=ROW(INDIRECT(“1:”&LEN(B1))

The ROW function returns the below array list:

{1,2,3,4,5,6,7,8,9}

=MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)

The MID formula will return the below array:

{"e","x","c","e","l","2","0","1","6"}

=IFERROR(MID(B1,ROW(INDIRECT(“1:”&LEN(B1))),1)+0,””)

The array returned by the above MID function add zero for each value in array. If the value is a numeric text, it will be converted to text format. If not, returns empty string. So the IFERROR function returns the below array:

{2,0,1,6}

Last, the TEXTJOIN function join the values in above array returned by the IFERROR function.

Remove non numeric characters with an Excel Formula1

2. Remove non numeric characters using VBA Code

You can create a new function to remove numeric characters from a cell that contain text string in Excel VBA. Just refer to the below steps:

Step1: open visual Basic Editor, then insert a module and name as : RemoveNonNum.

Remove non numeric characters with excel vba1

Step2: click “Insert“->”Module“, then paste the following VBA code into the window:

Step3: paste the below VBA code into the code window. Then clicking “Save” button.

remove non numeric characters from a cell1
Sub RemoveNonNum()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("select one Range that you want to remove non numeric characters", "RemoveNonNum", myRange.Address, Type:=8)
    For Each myCell In myRange
        LastString = ""
        For i = 1 To Len(myCell.Value)
            mT = Mid(myCell.Value, i, 1)
            If mT Like "[0-9]" Then
                tString = mT
            Else
                tString = ""
            End If
            LastString = LastString & tString
        Next i
        myCell.Value = LastString
    Next
End Sub

Step4: back to the current worksheet, then run the above excel macro. Click Run button.

Remove non numeric characters with excel vba4

Step5: select one Range that you want to remove non numeric characters. click Ok button.

remove non numeric characters from a cell2

Step6: Let’s see the last result:

Remove non numeric characters with excel vba4

3. Video: Remove non numeric characters in Excel

This video will demonstrate how to remove non-numeric characters in Excel using a formula or VBA code.

4. Related Formulas

  • Remove Numeric Characters from a Cell
    If you want to remove numeric characters from alphanumeric string, you can use the following complex array formula using a combination of the TEXTJOIN function, the MID function, the Row function, and the INDIRECT function..…
  • Combine Text from Two or More Cells into One Cell
    If you want to combine text from multiple cells into one cell and you can use the Ampersand (&) symbol.If you are using the excel 2016, then you can use a new function TEXTJOIN function to combine text from multiple cells…

5. Related Functions

  • Excel TEXTJOIN function
    The Excel TEXTJOIN function joins two or more text strings together and separated by a delimiter. you can select an entire range of cell references to be combined in excel 2016.The syntax of the TEXTJOIN function is as below:= TEXTJOIN  (delimiter, ignore_empty,text1,[text2])…
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…
  • Excel IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel INDIRECT  function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….

How to Select Only Bold Cells in Excel

This post will guide you how to select only bold cells in a range of cells in Excel. How do I select all bold cells in a given range using VBA Macro in Microsoft Excel 2013/2016/2019/365.

Assuming that you have a list of data in range B1:C5, and you want to select all cells with bold font formatting in Excel. This post will show you two methods to select all bold cells.

1. Select Only Bold Cells Using Find And Replace

If you want to select all cells with bold fond formatting, you can use Find and Replace feature to find the specific text or specific format in the worksheet, such as: cell color, bold font…etc. Just do the following steps to find and select all the bold formatting in the selected range of cells:

Step1: select the range in which contain bold cells that you want to find.

select only bold cells1

Step2: go to Home tab, click Find & Select command under Editing group. And click on Find from the Find and Select drop down list. The Find and Replace dialog will open.

select only bold cells2

Step3: click on the Options button in the Find and Replace dialog box.

select only bold cells3

Step4: click the Format button in the Find what section, and select Choose Format From Cell from the Format drop down list. And the Find Format dialog will open.

select only bold cells4

Step5: click Font tab in the Find Format dialog box, and select the Bold option in the Font Style list box, and click Ok button to go back to the Find and Replace dialog box.

select only bold cells5

Step6: click the Find All button, then all cells with the bold font formatting in the selected range of cells would be searched.

select only bold cells6

Step7: press Ctrl + C keys on your keyboard to select all results, then it will select all the cells that have the text with bold fond formatting.

select only bold cells7
select only bold cells8

2. Select Only Bold Cells Using VBA

You can also use an Excel VBA Macro to achieve the same result of selecting only bold cells in a given range of cell. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Adding Comma Character at End of Cells vba1.png

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

select only bold cells9
Sub SelectOnlyBoldCells()
    Dim myRange As Range
    Dim cell As Range
    Dim tempRange As Range
    
    Set myRange = Range("A1", "D3")
    For Each cell In myRange
        If cell.Font.Bold = True Then
            If tempRange Is Nothing Then
                Set tempRange = cell
            Else
                Set tempRange = Union(tempRange, cell)
            End If
        End If
    Next cell
     
    If Not tempRange Is Nothing Then
        tempRange.Select
    End If
    
End Sub

Note: This VBA code will use another range called tempRange, which receives the cell reference with the bold fond formatting. And the UNION function is used to concatenate the addresses together. After the procedure finished, the range tempRange is selected.

You need to change the value of myRange variable as you need.

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

select only bold cells10

Step6: Let’s see the result:

select only bold cells8

3. Video: How to Select Only Bold Cells in Excel

In this video, you will learn how to select only bold cells in Excel using find and replace as well as VBA code.

How to Set Specific Cells as Read Only/Locked in Excel

This post will guide you on how to set specific cells as read-only or locked using Protect Sheet feature and vba code in Microsoft Excel 2013/2016/2019/365.

In our daily work, in case of protect our worksheet from being edit by others, we often add a password for opening worksheet. But sometimes, the worksheet is open for all or shared with your partners, we just want to set some important cells as read only in case of preventing others to edit them in worksheet, for example we need to set some cells with formulas or salaries as read only in payroll, how can we do? This article will help you to make your specific cells as read only.

1. Lock Specific Cells in Worksheet by Format Cells Settings

Prepare a table with ID, Name, Score three columns. And we want to make some cells in this table cannot be editable by others.

A simple table for demonstration in this article:

Set Specific Cells as Read Only 1

Step1: Press Ctrl+A to select the whole worksheet. You can also click on the arrow Set Specific Cells as Read Only 2 arrow button to select whole cells on current worksheet, see screenshot below.

Set Specific Cells as Read Only 3

Step2: On worksheet anywhere, right click to load menu, select Format Cells. You can also load Format Cells window by press Ctrl+1.

Set Specific Cells as Read Only 4

Step3: Format Cells window is loaded properly.

Set Specific Cells as Read Only 5

Step4: Click on Protection, there are two options, Locked and Hidden, Locked option is checked by default. Uncheck Locked option, then click OK.

Set Specific Cells as Read Only 6

Notes:

a. You can see the comments under the option that locked or hidden options only take effective once protect the worksheet is enabled. So, we need to protect the worksheet then.

b. If we don’t uncheck Locked option first, and directly protect worksheet by password, then the whole worksheet will be locked. So this step is to make sure that only the selected range will be locked, please see the following step.

Step5: Select the range you want to make them as Read Only, for example select A2:C4. Right click to load menu, select Format Cells again.

Set Specific Cells as Read Only 7

Step6: This time, enter Protection tab, check on the Locked option, then click OK.

Set Specific Cells as Read Only 8

Step7: Click Review->Protect Sheet.

Set Specific Cells as Read Only 9

Step8: In Protect Sheet window, “Protect worksheet and contents of locked cells”, “Select locked cells” and “Select unlocked cells” are checked by default. Enter password in textbox and click OK, re-enter and confirm password again, then click OK.

Enter password:

Set Specific Cells as Read Only 10

Confirm password:

Set Specific Cells as Read Only 11

Step9: Try to edit the table. Verify that below error message pops up.

Set Specific Cells as Read Only 12

Step10: Click A3. Verify that user still can edit it due to it is not locked.

Set Specific Cells as Read Only 13

Step11: If we want edit the locked cells, please click on Review->Unprotect Sheet. Unprotect Sheet pops up.

Set Specific Cells as Read Only 14

Step12: Enter the password. Click OK. Now you can edit specific locked cells normally again.

Set Specific Cells as Read Only 15

2. Lock Specific Cells in Worksheet by VBA

Step1: Right click on Sheet1 to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

How to Prevent Users from Adding New Worksheet 6

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic.

How to Prevent Users from Adding New Worksheet 7

Step2: In Microsoft Visual Basic for Applications window, click on Sheet1, enter below code:

Set Specific Cells as Read Only 16
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 Or Target.Column = 2 Or Target.Column = 3 Then
        If Target.Row = 2 Or Target.Row = 3 Or Target.Row = 4 Then
            Beep
            Cells(Target.Row, Target.Column).Offset(0, 1).Select
            MsgBox Cells(Target.Row, Target.Column).Address & " You cannot edit the cell!", _vbInformation, "Error Message"
        End If
    End If
End Sub

Comments:

a. In above script, Target.Column and Target.Row define the range will be locked. In this case, column 1,2,3 (A, B, C) and row 2,3,4 are included, that means range A2:C4 is selected and locked.

b. “You cannot edit the cell!” is the message pops up when user clicking on locked cell. “Error Message” is the header for pops up error message. You can replace them.

Step3: Save the codes, see screenshot below. And then quit Microsoft Visual Basic for Applications.

Step4: Try to edit the locked range A2:C4. Below error message pops up.

Set Specific Cells as Read Only 17

Step5: Try to edit A1 which is not locked. Verify that user still can edit A1 properly. User can also edit other unlocked cells as well.

Set Specific Cells as Read Only 18

3. Video: Set Specific Cells as Read Only/Locked in Excel

This Video will show yo how to set specific cells as red-only or locked in Excel using Protect Sheet feature and VBA Code.

4. SAMPLE Files

Below are sample files in Microsoft Excel that you can download for reference if you wish.

5. Conclusion

Excel provides users with the ability to protect their worksheets and workbooks to prevent unauthorized changes. By default, all cells in a worksheet are editable, but you can restrict access to specific cells to maintain data integrity and prevent accidental changes. In this guide, you can set specific cells as read-only or locked, and ensure that only authorized users can modify them.

Center an Image in an Excel Cell

This post will guide you how to center an image file in an excel cell. And how do I center to align a graphic in an excel cell. How to align a picture within a cell. How to align a picture to the center of a cell with VBA code in excel.

1. Center an Image in an Excel Cell

To quickly center an image in an excel cell, you can create an excel VBA macro to achieve the result. Just do the following steps:

Step1: import one picture into the current worksheet.

center iamge11

Step2: click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step3: then the “Visual Basic Editor” window will appear.

Step4: click “Insert” ->”Module” to create a new module

convert column number to letter3

Step5: paste the below VBA code into the code window. Then clicking “Save” button.

center iamge1
Sub CenterImages()
     With ActiveSheet.Shapes("Picture 1")
         .Top = Range("B1").Top + (Range("B1").Height - .Height) / 2
         .Left = Range("B1").Left + (Range("B1").Width - .Width) / 2
    End With
End Sub

Step6: back to the current worksheet, then run the above excel macro. Click Run button.

center iamge2

Step7: Let’s see the result.

center iamge3

2. Video: Center an Image in an Excel Cell

This video will show you how to center an image in an Excel cell using VBA code.

3. Conclusion

Centering an image in an Excel cell can enhance the presentation of the data and make the spreadsheet more visually appealing, and can be achieved through various methods, including manual adjustment or by using VBA code to automate the process.

Insert Character or Text to Cells

This post will guide you how to insert character or text in middle of cells in Excel. How do I add text string or character to each cell of a column or range with a formula in Excel. How to add text to the beginning of all selected cells in Excel. How to add character after the first character of cells in Excel.

Assuming that you have a list of data in range B1:B5 that contain string values and you want to add one character “E” after the first character of string in Cells. You can refer to the following two methods.

1. Insert Character or Text to Cells with a Formula

To insert the character to cells in Excel, you can use a formula based on the LEFT function and the MID function. Like this:

=LEFT(B1,1) & "E" & MID(B1,2,299)

Type this formula into a blank cell, such as: Cell C1, and press Enter key. And then drag the AutoFill Handle down to other cells to apply this formula.

insert text to cells1

This formula will inert the character “E” after the first character of string in Cells. And if you want to insert the character or text string after the second or third or N position of the string in Cells, you just need to replace the number 1 in Left function and the number 2 in MID function as 2 and 3. Like below:

=LEFT(B1,2) & "E" & MID(B1,3,299)
insert text to cells2

2. Insert Character or Text to Cells with VBA

You can also use an Excel VBA Macro to insert one character or text after the first position of the text string in Cells. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

insert text to cells3
Sub AddCharToCells()
    Dim cel As Range
    Dim curR As Range
    Set curR = Application.Selection
    Set curR = Application.InputBox("select one Range that you want to insert one 
    character", "add character to cells", curR.Address, Type: = 8)
    For Each cel In curR
        cel.Value = VBA.Left(cel.Value, 1) & "E" & VBA.Mid(cel.Value, 2, 
        VBA.Len(cel.Value) - 1)
    Next
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

insert text to cells4

Step6: select one Range that you want to insert one character.
insert text to cells5

Step7: lets see the result.

insert text to cells6

3. Video: Insert Character or Text to Cells

This video will demonstrate how to insert character or text in middle of cells in Excel using both formulas and VBA code.

4. Related Functions

  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…

How to Get the Data Size of Each Worksheet in a Workbook

If you’re working with large workbooks in Excel, you may find it useful to know the size of each worksheet within the workbook. This information can help you identify which worksheets are taking up the most space and optimize your file for better performance.

This post will guide you how to check the size of each worksheet of workbook using VBA code in Excel. How do I get the data size of each worksheet in a workbook in Excel 2013/2016/2019/365.

Assuming that you have a Workbook which contains multiple worksheet, and you want to know the data size of each worksheet in the current workbook. This post will show two methods with you.

Method1: Get the Data Size of Each Worksheet by Manually

You can get the data size of each worksheet one by one manually, and you just need to copy the contents of an Excel worksheet in your workbook, and then create a new workbook, and pasted the content into sheet1 in the new workbook, and then save the new workbook to a windows folder. Then you can get the size of this newly workbook.

You can repeat the above steps to get the data sizes of other worksheets one by one in your workbook.

Method2: Get the Data Size of Each Worksheet via VBA Macro

If you want to quickly get the data size of each worksheet in your current workbook, you can use an Excel VBA macro to accomplish it. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

get size of each sheet1
Sub WorksheetSizes_excelhow()
    Dim wks As Worksheet
    Dim c As Range
    Dim sFullFile As String
    Dim sReport As String
    Dim sWBName As String

    sReport = "Size Report"
    sWBName = "tmp.xls"
    sFullFile = ThisWorkbook.Path & _
      Application.PathSeparator & sWBName

    ' Add new worksheet to record sizes
    On Error Resume Next
    Set wks = Worksheets(sReport)
    If wks Is Nothing Then
        With ThisWorkbook.Worksheets.Add(Before:=Worksheets(1))
            .Name = sReport
            .Range("A1").Value = "Worksheet Name"
            .Range("B1").Value = "Approximate Size"
        End With
    End If
    On Error GoTo 0
    With ThisWorkbook.Worksheets(sReport)
        .Select
        .Range("A1").CurrentRegion.Offset(1, 0).ClearContents
        Set c = .Range("A2")
    End With

    Application.ScreenUpdating = False
    ' Loop through worksheets
    For Each wks In ActiveWorkbook.Worksheets
        If wks.Name <> sReport Then
            wks.Copy
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs sFullFile
            ActiveWorkbook.Close SaveChanges:=False
            Application.DisplayAlerts = True
            c.Offset(0, 0).Value = wks.Name
            c.Offset(0, 1).Value = FileLen(sFullFile)
            Set c = c.Offset(1, 0)
            Kill sFullFile
        End If
    Next wks
    Application.ScreenUpdating = True
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

get size of each sheet2

Step6: let’s see the last result:

get size of each sheet3

Video: Get the Data Size of Each Worksheet

This video will show you how to use VBA code to get the data size of each worksheet in a workbook.

Conclusion

With the above VBA code, you can quickly and easily retrieve the size of each worksheet in your workbook and take necessary steps to manage the file size in Microsoft Excel.