Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need A Macro To Copy Previous Tab to New Tab
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need A Macro To Copy Previous Tab to New Tab
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need A Macro To Copy Previous Tab to New Tab
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need A Macro To Copy Previous Tab to New Tab
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. "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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need A Macro To Copy Previous Tab to New Tab
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" wrote: 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need A Macro To Copy Previous Tab to New Tab
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" wrote: 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. "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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need A Macro To Copy Previous Tab to New Tab
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" wrote: 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" wrote: 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. "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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need A Macro To Copy Previous Tab to New Tab
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" wrote: 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" wrote: 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" wrote: 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. "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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need A Macro To Copy Previous Tab to New Tab
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. "MGC" wrote: 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" wrote: 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" wrote: 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" wrote: 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. "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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy previous row and insert two blank rows | Excel Discussion (Misc queries) | |||
Copy Formatting of previous worksheet | Excel Discussion (Misc queries) | |||
Referring to the previous selected sheet in a macro | Excel Discussion (Misc queries) | |||
how would activate the previous sheet in macro | Excel Discussion (Misc queries) | |||
Keyboard Macro Just Copies Content of Previous Cell | Excel Discussion (Misc queries) |