Excel VBA WorkSheet.Copy After Excel 2013

 

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"

Next run this VBA Code: 

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.

Get Handle on last worksheet copied by 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.

现有的代码可以添加

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

End Sub

The index property is zero base, unless specified elsewhere. Sheets property is base of one. You need to either:

  • Add 1 to the index, not preferred.
  • Reference the .name property, better.
  • Reference the sheet variable you have Ie Wst.copy after:=wst, best.
  • Or move the hidden sheet to after the new one.
    • 链接地址: http://www.djcxy.com/p/35760.html

    上一篇: 使用NPOI将图像插入Excel文件

    下一篇: 在Excel 2013之后的Excel VBA WorkSheet.Copy