Macro to copy active sheet and sheet to the right of it to new workbook

[tonywatsonhelp]

Hi Everyone,

I need a macro that can copy the activesheet and the sheet to the right of it and create a new workbook

Please help if you can
thanks

Tony

 

[MARK858]

On a basic level maybe...

Sub CreateFile()

    Dim wb As Workbook, wbNew As Workbook
    Dim sh As Worksheet
    Dim shIndex As Long, i As Long

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With


    Set wb = ThisWorkbook
    shIndex = ActiveSheet.Index

    Workbooks.Add
    Set wbNew = ActiveWorkbook

    For i = shIndex To shIndex + 1
        wb.Sheets(i).Copy After:=wbNew.Sheets(wbNew.Sheets.Count)
    Next

    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With

End Sub

 

[Norie]

Try this.

Sheets(Array(ActiveSheet.Name, Sheets(ActiveSheet.Index+1).Name)).Copy

 

[tonywatsonhelp]

Thanks Norie and Mark,

Both great ideas, I'll play around and see which one suits me best


Thanks

Tony