VBA to Copy last 3 Spreadsheets to a new Workbook

[avd88]

Hello, I am trying to create a macro which copies and pastes the last 3 Spreadsheets of a Workbook into a new one. Anyone has any ideas of how this can be accomplished? What I've tried so far does not work.

Thank you in advance!

 

[JLGWhiz]

This is the general idea

Sub t()
Dim Oldwb As Workbook, Newwb As Workbook
Set Oldwb = Workbooks(1)
Set Newwb = Workbooks(2)
    For i = 1 To 3
        Oldwb.Sheets(Oldwb.Sheets.Count - i).Copy After:=Newwb.Sheets(Sheets.Count)
    Next
End Sub

 

[dmt32]

hi,
another way maybe:

Sub CopySheets()


    Dim wbNew As Workbook


    Set wbNew = Workbooks.Add(1)
    With ThisWorkbook
        .Worksheets(Array(.Sheets.Count, .Sheets.Count - 1, .Sheets.Count - 2)).Copy _
                                            After:=wbNew.Sheets(wbNew.Sheets.Count)
    End With


End Sub

Dave

 

[avd88]

Thank you very much!

I ended up using ideas from both responses this is my code and it works:

'COPY AND SAVE'

Dim Oldwb As Workbook, Newwb As Workbook
Set Oldwb = Workbooks("Saxco COSR Test 25.xlsm")
Set Newwb = Workbooks.Add(1)




    For i = 1 To 3
        Oldwb.Sheets(Oldwb.Sheets.Count + 1 - i).Copy Before:=Newwb.Sheets(Sheets.Count)
    Next


Oldwb.Close SaveChanges:=True

 

[JLGWhiz]

thanks for the feedback,
Regards, JLG