I also would like to know what I need to change in order for the sheet to
return to the 'new sheet' instead of going back to the one it was copied from.

Any help you could give would be much appreciated. Thank you!

"MGC" wrote:

I have found the following macro:

Sub Create_Separate_Sheet_For_Each_HPageBreak()
Dim HPB As HPageBreak
Dim RW As Long
Dim PageNum As Long
Dim Asheet As Worksheet
Dim Nsheet As Worksheet
Dim Acell As Range

'Sheet with the data, you can also use Sheets("Sheet1")
Set Asheet = ActiveSheet

If Asheet.HPageBreaks.Count = 0 Then
MsgBox "There are no HPageBreaks"
Exit Sub
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'When the macro is ready we return to this cell on the ActiveSheet
Set Acell = Range("A1")

'Because of this bug we select a cell below your data
'http://support.microsoft.com/default.aspx?scid=kb;en-us;210663
Application.Goto Asheet.Range("A" & Rows.Count), True

RW = 1
PageNum = 1

For Each HPB In Asheet.HPageBreaks
'Add a sheet for the page
With Asheet.Parent
Set Nsheet = Worksheets.Add(after:=.Sheets(.Sheets.Count))
End With

'Give the sheet a name
On Error Resume Next
Nsheet.Name = "Page " & PageNum
If Err.Number 0 Then
MsgBox "Change the name of : " & Nsheet.Name & " manually"
Err.Clear
End If
On Error GoTo 0

'Copy the cells from the page into the new sheet
With Asheet
.Range(.Cells(RW, "A"), .Cells(HPB.Location.Row - 1, "K")).Copy _
Nsheet.Cells(1)
End With
' If you want to make values of your formulas use this line also
' Nsheet.UsedRange.Value = Nsheet.UsedRange.Value

RW = HPB.Location.Row
PageNum = PageNum + 1
Next HPB

Asheet.DisplayPageBreaks = False
Application.Goto Acell, True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


However, when the macro is run, the formatting is different. Is there any
way to retain the format from sheet to sheet?





"MGC" wrote:

Here we go:

I have several workbooks which contain daily timesheets for my employees.
Because the names of these employees seldom change from day to day, I would
like to know if there is a macro I could use to copy the previous days' tab
to a new tab which I could later rename. The tabs are named according to the
job number (the date followed by a letter).

This is a huge project as I currently have 25+ jobs (workbooks) which need
time entered daily. Any help you could provide would be great. Thank you!