CODEWITHSUNDEEP
vbaexcel
blair b
blair b

Reputation: 13

Copy worksheets after a specific worksheet

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?

Upvotes: 0

Views: 1833

Answers (2)

FloLie
FloLie

Reputation: 1840

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

Upvotes: 2

DisplayName
DisplayName

Reputation: 13386

I’d go the opposite way: copy all worksheets to a new workbook and delete its first three ones:

Workbooks("Fruits.xlsm").Worksheets.Copy

Dim i As Long
Application.DisplayAlerts = False
For i = 3 To 1 Step - 1
    Worksheets(i).Delete
Next
Application.DisplayAlerts = True

Upvotes: 0

Related Questions