select & copy last sheet

[lior03]

hello
the folllowing code select the last sheet in the workbook.
i want to be able to copy the last sheet once it was selected and copy it to another sheet which will be placed last in the workbook.

Dim i As Integer
For i = Sheets.Count To 1 Step -1 ' Counting backwards
If Sheets(i).Visible And TypeName(Sheets(i)) <> "Module" Then
Sheets(i).Select
Exit Sub
End If
Next i

thanks

[lior03]

what about...........

Sub copy_last()
Application.ScreenUpdating = False
Dim i As Integer
For i = Sheets.Count To 1 Step -1
If Sheets(i).Visible And TypeName(Sheets(i)) <> "Module" Then
Sheets(i).Copy After:=Sheets(Sheets.Count)
Exit Sub
End If
Next i
Application.ScreenUpdating = True
End Sub

[lucas]

That seems to work fine moshe. If you wanted to name the new sheet you could put that value in cell A1 of the sheet being copied and use this:


Sub copy_last()
Application.ScreenUpdating = False
Dim i As Integer
For i = Sheets.Count To 1 Step -1
If Sheets(i).Visible And TypeName(Sheets(i)) <> "Module" Then
Sheets(i).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Range("A1").Value
Exit Sub
End If
Next i
Application.ScreenUpdating = True
End Sub

[lucas]

If you wanted to name your sheets by page number you could use something like this:


Sub copy_last()
Application.ScreenUpdating = False
Dim i As Integer
Dim TotalSheets As Integer

For i = Sheets.Count To 1 Step -1
If Sheets(i).Visible And TypeName(Sheets(i)) <> "Module" Then
Sheets(i).Copy After:=Sheets(Sheets.Count)
TotalSheets = Worksheets.Count - 1
ActiveSheet.Name = "Page " & TotalSheets + 1
Exit Sub
End If
Next i
Application.ScreenUpdating = True
End Sub