Run a macro on a different sheet using VBA

advertisements

I am having trouble trying to run a macro, that I recorded on the "Current" sheet, on my "Buttons" sheet. These are 2 separate sheets and I want to run the macro on the "Buttons" sheet. This is the code I have so far:

Sub FormatCurrentSheet()

Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Bold = True
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=6
With Selection.Font
    .Name = "Calibri"
    .Size = 9
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontMinor
End With
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Rows.AutoFit
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Columns.AutoFit
Columns("H:H").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Current").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Current").AutoFilter.Sort.SortFields.Add Key:= _
    Range("H1:H800"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
With ActiveWorkbook.Worksheets("Current").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

How can I change this around so that it works on the "Buttons" sheet when I click the button that this macro is assigned to. I have a feeling I might have to use ActiveSheet. Thank you for your help and feel free to ask questions.


You need to specify the sheet when using Range like so:

Worksheets("SheetName").Range("A1").Select

A more elegant solution is the use With which you can use like so:

With Worksheets("SheetName")
    .Range("A1").Select
    .Range("A2").Select
End With