Excel spreadsheet - rolling changes


I'm hoping somebody can give me some advice on an Excel question.

I have an Excel spreadsheet, which has multiple tabs associated with departments, in which data has to be entered each week in. For the sake of argument, let's assume that each tab contains two columns "Credit/Debit Previous" and "Credit/Debit Current".

In week 1, I will enter data into the spreadsheet and this will populate the "Credit/Debit Current" column; "Credit/Debit Previous" will be empty at the beginning. The next week, I manually copy the values of "Credit/Debit Current" from the week 1 to the "Credit/Debit Previous" of the week 2 sheet. This process repeats as the weeks pass.

My question is, is there a way to automate this process of copying the necessary values of one sheet to the new sheet?

For reasons I won't go into, the data for each week has to be included in seperate spreadsheet files.

Thanks in advance

Who is Participating?
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Normally such things are done by VBA. But it is also possible, with some limitations, without VBA. See attached file. for every new week make a copy of the T sheet and then rename that copy to the new week number and it will pick up the current values of the previous week in the previous column.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

the description of the scenario is a bit confusing. Do you have a sheet for each department or do you have a sheet for each week?

>>  multiple tabs associated with departments

>>  copy the values of "Credit/Debit Current" from the week 1 to the "Credit/Debit Previous" of the week 2 sheet

Manual copying should not be required. Formulas can do that job.

It also sounds as if you are entering data into different sheets that have a report format. That is not good practice. Data should be entered into one table, on one sheet only. Then you can create reports by week or by department with pivot tables or other techniques.

Please post a sample with dummy data that illustrates what you are trying to achieve.

cheers, teylyn
IssacJonesAuthor Commented:
@teylyn - each sheet (file) has different departments on tabs. I agree it isn't good practise but it is what has been inherited and the powers that be don't want major changes :(

@ssaqibh - I suspected VBA would be an approach. I haven't really done any but I will check your code and do some reading. It may be exacly what I need :)
IssacJonesAuthor Commented:
I'm possibly confusing things with the terminology I'm using. I haven't really used Excel before!

A spreadsheet (which I associated with a file) is for one week, with different tabs (sheets?) for each department.

Data is entered in one spreadsheet for a week and the following week I want the data from the previous week to be transferred over to the new spreadsheet.

I agree it isn't ideal way to do it.
Saqib Husain, SyedEngineerCommented:
That is what my spreadsheet is doing.....without VBA.  VBA can also be used as an alternative
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.