Create 52 sheets and carry all formulas and figures forward?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have made a spread sheet and I need to make one for every week of the year
and have it carry all formulas and data forward as well as have it change the
dates for each week. With out have to dot the old cut past to each and every
sheet. If anyone can help that would be GREAT!
 
Do you want to create a workbook full of 52 sheets all at once or just do
this at the end of a week when you are ready to start a new week?
A little information about the layout of your sheet(s) would help. For
instance, where is the date kept on each? Is ALL data to be copied to the
next week, or just a total of certain values?

The following code would copy the currently active sheet, placing it at the
end of the workbook and place the new date in it. Assumes date is in cell A1
of the sheets.
Sub StartNewWeek()
Dim NewDate As Date
NewDate = ActiveSheet.Range("A1") + 7
ActiveSheet.Copy After:=Sheets(Sheets.Count)
Range("A1") = NewDate
End Sub

if you want to do all 52 weeks at once:
Sub StartNewWeek()
Dim NewDate As Date
Dim LC As Integer
For LC = 1 To 51
NewDate = ActiveSheet.Range("A1") + 7
ActiveSheet.Copy After:=Sheets(Sheets.Count)
Range("A1") = NewDate
Next
End Sub
 
Thanks for the responce. I have a spread sheet of which I have formulas that
calculates information inputed that adds and subtracts from different cells.
This information in carried out day today keeping a running total for the
week. I need this information carried forward to the next (weekly) spread
sheet and to calculate in the new information. eg. each day I produce 5 pc.
one may be rejected so now I have 4 until I repair it. Once it is repaired
weather it be this week or a month from now when it is taken out of the
rejected it calulates back in.
What I have now will work by copy paste to new sheet however I have to go in
and change each formula to add the total from the previous page. I remember
doing this in college but it has been some time I do appreciate any help you
can give.
Thanks again
 
PB,
Sounds like the first routine I provided would be the one to base a 'custom'
solution on - unless you have fixed positions that the information to be
carried forward would always be found in. If the information to be carried
forward is always in the same place(s) and is always carried forward to
specific location(s)/cells on the next week's sheet then it can be coded up
easily, but if the location of the information to be carried forward is not
constant then we have to work at it a little more - determining where that
information is and coming up with a rule that says something like "if this
value here is XXX then we must take the value from this row, in column(s)
such and such and carry it forward to the next sheet".

It is all do-able, it's a matter of determining what has to be done and
when/how to know that it needs to be done. We can either try to hash this
all out here through exchanging messages, or if you like, you could send me a
copy of the workbook with a couple or three weeks worth of sample data and I
could try to put something custom, but flexible together for you. If you
want to try that, you can send a sample file to (remove spaces): HelpFrom @
jlathamsite.com and I'll give it a go. An explanation of what needs to be
moved from where to where and how you know that it needs to be moved would be
very helpful.
 
Hello JLatham.
I'm going to give this a try and see what I can do. If I still can't get it
I will send it to you. Thanks so much for the help, I'll let you know how I
make out.
PB
 
If your data is always going to end up in a specific layout with entries
always in the same cells, you could start by recording a macro to go through
the entire process and then modify that macro to make it more generic. For
example here are 2 code segments. The first is what was recorded to copy a
specific sheet - so using it would simply result in that same sheet being
copied over and over, but then I modified it as shown in the second segment
to become a generic copy macro that would copy what ever sheet was chosen at
the time the macro was run.

Sub RecordedCopySheetSetup()
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets(1)
Range("A466").Select
ActiveCell.FormulaR1C1 = "='Sheet1'!R[-2]C"
Range("A467").Select
End Sub

Sub GenericCopySheetSetup()
Dim anySheet As String

anySheet = ActiveSheet.Name
Sheets(anySheet).Copy After:=Sheets(Sheets.Count)
Range("A466").Select
ActiveCell.FormulaR1C1 = "='" & anySheet & "'!R[-2]C"
Range("A467").Select
End Sub

I think you can see the changes that have to take place: you have to capture
the name of the sheet being copied so you can modify formulas built after the
copy is created to link back to the original sheet. Then you have to modify
the code using the variable holding that sheet's name to handle things. Plus
by using (Sheets.Count) it will always place the new sheet at the end of the
workbook rather than after Sheets(1) as the recorded macro would always do.

But if your data to be carried forward isn't always in the same row or
column, then you have to modify the code some more to find where those values
are and set up the code to properly refer to them. That would be the case if
the values to be carried forward was from =SUM() formulas at the bottom of
variable length columns. Plus you've at least got to do the change date
setup as was previously shown.
 
Back
Top