使用 VBA 在不同的工作表上运行宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24064018/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Running a macro on a different sheet using VBA
提问by user3688713
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:
我在尝试运行记录在“当前”工作表和“按钮”工作表上的宏时遇到问题。这些是 2 个单独的工作表，我想在“按钮”工作表上运行宏。这是我到目前为止的代码：
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.
当我单击此宏分配给的按钮时，如何更改它以便它在“按钮”表上工作。我有一种感觉，我可能不得不使用ActiveSheet. 感谢您的帮助，并随时提出问题。
回答by Gareth
You need to specify the sheet when using Rangelike so:
使用时需要指定工作表，Range如下所示：
Worksheets("SheetName").Range("A1").Select
A more elegant solution is the use Withwhich you can use like so:
一个更优雅的解决方案是使用With，您可以像这样使用：
With Worksheets("SheetName")
.Range("A1").Select
.Range("A2").Select
End With
回答by Kory
You could start off the meat of your commands by saying sheets("Buttons").select
你可以通过说 sheet("Buttons").select 来开始你的命令的内容
Later in your code, it says you're working with sheet "current". Change that "current" to "buttons" and it should work fine!
在您的代码后面，它说您正在使用“当前”工作表。将“当前”更改为“按钮”，它应该可以正常工作！
