New sheet date update

jbrooks96 · Jan 16th 2017

I have an Excel workbook that will eventually have about 28 sheets. I was able to find code that let me copy the first time card properly and change its tab name. However, I am having issues that when the new sheet is created the date in the new sheet still corresponds to the date in the source. I was wanting to use the corresponding date in the summary page to populate the date in the new sheet. I have looked at VLookup functions and I see them used in reverse. I was also wanting to know if it might be possible to use the sheet names to create the corresponding date.forum.ozgrid.com/index.php?attachment/71104/

I have attached the beginning of the workbook along with the current sheets that I am trying to properly do. Right now I have to go into each sheet and modify C4 to the corresponding pay period in the summary page.

NoSparks · Jan 16th 2017

Re: New sheet date update

See if this macro does what you're after

Code

Sub TryThis()
    Dim sName As String
    Dim i As Integer, j As Integer
    Dim ws As Worksheet
    Dim wkEndDate As Date
    
'figure name for new sheet
For Each ws In ThisWorkbook.Sheets
  If Left(ws.Name, 2) = "PP" Then
    i = Mid(ws.Name, 3)
    j = Application.WorksheetFunction.Max(j, i)
  End If
Next ws
sName = "PP" & j + 1


'copy sheet to end of tab lineup
Worksheets("PP1").Copy after:=Sheets(Worksheets.Count)
'new sheet is automatically the active sheet
ActiveSheet.Name = sName
'figure end date for first week on time card
wkEndDate = Sheets("Summary").Range("D2") + 6 + (j * 14)
ActiveSheet.Range("C4").Value = wkEndDate
End Sub

Display More

jbrooks96 · Jan 16th 2017

Re: New sheet date update

I love it! Does exactly what I want and it cuts out my having to name the page.

My only concern that I have now, is how do I get the summary page to update as I add sheets. Right now I have 3 rows of the 27 with the correct formula (each manually entered). I would rather not have to adjust each row and column for every new sheet to get the equations correct.

NoSparks · Jan 16th 2017

Re: New sheet date update

Hope this works.
I put formulas in row 6, E to J and dragged them down.
Will leave the formatting and other formulas to you.

jbrooks96 · Jan 17th 2017

Re: New sheet date update

I cannot explain how much you have helped me. I tried for several days to do that and was beginning to think that it included the indirect function but given my unfamiliarity with it I knew it would take me forever to figure it out. I appreciate your help and experience.

NoSparks · Jan 17th 2017

Re: New sheet date update

Glad I would help.

New sheet date update

Participate now!

Share

Users Viewing This Thread