CODEWITHSUNDEEP
vbaexcel
user3688713
user3688713

Reputation: 41

Running a macro on a different sheet using VBA

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.

Upvotes: 0

Views: 28261

Answers (2)

Kory
Kory

Reputation: 318

You could start off the meat of your commands by saying sheets("Buttons").select

Later in your code, it says you're working with sheet "current". Change that "current" to "buttons" and it should work fine!

Upvotes: -1

Gareth
Gareth

Reputation: 5243

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

Upvotes: 4

Related Questions