Excel VBA WorkSheet.Copy After Excel 2013
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16347036/
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
Excel VBA WorkSheet.Copy After Excel 2013
提问by Jonathan
I want to copy a worksheet and place it DIRECTLY after another sheet. But I having trouble when there are hidden sheets involved.
我想复制一个工作表并将其直接放在另一张工作表之后。但是当涉及隐藏的工作表时，我遇到了麻烦。
Using Excel 2013 I open a new workbook. Add a Sheet after "Sheet1". Rename it "HiddenSheet2" Add a Sheet after "HiddenSheet2". Rename new sheet "Sheet3" So I have "Sheet1" - "HiddenSheet1" - "Sheet3" Then hide "HiddenSheet1"
我使用 Excel 2013 打开一个新工作簿。在“Sheet1”之后添加一个 Sheet。将其重命名为“HiddenSheet2” 在“HiddenSheet2”之后添加一个工作表。重命名新工作表“Sheet3”所以我有“Sheet1”-“HiddenSheet1”-“Sheet3”然后隐藏“HiddenSheet1”
Next run this VBA Code:
接下来运行此 VBA 代码：
Sub test()
Dim ws1 AS Worksheet
Set wst = Sheets("Sheet1")
wst.Copy After:=Sheets(wst.Index)
End Sub
The inserted sheet get placed after "HiddenSheet2" and not after "Sheet1". I found that the copied sheet becomes the active sheet.
插入的工作表放置在“HiddenSheet2”之后而不是“Sheet1”之后。我发现复制的工作表成为活动工作表。
Get Handle on last worksheet copied by Worksheet.Copy
获取 Worksheet.Copy 复制的最后一个工作表的句柄
VBA Copy Sheet to End of Workbook (with Hidden Worksheets)
But my problem is I need the sheet to remain in a specific order.
但我的问题是我需要工作表保持特定顺序。
Thanks
谢谢
Edit:
编辑：
Sub test()
Dim ws1 AS Worksheet
Set wst = Sheets("Sheet1")
wst.Copy After:=Sheets(wst.Index)
ThisWorkbook.ActiveSheet.Move After:=Sheets(wst.Index)
End Sub
This doesn't get the new sheet directly after "Sheet1" either.
这也不会在“Sheet1”之后直接获得新工作表。
回答by Sakir SEN
Existing code can be added
可以添加现有代码
Sub test()
Dim ws1 AS Worksheet
Set wst = Sheets("Sheet1")
wst.Copy After:=Sheets(wst.Index)
ThisWorkbook.ActiveSheet.Move After:=Sheets(wst.Index)
If (ActiveSheet.Index - wst.Index - 1) <> 0 Then Sheets(wst.Index + 1).Visible = True ActiveSheet.Move After:=wst Sheets(wst.Index + 2).Visible = False End If
If (ActiveSheet.Index - wst.Index - 1) <> 0 Then Sheets(wst.Index + 1).Visible = True ActiveSheet.Move After:=wst Sheets(wst.Index + 2).Visible = False End If
End Sub
回答by glh
The index property is zero base, unless specified elsewhere. Sheets property is base of one. You need to either:
除非在别处指定，否则索引属性为零基数。Sheets 属性是 1 的基础。您需要：
- Add 1 to the index, not preferred.
- Reference the .name property, better.
- Reference the sheet variable you have I.e. Wst.copy after:=wst, best.
- Or move the hidden sheet to after the new one.
- 将 1 添加到索引中，不是首选。
- 引用 .name 属性，更好。
- 引用您拥有的工作表变量，即 Wst.copy after:=wst，最好。
- 或者将隐藏的工作表移到新工作表之后。
