Need A Macro To Copy Previous Tab to New Tab

[MGC]

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!

 

[MGC]

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]

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!

 

[JLatham]

That macro would take some work to fine tune to what you may need. I think
you're probably not interested in page breaks at all for one thing.

Please explain in a little more detail what needs to be done. Picture
yourself doing the job by hand. Try answering these questions in your
description:

Will you need/want to process more than one workbook at a time? If yes, are
all of the workbooks in the same folder?

Do you simply need to copy one sheet (last one created) in each workbook to
become another new sheet, complete with all information? Then at some future
point you'd rename those new sheets (one in each workbook) and clear out any
unneeded information?

Would it be better if you had an 'empty' timesheet to use as the new sheet
so you wouldn't have to clear out existing information, but could instead
simply update some entries (time period for timesheet, for example) and use
as the new timesheet?

These answers and your description can help someone provide a solution for
you.

 

[JLatham]

This may be all you need? I'm not sure yet. This code simply copies the
current 'active' (selected) worksheet, placing the copy immediately behind
the original, then returns to the original sheet.

To place the code into your workbook, open it and press [Alt]+[F11] to get
into the VB Editor. Choose Insert | Module from the menu and then copy and
paste the code below into the module provided. Close the VB Editor. Tools |
Macro | Macros will get you to the macro to run it.

You could actually put this into a workbook of its own, then have it open
while you open the others and pick the sheet in each needing to be copied,
and run the macro from that other workbook. It'll show up in the list of
available macros with the name of the book it is in in front of it, but it
will operate using the selected workbook and worksheet.

Sub DuplicateCurrentSheet()
'
'Copies the currently selected sheet
'placing the copy immediately behind it
'and then returning to the original sheet
Dim thisSheet As String

thisSheet = ActiveSheet.Name
Worksheets(thisSheet).Copy after:=Sheets(thisSheet)
Worksheets(thisSheet).Activate ' back to original sheet
End Sub

 

[MGC]

The macro in my earlier e-mail works fine. However, here is a case scenario:

I have a workbook for each job we have going on in one of our plants which
contains the daily timesheets for that job. Because the employees (and
sometimes the hours) change infrequently and to make the process of copying
data easier for the user (who by the way is not real familiar with Excel) I
would like a macro which would copy the previous days' sheet to a new tab
(preferably behind the last one). The user would then be able to modify any
data necessary and rename the tab.

The earlier macro did just what I wanted it to do...however, I need to know
what I need to change in order to keep the formatting the same from tab to
tab (this is a standard form which cannot be submitted in another
format)...and...once the macro is run to have it return to the new sheet (the
current macro returns you to the previous day).

Any help you could give would be great!

 

[JLatham]

First, to remain on the new sheet, remove or comment out, the line that reads
Application.Goto Acell, True
to comment it out, simply add a ' in front of Application, as:
'Application.Goto Acell, True
or replace that with this line:
Nsheet.Range("A1").Select ' go to new sheet.

Let me request that you try this (slight modification of what I put up
earlier)

Sub DuplicateCurrentSheet()
'
'Copies the currently selected sheet
'placing the copy at the 'end' of the workbook

Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count)
'remains on the new sheet
End Sub

Then tell me what is wrong with it - i.e. copies too much information?
Something else?

Your code is doing copies by cells (rows/columns) and so when it pastes it
doesn't paste anything except the values, not the formatting. With this
simple "duplicate the sheet" line of code, an exact duplicate of the
worksheet with all formatting and data will be created.

If the above that I provided is insufficient, try (I haven't completely
tested, so test on copy of your workbook) replacing the section that reads:

With Asheet
.Range(.Cells(RW, "A"), .Cells(HPB.Location.Row - 1, "K")).Copy _
Nsheet.Cells(1)
End With
with this code
Asheet.Range(.Cells(RW, "A"), .Cells(HPB.Location.Row - 1, "K")).Copy
Nsheet.Cells(1).PasteSpecial xlPasteAll


Otherwise, within your existing code, you're going to need to use a
..PasteSpecial

 

[MGC]

I'm pretty sure this works. I will work with it some more and let you know
but I think this is most likely what I'm after. This is way cool!!! If
there are any bugs, be assured I will let you know. Thanks so much for your
time and awsome help!!!!

Have a nice evening!

 

[JLatham]

Glad it seems to be working - since I'm not sure which way you went with my
offerings, if you do have problems, be sure and paste the current code you're
using.