Running a macro on a different sheet using VBA

Question

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.

Solution

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

Comments

[Denise Skidmore]

Selecting Sheets always slows down VBA. Whenever possible do your work via sheet name or reference and avoid making changes to the display during your calculations unless you specifically want the user to see what is happening while you do it.

[user3688713]

Hey I've changed Worksheet("Current") to Worksheet("Button") because I want to run the macro on the "Buttons" sheet to change the format on the "Current" sheet. However, when I change the sheet from "Current" to "Button" on the code, it ends up formatting the "Buttons" sheet. How can I get it so that it formats the "Current" sheet while pressing the button on the "Buttons" sheet? Thanks.

[Kory]

Using Application.Screenupdating = False solves that issue. I was just providing an alternative solution that may have been a little easier to implement. Certainly not worth a downvote because it DOES solve the problem. It just may not be the most efficient.

[Gareth]

So to clarify, you want to run the macro to format Worksheet("Current")? How are you running the the macro - by clicking a button on Worksheet("Button")?