Carry balance with each new sheet created

[shane_kidani]

I have this code used for my macro:

Option Explicit
Sub CreateNextWeek()
  Dim WeekNbr As Long
  WeekNbr = InputBox("Enter the week number.")
  Sheets("Template").Copy After:=Sheets(Sheets.Count)
  ActiveSheet.Name = "WE (" & WeekNbr & ")"
  
  
  Cells(2, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H2"
  Cells(3, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H3"
  Cells(4, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H4"
  Cells(5, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H5"
  Cells(6, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H6"
  Cells(7, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H7"
  Cells(8, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H8"
  Cells(9, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H9"
  Cells(10, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H10"
  Cells(11, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H11"
  Cells(12, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H12"
  Cells(13, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H13"
  Cells(14, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H14"
  Cells(15, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H15"
  Cells(16, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H16"
  Cells(17, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H17"
  Cells(18, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H18"
  Cells(19, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H19"
  Cells(20, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H20"
  Cells(21, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H21"
  Cells(22, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H22"
  Cells(23, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H23"
  Cells(24, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H24"
  Cells(25, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H25"
  Cells(26, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H26"
  Cells(27, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H27"
  Cells(28, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H28"
  Cells(29, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H29"
  Cells(30, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H30"
  Cells(31, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H31"
  Cells(32, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H32"
  Cells(33, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H33"
  Cells(34, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H34"
  Cells(35, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H35"
  Cells(36, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H36"
  Cells(37, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H37"
  Cells(38, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H38"
  Cells(39, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H39"
  Cells(40, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H40"
  Cells(41, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H41"
  Cells(42, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H42"
  Cells(43, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H43"
  Cells(44, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H44"
  Cells(45, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H45"
  Cells(46, "D").Formula = "='Week (" & WeekNbr - 1 & ")'!H46"
  
  
  
End Sub

which works fine, except for one big irritation: I don't want to repeat the steps 46 times.
Can anyone out there suggest a way to condense all this into one step?

Here is the goal of the workbook:

1. Run the macro "CreateNextWeek"
2. The macro will prompt for the number of the new week
3. After entering the number and clicking OK, a new sheet is created
4. As the new sheet is created, D2 will be changed to the value of the previous sheet's H2
5. Repeat automatically down to D46

The problem is that for each row, I must select the workbook where the sheets are contained. I have uploaded the file for review. Thank you.

 

[Smallman]

Hi Shane

This is not really tested I got confused by the brackets in your new sheet but not in your existing sheets. The theory holds though so if you can't get this working a derivation of this will work.

Option Explicit
Sub CreateNextWeek()
    Dim wkNo As Long
    wkNo = InputBox("Enter the week number.")
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "WE (" & wkNo & ")"
    If wkNo = 1 Then
        Cells(2, "H") = ""
    Else
        [D2:D46].Value = Sheets(wkNo - 1).[h2:h46].Value
    End If
End Sub

Take care

Smallman

 

[Somendra Misra]

Hi Shane,

You can also try below code:

Option Explicit
Sub CreateNextWeek()
    Dim WeekNbr As Long
    Dim j As Integer, i As Integer


j = ThisWorkbook.Worksheets.Count

    WeekNbr = InputBox("Enter the week number.")
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "WE (" & WeekNbr & ")"
    j = ThisWorkbook.Worksheets.Count

    If WeekNbr = 1 Then
        Cells(2, "H") = ""
       
    Else
       
        For i = 2 To 46
        Sheets(j).Cells(i, 4) = Sheets(j - 1).Cells(i, 8)
        Next i
    End If
   
End Sub

Regards,

 

[shane_kidani]

Sorry I didn't check back sooner. I used to get email notifications when there were responses to my posts... Anyhow, in both suggestions, when I run the macro and name the week "1", H2 goes blank, as it should, and D2 will show $831,607.00 but any new sheet/week created after that generates figures that are unexplainable.

For example, using SmallMan's suggestion, if I create week 2, D2 will have
$126,722.94 and H2 will have $116,058.94.

Using Somendra's suggestion, creating week 2 leaves D2 blank and H2 with $ (10,664.00). Actually, I understand what is happening with this, I just don't know why. The formula in H2 is =sum(D2+E2-F2-G2), and since there are no values in D2, E2 &, G2 will result in negative value of F2.

Perhaps one of you might suggest a completely different approach. Let's take it from the top.

Assume I had no macro in my workbook. How would one suggest that when creating a new sheet, said sheet would put the values of H2:H46 from the previous sheet into D2:D46 of the new sheet.

Example:

If workbook had only one sheet (let's call it sheet1), how should i code the macro that would allow me to copy sheet1 into a new sheet, but instead of being an exact copy, sheet2 would have in D2:D46, the values of H2:H46 from sheet 1.

 

[NARAYANK991]

Hi Shane ,

Can you clarify the following ?

1. Is this macro meant for this workbook , or for another workbook , where you will be starting from scratch ?

What I mean is , will the sheet corresponding to the week number exist when the macro is run ? What should happen in such a case ?

Suppose the user enters a week number well into the future ? For instance when we are in week 7 , the entered week number is 49 ; what should happen in this case ?

2. I do not know why you want the opening balance to come through a formula , especially when it is in a macro ; the opening balance can be a value , in which case , the only problem is to decipher the name of the sheet corresponding to the week number just prior to the week number entered by the user.

3. Some of the the sheets already present in the workbook have 2 situations :

1. The values in column D for a particular week do not correspond at all to the values in the sheet corresponding to the previous week.
2. The values in column D for a particular week match the values in the sheet corresponding to the previous week , but in column K

Narayan

 

[Somendra Misra]

Hi Shane,

You are getting values in column E,F, & G since your template which is being getting copied has those values. If you clear your template and just keep a blank template than the problem will be resolved.

Regards,

 

[shane_kidani]

@ NARAYANK991
Yes, the macro in question is meant for the work in question.

Not sure I understand your question: "
What I mean is , will the sheet corresponding to the week number exist when the macro is run ? What should happen in such a case ?"

But I'll try to answer it. The original macro I added, as well as the two other suggestions, create a new sheet based off of the active sheet. When the macro is run, the user specifies which week number, and if I'm not mistaken, only specifying week 1 makes a difference. That is, after week 1, a user could enter any week number, and the result would be the same because the new sheet (week 1) would be the active sheet, thus creating the new starting point when the macro is run again. That is, of course, unless the user runs the macro while on a different sheet.

I somewhat answered your suppositional question above, but to be honest, I've never tried, nor have I considered it, but in theory, even if the user specified that the next week to be created was 49, despite current week being 7, then although in name it's week 49, the figures would represent week 8.

The biggest reason for this idea is that the work is repetitive. The owner/user doesn't want to copy and paste with every new sheet. I agree, though, that the problem is finding a (better) way to point to the previous week to pull the data from. Perhaps there is something you can suggest?

In regards to the data correspondence, I'm not the creator of this workbook, rather I'm helping a colleague. The only sheet I've created was "template", so I'll have to take a closer look at the data, as this may help me understand the problem.

@Somendra Misra
I think you're on to something. I moved the template sheet into a blank workbook, viola, the subsequent weeks created by the macro revision you suggested work like a charm.

Thank you.