1. Copy file from one location to other using Excel VBA code
You can use this code to copy paste the file from one destination to other
e.g. if you require to take backups of the file on daily basis.
Sub Copy_File()
' Declare variables
Dim filename As String, Source As String, Destination As String
'Set values
filename = "abc"
Source = "C:\Test\Desert.jpg"
Destination = "C:\Test\Backup\" & filename & ".jpg"
'File copy code
FileCopy Source, Destination
End Sub
2. Copy and Save file with Date Time Stamp using Excel VBA
You can use below code to copy and save the file with the date and time stamp. This code is same as one of the Copy file code in my previous blog, Just check the changes in the code for Destination.
Sub Copy_File_dt ()
' Declare variables
Dim filename As String, Source As String, Destination As String
'Set values
filename = "abc_"
Source = "C:\Test\Desert.jpg"
Destination = "C:\Test\Backup\" & filename & Format(Now(), "mm_dd_yyyy hh mm AMPM") & ".jpg"
'File copy code
FileCopy Source, Destination
End Sub
3. Remove hyperlinks from Excel, using VBA code
There is a simple one line code to do this. Copy paste below VBA code in excel VBA window and run. All hyperlinks from the active sheet will be removed.
Sub removeallhyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub
you can also use below code:
Sub removeallhyperlinks()
Cells.Hyperlinks.Delete
End Sub
4. Resize all charts to same size using Excel VBA
Below code is used to re-size all the charts to same size from the the active sheet. Copy paste below code in your excel VBA window and run the macro to resize the charts.
Sub ResizeCharts()
' assigning the chart as chartobject, which will identify the charts in the activesheet
Dim chart As ChartObject
For Each chart In ActiveSheet.ChartObjects
With chart.Parent
' change the numbers in the below brackets (5) to change the size of the chart. Here we are using inches to set the chart size.
chart.Height = Application.InchesToPoints(5)
chart.Width = Application.InchesToPoints(10)
End With
Next
MsgBox "All charts in the active sheet are resized !"
End Sub
In above code we are using inches as the measure to change the chart size. Similarly we can use centimeters to re-size the charts.
Sub ResizeCharts()
' assigning the chart as chartobject, which will identify the charts in the activesheet
Dim chart As ChartObject
For Each chart In ActiveSheet.ChartObjects
With chart.Parent
' change the numbers in the below brackets (5) to change the size of the chart. Here we are using inches to set the chart size.
chart.Height = Application.CentimetersToPoints(5)
chart.Width = Application.CentimetersToPoints(10)
End With
Next
MsgBox "All charts in the active sheet are resized !"
End Sub
If you observe in the above code only two lines are changed.
chart.Height = Application.CentimetersToPoints(5)
chart.Width = Application.CentimetersToPoints(10)
5. How to deactivate Alerts while running macro in Excel VBA
You can deactivate the alerts received while running the VBA macro in excel, using below code -
Application.DisplayAlerts = False
You can also deactivate the screen updating while running the macro by using the below code -
Application.ScreenUpdating = False
If your macro is moving from different tabs of worksheet or excel files, the screen will show the details of the macro's step. To disable this we can use the above code.
Below is one of the example, on how this codes can be used -
Sub deactivateAlerts()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("Sheet1").Select
ActiveSheet.Hyperlinks.Delete
Sheets("Sheet2").Select
ActiveSheet.Hyperlinks.Delete
Sheets("Sheet3").Select
ActiveSheet.Hyperlinks.Delete
Sheets("Sheet4").Select
ActiveSheet.Hyperlinks.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Your macro is completed !"
End Sub
The simple code above used, to delete all the hyperlinks in 4 different sheets from your workbook. When the macro is running without the displayalerts = false and screenupdating = false, it will show the steps of macro on screen.
When the displayalerts = false and screenupdating = false is used, you will see the same screen where you are running the macro and you will directly receive the message box stating "Your macro is completed !"
Don't forget to add the below lines before End Sub of your code.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
These lines will reset the Alerts and Screen updating.
6. How to Open, Save As and Replace file using Excel VBA macro
Below is the macro which is used to Open, Save As and Replace the abc.xls file saved on location - C:\Users\Public\Documents\abc.xls
Sub ReplaceRename()
' Disable alerts
Application.DisplayAlerts = False
'Open file
Workbooks.Open filename:= _
"C:\Users\Public\Documents\abc.xls"
'Save As and replace or Rename file. In below case we are saving the .xls file in .xlsx format. In case you need to rename the file, just change the abc.xlsx to newname.xlsx in below code
ActiveWorkbook.SaveAs filename:= _
"C:\Users\Public\Documents\abc.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
'Close workbook
ActiveWindow.Close
'Reset alerts
Application.DisplayAlerts = True
'Message pop-up
Msgbox "Macro completed successfully !"
'Exit Macro
End Sub
If we run the macro with the removing the display alerts code on line 2 of code, then we will receive below pop-up message. With the above code we will receive only below message -
"Macro completed successfully !"
Above code is used only in case you are using excel file and need to convert the format from .xls to .xlsx or any other excel formats.
To replace or rename any other file format you can use the below code -
Sub RepRenanyfile()
'FileCopy Source, Destination
FileCopy "C:\Users\Public\Documents\abc.xls", "C:\Users\Public\Documents\def.xlsx"
MsgBox "Macro completed successfully !"
End Sub
7. How to create a function in excel to get the logged in USER Name
Below is the simple function which is used to get the logged in user name
Function GetUserName()
GetUserName = Environ("UserName")
End Function
Once the above function is created in the module, you can write a formula in any cell in excel cell to get the output as logged in user's name -
=GetUserName()
Same macro can be used to give a popup message with the User name, with Msgbox code addition -
Function GetUserName()
GetUserName = Environ("UserName")
MsgBox "Logged in User Name is " & GetUserName
End Function
8. How to execute excel VBA macro on change of specific cell in excel sheet ?
Please follow the below steps to execute the task -
1. We need to go to Excel VBA editor by pressing ALT + F11
2. Double click on sheet which contains our target cell e.g. Sheet1
3. Add the below code to the sheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$3" Then
Call Macro1
End If
End Sub
9. How to select next column or row using excel VBA ?
Below are the codes using which you can move the active selection of cell to the Left, Right, Up or Down.
The code is -
Activecell.Offset(0,0)
The (0,0) in brackets represent the row and column.
Copy and paste the below code in your excel VBA window to test the working of the Offset code.
Sub TestOffset()
' Here the cursor on active sheet moves to next row (moves once), no change in column
ActiveCell.Offset(1, 0).Select
' Here the cursor on active sheet moves to next column (moves once), no change in row
ActiveCell.Offset(0, 1).Select
' Here the cursor on active sheet moves to next row and next column
ActiveCell.Offset(1, 1).Select
' Here the cursor on active sheet moves to upper row (moves once), no change in column
ActiveCell.Offset(-1, 0).Select
' Here the cursor on active sheet moves to previous column (moves once), no change in row
ActiveCell.Offset(0, -1).Select
' Here the cursor on active sheet moves to upper row and previous column
ActiveCell.Offset(-1, -1).Select
'Using the above codes, you can select the columns or rows you need from activecell. Below are some examples -
ActiveCell.Offset(2, 0).Select
ActiveCell.Offset(-1, 2).Select
ActiveCell.Offset(2, 3).Select
End Sub
10. How to Resize and Reset the Comments in Excel using VBA code ?
Below code can be used to reset all comments to default location and size.
Copy paste the below code to any of the Excel VBA module and run it on the active sheet from which you need to reset the comments.
Sub resetcomments()
On Error Resume Next
Application.ScreenUpdating = False
Set allCommentsr = Range("A1").SpecialCells(xlCellTypeComments)
If allCommentsr Is Nothing Then MsgBox "No comments in " & ActiveSheet.Name: GoTo Out
For Each r In allCommentsr
With r.Comment
.Shape.Height = 55 'you can change the number you need to resize
.Shape.Width = 96 'you can change the number you need to resize
End With
r.Comment.Visible = True
r.Comment.Shape.Select True
With Selection
.Interior.ColorIndex = 19 'you can change the colorindex number for using different color
.Font.Bold = False 'you can change to True if you need the text in BOLD
.Font.Size = 8 'you can change the number for font size you need
End With
r.Comment.Visible = False
Next r
Out:
Set allCommentsr = Nothing
MsgBox "All comments are resized and restored to default", vbInformation
Application.ScreenUpdating = True
End Sub
11. Add Start and End row to run the macro, in Excel VBA
Below code can be used to enter the start and end rows to run the macro for specific code. In the below example we have used the Msgbox code to display, the value in the specific cell when the macro is executed.
Sub testrow()
Do While True
line1 = InputBox("ENTER FIRST ROW NUMBER ")
line1 = LTrim(RTrim(line1))
If line1 <> " " Then
Exit Do
End If
Loop
Do While True
line2 = InputBox("ENTER THE LAST ROW NUMBER ")
line2 = LTrim(RTrim(line2))
If line2 <> " " And line2 > 1 Then
Exit Do
End If
Loop
For irow = line1 To line2
icol = 1
a = Cells(irow, icol).Value
MsgBox a ' You can change this line to call your macro or update any other code
Next
MsgBox "completed"
End Sub
12. How to filter the unwanted fields using Auto filter in Excel VBA ?
In below example, there are name of Subjects in column D. Say, we need to delete all rows except Hindi and Marathi subject using excel VBA code.
We can use the below code to execute the deletion task using VBA code.
Sub Keep_HM()
'Deselect the subjects Hindi and Marathi
ActiveSheet.range("D:D").AutoFilter Field:=1, Criteria1:="<>HINDI", _
Operator:=xlAnd, Criteria2:="<>MARATHI"
'Delete the filtered cells
ActiveSheet.range("D1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
'Remove the filter from active sheet
ActiveSheet.AutoFilterMode = False
'Message box pop-up after execution
Msgbox "All subjects except, HINDI and MARATHI are deleted", vbInformation
End Sub
*all codes in this blog are tested on Excel 2010
You can use this code to copy paste the file from one destination to other
e.g. if you require to take backups of the file on daily basis.
Sub Copy_File()
' Declare variables
Dim filename As String, Source As String, Destination As String
'Set values
filename = "abc"
Source = "C:\Test\Desert.jpg"
Destination = "C:\Test\Backup\" & filename & ".jpg"
'File copy code
FileCopy Source, Destination
End Sub
2. Copy and Save file with Date Time Stamp using Excel VBA
You can use below code to copy and save the file with the date and time stamp. This code is same as one of the Copy file code in my previous blog, Just check the changes in the code for Destination.
Sub Copy_File_dt ()
' Declare variables
Dim filename As String, Source As String, Destination As String
'Set values
filename = "abc_"
Source = "C:\Test\Desert.jpg"
Destination = "C:\Test\Backup\" & filename & Format(Now(), "mm_dd_yyyy hh mm AMPM") & ".jpg"
'File copy code
FileCopy Source, Destination
End Sub
3. Remove hyperlinks from Excel, using VBA code
There is a simple one line code to do this. Copy paste below VBA code in excel VBA window and run. All hyperlinks from the active sheet will be removed.
Sub removeallhyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub
you can also use below code:
Sub removeallhyperlinks()
Cells.Hyperlinks.Delete
End Sub
4. Resize all charts to same size using Excel VBA
Below code is used to re-size all the charts to same size from the the active sheet. Copy paste below code in your excel VBA window and run the macro to resize the charts.
Sub ResizeCharts()
' assigning the chart as chartobject, which will identify the charts in the activesheet
Dim chart As ChartObject
For Each chart In ActiveSheet.ChartObjects
With chart.Parent
' change the numbers in the below brackets (5) to change the size of the chart. Here we are using inches to set the chart size.
chart.Height = Application.InchesToPoints(5)
chart.Width = Application.InchesToPoints(10)
End With
Next
MsgBox "All charts in the active sheet are resized !"
End Sub
In above code we are using inches as the measure to change the chart size. Similarly we can use centimeters to re-size the charts.
Sub ResizeCharts()
' assigning the chart as chartobject, which will identify the charts in the activesheet
Dim chart As ChartObject
For Each chart In ActiveSheet.ChartObjects
With chart.Parent
' change the numbers in the below brackets (5) to change the size of the chart. Here we are using inches to set the chart size.
chart.Height = Application.CentimetersToPoints(5)
chart.Width = Application.CentimetersToPoints(10)
End With
Next
MsgBox "All charts in the active sheet are resized !"
End Sub
If you observe in the above code only two lines are changed.
chart.Height = Application.CentimetersToPoints(5)
chart.Width = Application.CentimetersToPoints(10)
5. How to deactivate Alerts while running macro in Excel VBA
You can deactivate the alerts received while running the VBA macro in excel, using below code -
Application.DisplayAlerts = False
You can also deactivate the screen updating while running the macro by using the below code -
Application.ScreenUpdating = False
If your macro is moving from different tabs of worksheet or excel files, the screen will show the details of the macro's step. To disable this we can use the above code.
Below is one of the example, on how this codes can be used -
Sub deactivateAlerts()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("Sheet1").Select
ActiveSheet.Hyperlinks.Delete
Sheets("Sheet2").Select
ActiveSheet.Hyperlinks.Delete
Sheets("Sheet3").Select
ActiveSheet.Hyperlinks.Delete
Sheets("Sheet4").Select
ActiveSheet.Hyperlinks.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Your macro is completed !"
End Sub
The simple code above used, to delete all the hyperlinks in 4 different sheets from your workbook. When the macro is running without the displayalerts = false and screenupdating = false, it will show the steps of macro on screen.
When the displayalerts = false and screenupdating = false is used, you will see the same screen where you are running the macro and you will directly receive the message box stating "Your macro is completed !"
Don't forget to add the below lines before End Sub of your code.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
These lines will reset the Alerts and Screen updating.
6. How to Open, Save As and Replace file using Excel VBA macro
Below is the macro which is used to Open, Save As and Replace the abc.xls file saved on location - C:\Users\Public\Documents\abc.xls
Sub ReplaceRename()
' Disable alerts
Application.DisplayAlerts = False
'Open file
Workbooks.Open filename:= _
"C:\Users\Public\Documents\abc.xls"
'Save As and replace or Rename file. In below case we are saving the .xls file in .xlsx format. In case you need to rename the file, just change the abc.xlsx to newname.xlsx in below code
ActiveWorkbook.SaveAs filename:= _
"C:\Users\Public\Documents\abc.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
'Close workbook
ActiveWindow.Close
'Reset alerts
Application.DisplayAlerts = True
'Message pop-up
Msgbox "Macro completed successfully !"
'Exit Macro
End Sub
If we run the macro with the removing the display alerts code on line 2 of code, then we will receive below pop-up message. With the above code we will receive only below message -
"Macro completed successfully !"
Above code is used only in case you are using excel file and need to convert the format from .xls to .xlsx or any other excel formats.
To replace or rename any other file format you can use the below code -
Sub RepRenanyfile()
'FileCopy Source, Destination
FileCopy "C:\Users\Public\Documents\abc.xls", "C:\Users\Public\Documents\def.xlsx"
MsgBox "Macro completed successfully !"
End Sub
7. How to create a function in excel to get the logged in USER Name
Below is the simple function which is used to get the logged in user name
Function GetUserName()
GetUserName = Environ("UserName")
End Function
Once the above function is created in the module, you can write a formula in any cell in excel cell to get the output as logged in user's name -
=GetUserName()
Same macro can be used to give a popup message with the User name, with Msgbox code addition -
Function GetUserName()
GetUserName = Environ("UserName")
MsgBox "Logged in User Name is " & GetUserName
End Function
8. How to execute excel VBA macro on change of specific cell in excel sheet ?
Please follow the below steps to execute the task -
1. We need to go to Excel VBA editor by pressing ALT + F11
2. Double click on sheet which contains our target cell e.g. Sheet1
3. Add the below code to the sheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$3" Then
Call Macro1
End If
End Sub
4. If any value in cell E3 of Sheet1 is updated or changed the Macro1 will be executed
5. Insert new module and Add the below code to with name Macro1
Sub Macro1()
MsgBox "The value entered in Range E3 is " & Range("E3").Value
End Sub
6. Now try changing the value in cell E3 of Sheet1, the Macro1 will be executed and you will get a messagebox pop-up window
9. How to select next column or row using excel VBA ?
Below are the codes using which you can move the active selection of cell to the Left, Right, Up or Down.
The code is -
Activecell.Offset(0,0)
The (0,0) in brackets represent the row and column.
Copy and paste the below code in your excel VBA window to test the working of the Offset code.
Sub TestOffset()
' Here the cursor on active sheet moves to next row (moves once), no change in column
ActiveCell.Offset(1, 0).Select
' Here the cursor on active sheet moves to next column (moves once), no change in row
ActiveCell.Offset(0, 1).Select
' Here the cursor on active sheet moves to next row and next column
ActiveCell.Offset(1, 1).Select
' Here the cursor on active sheet moves to upper row (moves once), no change in column
ActiveCell.Offset(-1, 0).Select
' Here the cursor on active sheet moves to previous column (moves once), no change in row
ActiveCell.Offset(0, -1).Select
' Here the cursor on active sheet moves to upper row and previous column
ActiveCell.Offset(-1, -1).Select
'Using the above codes, you can select the columns or rows you need from activecell. Below are some examples -
ActiveCell.Offset(2, 0).Select
ActiveCell.Offset(-1, 2).Select
ActiveCell.Offset(2, 3).Select
End Sub
10. How to Resize and Reset the Comments in Excel using VBA code ?
Below code can be used to reset all comments to default location and size.
Copy paste the below code to any of the Excel VBA module and run it on the active sheet from which you need to reset the comments.
Sub resetcomments()
On Error Resume Next
Application.ScreenUpdating = False
Set allCommentsr = Range("A1").SpecialCells(xlCellTypeComments)
If allCommentsr Is Nothing Then MsgBox "No comments in " & ActiveSheet.Name: GoTo Out
For Each r In allCommentsr
With r.Comment
.Shape.Height = 55 'you can change the number you need to resize
.Shape.Width = 96 'you can change the number you need to resize
End With
r.Comment.Visible = True
r.Comment.Shape.Select True
With Selection
.Interior.ColorIndex = 19 'you can change the colorindex number for using different color
.Font.Bold = False 'you can change to True if you need the text in BOLD
.Font.Size = 8 'you can change the number for font size you need
End With
r.Comment.Visible = False
Next r
Out:
Set allCommentsr = Nothing
MsgBox "All comments are resized and restored to default", vbInformation
Application.ScreenUpdating = True
End Sub
11. Add Start and End row to run the macro, in Excel VBA
Below code can be used to enter the start and end rows to run the macro for specific code. In the below example we have used the Msgbox code to display, the value in the specific cell when the macro is executed.
Sub testrow()
Do While True
line1 = InputBox("ENTER FIRST ROW NUMBER ")
line1 = LTrim(RTrim(line1))
If line1 <> " " Then
Exit Do
End If
Loop
Do While True
line2 = InputBox("ENTER THE LAST ROW NUMBER ")
line2 = LTrim(RTrim(line2))
If line2 <> " " And line2 > 1 Then
Exit Do
End If
Loop
For irow = line1 To line2
icol = 1
a = Cells(irow, icol).Value
MsgBox a ' You can change this line to call your macro or update any other code
Next
MsgBox "completed"
End Sub
12. How to filter the unwanted fields using Auto filter in Excel VBA ?
In below example, there are name of Subjects in column D. Say, we need to delete all rows except Hindi and Marathi subject using excel VBA code.
We can use the below code to execute the deletion task using VBA code.
Sub Keep_HM()
'Deselect the subjects Hindi and Marathi
ActiveSheet.range("D:D").AutoFilter Field:=1, Criteria1:="<>HINDI", _
Operator:=xlAnd, Criteria2:="<>MARATHI"
'Delete the filtered cells
ActiveSheet.range("D1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
'Remove the filter from active sheet
ActiveSheet.AutoFilterMode = False
'Message box pop-up after execution
Msgbox "All subjects except, HINDI and MARATHI are deleted", vbInformation
End Sub
*all codes in this blog are tested on Excel 2010
No comments:
Post a Comment