☰

宏运行后返回选定页面

tag-icon • tag-icon microsoft-excel macros
宏运行后返回选定页面

我有下面的宏，它将两个单元格复制到不同的页面，然后将它们打印到标签上，

Sub Tag_75()
ActiveCell.Resize(1, 1).Copy Worksheets("Sheet1").Range("A1")

ActiveCell.Offset(, 1).Resize(1, 1).Copy Worksheets("Sheet1").Range("A2")

'Worksheet("Sheet1").Range("A1:A2").Borders.LineStyle = xlNone
Sheets("Sheet1").Select
    Range("A1:A2").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Worksheets("Sheet1").Range("A2").WrapText = True
Worksheets("Sheet1").Range("A1:A2").Font.Size = 22
Worksheets("Sheet1").Range("A1:A2").ShrinkToFit = True

Dim STDprinter As String

STDprinter = Application.ActivePrinter
'Application.ActivePrinter = "\\SBS2011\Xerox WorkCentre 7830 on Ne06:"
Application.ActivePrinter = "\\JCSP55J\DYMO LabelWriter 450 (Copy 2) on Ne05:"
ActiveSheet.PrintOut
Application.ActivePrinter = STDprinter

End Sub

有人可以帮助我返回运行宏时的原始工作表吗？

谢谢

答案1

记住你来自哪里：

Sub Tag_75()
dim sh as worksheet
Set sh = Activesheet

ActiveCell.Resize(1, 1).Copy Worksheets("Sheet1").Range("A1")

ActiveCell.Offset(, 1).Resize(1, 1).Copy Worksheets("Sheet1").Range("A2")

'Worksheet("Sheet1").Range("A1:A2").Borders.LineStyle = xlNone
Sheets("Sheet1").Select
    Range("A1:A2").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Worksheets("Sheet1").Range("A2").WrapText = True
Worksheets("Sheet1").Range("A1:A2").Font.Size = 22
Worksheets("Sheet1").Range("A1:A2").ShrinkToFit = True

Dim STDprinter As String

STDprinter = Application.ActivePrinter
'Application.ActivePrinter = "\\SBS2011\Xerox WorkCentre 7830 on Ne06:"
Application.ActivePrinter = "\\JCSP55J\DYMO LabelWriter 450 (Copy 2) on Ne05:"
ActiveSheet.PrintOut
Application.ActivePrinter = STDprinter

sh.Activate
End Sub

答案2

尝试完全不使用Selection。这很少有必要。例如（未调试）：

Sub Tag_75()
ActiveCell.Resize(1, 1).Copy Worksheets("Sheet1").Range("A1")

ActiveCell.Offset(, 1).Resize(1, 1).Copy Worksheets("Sheet1").Range("A2")

'Worksheet("Sheet1").Range("A1:A2").Borders.LineStyle = xlNone
with Sheets("Sheet1").Range("A1:A2")
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
End With

Worksheets("Sheet1").Range("A2").WrapText = True
Worksheets("Sheet1").Range("A1:A2").Font.Size = 22
Worksheets("Sheet1").Range("A1:A2").ShrinkToFit = True

Dim STDprinter As String

STDprinter = Application.ActivePrinter
'Application.ActivePrinter = "\\SBS2011\Xerox WorkCentre 7830 on Ne06:"
Application.ActivePrinter = "\\JCSP55J\DYMO LabelWriter 450 (Copy 2) on Ne05:"
ActiveSheet.PrintOut
Application.ActivePrinter = STDprinter

End Sub

相关内容