Updating subsequent sheets with prior sheet data

November 2, 2012 at 13:32:16
Specs: Windows XP
I have a spreadsheet setup in Excel 2003 for a budget, each sheet is for a one week period because that is how I do my budget. I want to update each subsequent sheet with carryover budget dollars from the previous sheet. Is there a way to copy my master sheet over to each weeks sheet, updating the formulas to reflect the previous week?
For instance: In cell H6 of the sheet titled "14-Nov-12" I want to capture the remainder of sheet "07-Nov-12" cell I6. On sheet "21-Nov-12" I will want to have the cell I6 from Sheet "14-Nov-12" and so on down my list of sheets.
I have a macro already that automates the creation and naming of sheets (thank you to those who created it!), but cant figure out how to do this one without setting it up in each individual sheet as I switch to it.

See More: Updating subsequent sheets with prior sheet data

Report •

#1
November 2, 2012 at 17:46:00
Why not have the macro put a formula in whatever cell you want to contain the I6 value from the previous sheet?

Without seeing the code, I'm assuming that it knows what "date" to use to name the new sheet. From that date, just subtract 7 to get the name of the sheet you want to pull the value from.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 3, 2012 at 08:15:36
Well, I was thinking along that line, but I don't know how to set up the code. I understand VBA and know how to insert it in a module, I can even do some tweaking, but I dont have a clue as to where to start setting up the code I need. Here is the code I have, but I only run it once to create and name the sheets. Of course, if anyone can figure out how to insert the added code to do what you suggest, I will be glad to delete my current pages and create new ones. My dates are on the sheet labeled "Master" in column A.
Thanks for the response.

Sub SheetNames()
Dim inX As Integer
Dim stName As String
inX = 1
Do Until Cells(inX, 1).Text = ""
stName = Cells(inX, 1).Text
Sheets.Add
ActiveSheet.Name = stName
Worksheets("Master").Activate
inX = inX + 1
Loop

End Sub


Report •

#3
November 4, 2012 at 06:04:58
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post code in this forum. Posting it per those instructions makes it a lot easier to read, as you'll see from my code below.

Thanks.

As for your question, your code picks up the date from a series of cells in the Master sheet. Since you want a formula in each sheet that refers to the previous sheet, you can pick up the name of previous sheet from the cell above the cell from which the then-current sheet name was taken.

Each time through the Do Loop your sheet names are picked up from Cells(inX, 1). Since inX is the Row that contains the current sheet name, inX-1 is the Row above it, which contains the name of the previous sheet.

The only problem is that for the sheet named from Row 1, inX-1 refers to Row 0, which will cause an error. Therefore I used the On Error instruction to allow the code to keep running when it refers to Row 0, since that will only happen once. Basically the code will see the error when it tries to access Cells(0,1) and skip that instruction, moving on to continue the code. As long as that is the only error, things will work fine (as in my testing) but if any other errors are detected, the code will ignore them also. That could be an issue if you alter the code and introduce any other errors.

I suggest that you try this code in a backup copy of your workbook, one without sheets that are already named for the Dates in the Master sheet.

Sub SheetNames()
Dim inX As Integer
Dim stName As String
'On Error used deal with Row 0 access
On Error Resume Next
'Initiate Row counter
 inX = 1
'Do Loop to create sheets, insert formula
   Do Until Cells(inX, 1).Text = ""
'Get Date from Master sheet
    stName = Cells(inX, 1).Text
'Add Sheet and name is
    Sheets.Add
     ActiveSheet.Name = stName
'Insert formula, reference previous sheet
     ActiveSheet.Range("H6").Formula = _
        "='" & Sheets(Cells(inX - 1, 1).Text).Name & "'!I6"
     Worksheets("Master").Activate
'Increment Row counter
     inX = inX + 1
   Loop
End Sub

I do have a question...

As far as I can tell, this code adds Sheets, named for a date, and will now contain 1 formula. That doesn't sound like much of a budget sheet. How are you getting the other information/formulas into the new sheets?

P.S. You might want to refer to this How To also. It will give you some basic trouble shooting techniques which might help you understand VBA code a little better.

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 5, 2012 at 06:08:05
Sorry, guess I didnt think far enough ahead on my explanation. The code I have will create and name each sheet according to the date series I have set up in my master sheet. Good so far.
I have another sheet "Blank Week" that has all my fixed budget items as well as my variable budget items and formulas for the variables to calculate what has been spent, what category, etc. I was looking for a way to tell each sheet to look to the previous sheet for carryover data in a given set of cells (I only used one cell in my example) to update the amount spent, saved, carried over, etc.
I guess what I really need is a code that will create the sheets from a series, copy a spreadsheet to each, with incrementing sheet references for specific cells.
My goal was to be able to automate the process. Currently, I have to copy to each sheet from the "Blank Week" and then remember to insert/update the formulas to make it capture data from the previous week. Not so tough for someone who works with Excel, but hard to pass on to others who just need a simple weekly budget program. I share budgeting advice with others and have set up numerous spreadsheets as a result. My goal was to develop one that would be more user friendly that I can pass along.
Thanks for all the help.

Report •

#5
November 5, 2012 at 16:09:18
The danger in giving incomplete details of your requirements is that someone might create a test workbook based on your limited description and come up with a solution to the problem you described, only to find out that they wasted their time.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question