The Best Web Host!!

Disable/Stop Screen changes while a macro is running in excel

Do you want to make your macro pause the screen so that you do not see what is happening till it’s done? This will prevent seeing all the jumping between sheets & scrolling.

Application.ScreenUpdating = False

 

 

Cleaning List of Names in Excel with a formula

Take A1 and remove “*” located at the end.

=IF(ISERROR(IF(FIND(“,”,A1)>0,A1)),A1,IF(FIND(“,”,A1)>0,IF(ISERROR(FIND(“*”,A1)),A1,LEFT(A1,FIND(“*”,A1)-1))))

If A1 = “Doe, Jane*” it will return “Doe, Jane”

Enable/Disable alerts in a macro

Enable/Disable alerts in a macro

Disable

Application.DisplayAlerts = False

Enable

Application.DisplayAlerts = True

Run a Macro when a workbook opens (must be placed in the “ThisWorkbook” Object)

Run a Macro when a workbook opens (must be placed in the “ThisWorkbook” Object):

Private Sub Workbook_Open() MsgBox “Auto Calculation has been disabled.” Application.Calculation = xlCalculationManual End Sub

Paste Values only from current clipboard contents in a macro

Paste Values only from current clipboard contents:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False

Switch to a different open workbook in a macro

Switch to a different open workbook:

Windows(“file name.xlsm”).Activate

Clear the contents of the selected cells in a macro

Clear the contents of the selected cells:

Selection.ClearContents

Open a workbook with a filename\location that is configured from another cell value

Open a workbook with a filename\location that is configured from another cell value:

ChDir “C:\Users\user_name\folder_one\folder_two” Workbooks.Open Filename:= _”C:\Users\user_name\folder_name\Part_of_file_name1″ & variable & “.xls”

Set a variable to a value in a cell(X=Row,Y=Column) in a Macro

Set a variable to a value in a cell(X=Row,Y=Column):

variable_name = Worksheets(“sheet_name”).Cells(X, Y).Value

Clear the clipboard in a Macro

Clear the clipboard:

Application.CutCopyMode = False