macro for selecting last 3 sheets in a workbook

[jl]

I have a workbook that has numerous worksheets (appr. 12). I would
like to create a macro that automatically selects the last 3 sheets in
the workbook everytime I run it), and copies them to a brand new
workbook. Any ideas?

J-

 

[JS]

this should it...

Private Sub Workbook_Open()
Dim n As Integer
Dim wkbnew As Workbook
Dim cnt As Integer

cnt = ThisWorkbook.Worksheets.Count

If cnt >= 3 Then
Set wkbnew = Workbooks.Add
Do Until n > 2
ThisWorkbook.Worksheets(cnt - n).Copy
Before:=wkbnew.Worksheets(1)
n = n + 1
Loop
End If

End Sub

 

[Don P]

I am sure there are a more eloquent ways, but this works for me:

Sub LastThree()
Dim ws As Worksheet
Dim myarray(1 To 3)
a = ActiveWorkbook.Worksheets.Count
counter = 0
For Each ws In ActiveWorkbook.Worksheets
counter = counter + 1
If counter >= a - 2 Then
i = i + 1
myarray(i) = ws.Name
Else
End If
Next
Sheets(myarray).Copy before:=Workbooks("book3").Sheets(1)
End Sub

Don Pistulka

 

[Don P]

I overlooked the fact that you need to create a new workbook. Try this one:

Sub LastThree()
Dim ws As Worksheet
Dim myarray(1 To 3)
a = ActiveWorkbook.Worksheets.Count
counter = 0
For Each ws In ActiveWorkbook.Worksheets
counter = counter + 1
If counter >= a - 2 Then
i = i + 1
myarray(i) = ws.Name
Else
End If
Next
Workbooks.Add
ThisWorkbook.Sheets(myarray).Copy before:=ActiveWorkbook.Sheets(1)
End Sub


Don Pistulka