Tag Archives: macro

Excel Macro/VB Script – Common Commands

This is a list of common Excel Macro/VB script commands that I used daily.  Seen something & wonder how it was done? Check these out!

  • Run a Macro from within a macro:

    Application.Run “‘file_name.xlsm’!name_of_macro”

    or

    Application.Run “name_of_macro”

  • Switch to a different sheet:

    Sheets(“Example_Sheet”).Select

  • Have a message box pop up with an “OK” button:

    MsgBox “Starting Update ALL – Wait for Done Message!”

  • Turn Off Auto Cal:

    Application.Calculation = xlCalculationManual

  • Turn On Auto Cal:

    Application.Calculation = xlCalculationAutomatic

  • Select all cells that currently have data from a starting point “F1”:

    Range(“F1”).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select

  • Clear the clipboard:

    Application.CutCopyMode = False

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

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

  • Open a workbook with a filenamelocation that is configured from another cell value:

    ChDir “C:Usersuser_namefolder_onefolder_two” Workbooks.Open Filename:= _”C:Usersuser_namefolder_namePart_of_file_name1″ & variable & “.xls”

  • Clear the contents of the selected cells:

    Selection.ClearContents

  • Switch to a different open workbook:

    Windows(“file name.xlsm”).Activate

  • Paste Values only from current clipboard contents:

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

  • 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