Excel VBA WorkSheet.Copy After Excel 2013

Question

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.

What @glh said. You already have wst as a Worksheet variable. Just use that for the After argument :) — David Zemens
wst.copy after:=wst and wst.copy after:=Sheets("Sheet1") produces the same results. My new sheet get place after the hidden sheet and not directly after "Sheet1" — Jonathan

Sakir SEN Sakir SEN · Accepted Answer · 2013-05-03T08:56:29

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

End Sub

Excel VBA WorkSheet.Copy After Excel 2013

2 Answers