macro for selecting last 3 sheets in a workbook

J

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-
 
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
 
D

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top