How to copy consecutive sheets in excel using VBA

1
votes

I searched multiple posts about how to copy a sheet to another workbook. In all instances, the sheets names are known.

If I am on the current sheet, how can I create a vba that copy the current sheet and the next 2 consecutive sheets into a new workbook. The sheets' names change every week, so the program needs to pick the current active sheet and the next 2 sheets only. Thank you for your help.

1
excelvbacopypaste
Possible duplicate of How to copy sheets to another workbook using vba? – Ruchi

1 Answers

0
votes

Below code would suffice your requirement. Just make sure you are at the 3rd last sheet of workbook when running it.

Option Explicit
Sub copyConsecutiveSheets()
Dim x As Integer
Dim k As Integer
Dim j As Integer
Dim b As String

x = 1
k = ActiveSheet.Index

ThisWorkbook.Sheets(k).COPY
For j = 1 To 2
b = ActiveWorkbook.Name
ThisWorkbook.Sheets(k + j).COPY After:=Workbooks(b).Worksheets(x)
x = x + 1
Workbooks(b).Activate

Next

End Sub