Copy worksheets after a specific worksheet

Question

I have a workbook that has a dynamic number of worksheets within it. The relevant worksheets I want to extract always occur after the worksheet "Sheet 3". The problem is the relevant worksheets have dynamic names e.g. "Apple-A", "Orange-1", etc. and I wanted to create a code that will always copy every sheet after "Sheet 3" regardless of their naming convention.

To this point I have only been able to copy sheets when specifically calling its name e.g.

total = Workbooks("Fruits.xlsm").Worksheets.Count
Workbooks("Fruits").Worksheets("Apple-A").Copy_after:=Workbooks("Fruits.xlsm").Worksheets(total)

Is there a method for copying a number of sheets to a new workbook after a certain worksheet name is called?

If you want to use the third sheet, no matter the name, you can use Workbooks("Fruits.xlsm").Sheets(3).Range("A1")... which will do something with A1 from the third (via index) worksheet. So if you look at the worksheet tabs, from left to right, it will choose the third one. — BruceWayne
But the sheet called "Sheet 3" may not be the third sheet .... — teylyn

FloLie FloLie · Accepted Answer · 2018-04-03T20:35:24

Dim wb As Workbook
Set wb = Workbooks("Fruits.xlsm")
Dim ws As Worksheet

Set ws = wb.Sheets(wb.Sheets("Sheet 3").Index +1)
ws.Copy

Note:

Defining the Workbook you are operating in is always a good Idea. Next lets take a closer look at Worksheet Objects.

With Workbook.Sheets(index/name) you can access a worksheet at a certain position = index e.g 1 for the first sheet in the workbook or by its name.

Now you are looking for a sheet next to a sheet of which you know the name.

wb.Sheets("Sheet 3").Index returns the position of Sheet 3. Now you want the sheet to its right. This will be at the position wb.Sheets("Sheet 3).Index +1.

EDIT:

I think the question has been edited to copy "all sheets following 'Sheet 3'"

The code for this:

Dim wbSource As Workbook
Dim wbTarget As Workbook
Set wbSource = Workbooks("Fruits.xlsm")
Dim ws As Worksheet
Set wbTarget = Workbooks.Add

indexOfSheet3 = wbSource.Sheets("Sheet 3").Index

For i = indexOfSheet3 + 1 To wbSource.Sheets.Count
    wbSource.Sheets(i).Copy After:= wbTarget.Sheets(wbTarget.Sheets.Count)
Next i

'Delete the defualt sheet
Application.DisplayAlerts = False
wbTarget.Sheets(1).Delete
Application.DisplayAlerts = True

Copy worksheets after a specific worksheet

2 Answers