Saving Previous Data before editing

  • Thread starter Thread starter shriil
  • Start date Start date
S

shriil

Hi

I have an excel sheet where I input the expenses of my department for
a particular Month to get the total expenses for that month.

Month: Aug-09

Preventive Maintenance Expense: 25.3
Breakdown Maintenance Exp 31.5
Structural Repair Expenses 11.2
Term Contract Expenses 105.7
Total for the month 173.7

Likewise, for the next month, i.e. Sep-09, I will again input the
expenses in the same column and get the total. The problem is , that
before entering the data for Sep-09, I would like to keep and save
the August Data seperately. Right now the only option that I find is
Copy-Paste Special the Aug-09 Values in a seperate Range and then go
about entering new data for Sep-09.

Is there any way that I can run a code for keeping the previous
month's data and then start with the present month, in a format
similar to the one mentioned below


Month:
Aug-09 Sep-09 Oct-09

Preventive Maintenance Expense: 25.3
5.6 17.9
Breakdown Maintenance Exp 31.5
12.8 1.2
Structural Repair Expenses 11.2
24.0 4.1
Term Contract Expenses 105.7
104.3 106.5
Total for the month
173.7 146.7 129.7


Thanks

San
 
Apparently I'm missing what you want. Why can't you simply enter the
current month's expenses in the next unused column, just like you did in
your post? Provide a bit more info about what you want this "code" to do,
exactly. HTH Otto
 
Why not a separate column for each month?
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email













- Show quoted text -
Click to expand...

Sorry, I couldn't get the message across. The data that I had shown
was just a teeny weeny part of the original data entry form. The
actual form looks similar as below:

Month:
Aug-09
Sub Dept
Mech Elec Instr. Ops P&A Stores Lab
Planning ........................

Preventive Maintenance Expense: 25.3 ---
--- --- --- ---- --- --- ---
--- --- ---
Breakdown Maintenance Exp 31.5 ---
---- --- --- ---- ---- --- ---
--- --- ----
Structural Repair Expenses 11.2
--- --- ----- --- ---- ---
--- --- --- --- ----
Term Contract Expenses 105.7
--- --- ---- --- -- ---- ----
---- --- --- ---
Total for the month 173.7
--- ---- ----- ---- --- --- ---
---- --- -- ---

For each month, I need to enter a host of data pertaining to each
subdivision. In fact, there are other calculations, subtotals, etc
within the above range, which I have not been able to depict here. For
this reason, it is cumbersome for entering a new range of columns for
each month and enter the data for that month.

What I require is saving the data pertaining to the aggregate of each
Expense Head for each month in a seperate range before I start using
the entry form for inputting data of a new month.

For eg. I would like to save my data like: Aug 09

Preventive Maintenance Expense: [Sum of all Sub
Dept]
Breakdown Maintenance Exp [Sum of all Sub
Dept]
Structural Repair Expenses [Sum of all Sub
Dept]
Term Contract Expenses [Sum of all Sub
Dept]
Total for the month
----------

As of now, I have a range that sums the value, and then I Copy -
Paste Special- Value to another Range. - Which is quite manual in
nature.

If such an operation could be automated, it would have been easier for
me to handle the bulk data

shriil
 
put your cursor on the column letter>right click>insert>>>
or be more specific
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Why not a separate column for each month?
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email













- Show quoted text -
Click to expand...

Sorry, I couldn't get the message across. The data that I had shown
was just a teeny weeny part of the original data entry form. The
actual form looks similar as below:

Month:
Aug-09
Sub Dept
Mech Elec Instr. Ops P&A Stores Lab
Planning ........................

Preventive Maintenance Expense: 25.3 ---
--- --- --- ---- --- --- ---
--- --- ---
Breakdown Maintenance Exp 31.5 ---
---- --- --- ---- ---- --- ---
--- --- ----
Structural Repair Expenses 11.2
--- --- ----- --- ---- ---
--- --- --- --- ----
Term Contract Expenses 105.7
--- --- ---- --- -- ---- ----
---- --- --- ---
Total for the month 173.7
--- ---- ----- ---- --- --- ---
---- --- -- ---

For each month, I need to enter a host of data pertaining to each
subdivision. In fact, there are other calculations, subtotals, etc
within the above range, which I have not been able to depict here. For
this reason, it is cumbersome for entering a new range of columns for
each month and enter the data for that month.

What I require is saving the data pertaining to the aggregate of each
Expense Head for each month in a seperate range before I start using
the entry form for inputting data of a new month.

For eg. I would like to save my data like: Aug 09

Preventive Maintenance Expense: [Sum of all Sub
Dept]
Breakdown Maintenance Exp [Sum of all Sub
Dept]
Structural Repair Expenses [Sum of all Sub
Dept]
Term Contract Expenses [Sum of all Sub
Dept]
Total for the month
----------

As of now, I have a range that sums the value, and then I Copy -
Paste Special- Value to another Range. - Which is quite manual in
nature.

If such an operation could be automated, it would have been easier for
me to handle the bulk data

shriil
 
Add a new column before the one where the current months data is.
Title it PreviousYTD
Before ebtering a new months data copy the current YTD and past > special >
values into the new column.
Change the orig YTD ot a formual = PreviousYTD + Current Month

You can hide the PreviousYTD column if you make a macro to do the copy &
past > special > values

Eric
 
Sub TransferMonthDataSAS() 'SalesAidSoftware
mc = Cells.Find(What:=Range("b3"), After:=Range("b3"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
_
MatchCase:=False).Column
'MsgBox mc
For i = 2 To 8 'col H
mr = Cells.Find(What:=Cells(5, i), After:=Range("a6"), LookIn:=xlFormulas,
_
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
'MsgBox c & mr
Cells(6, i).Resize(7).Copy Cells(mr + 2, mc)

Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don Guillett said:
put your cursor on the column letter>right click>insert>>>
or be more specific
If desired, send your file to my address below along with this msg
and a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Why not a separate column for each month?
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email













- Show quoted text -
Click to expand...

Sorry, I couldn't get the message across. The data that I had shown
was just a teeny weeny part of the original data entry form. The
actual form looks similar as below:

Month:
Aug-09
Sub Dept
Mech Elec Instr. Ops P&A Stores Lab
Planning ........................

Preventive Maintenance Expense: 25.3 ---
--- --- --- ---- --- --- ---
--- --- ---
Breakdown Maintenance Exp 31.5 ---
---- --- --- ---- ---- --- ---
--- --- ----
Structural Repair Expenses 11.2
--- --- ----- --- ---- ---
--- --- --- --- ----
Term Contract Expenses 105.7
--- --- ---- --- -- ---- ----
---- --- --- ---
Total for the month 173.7
--- ---- ----- ---- --- --- ---
---- --- -- ---

For each month, I need to enter a host of data pertaining to each
subdivision. In fact, there are other calculations, subtotals, etc
within the above range, which I have not been able to depict here. For
this reason, it is cumbersome for entering a new range of columns for
each month and enter the data for that month.

What I require is saving the data pertaining to the aggregate of each
Expense Head for each month in a seperate range before I start using
the entry form for inputting data of a new month.

For eg. I would like to save my data like: Aug 09

Preventive Maintenance Expense: [Sum of all Sub
Dept]
Breakdown Maintenance Exp [Sum of all Sub
Dept]
Structural Repair Expenses [Sum of all Sub
Dept]
Term Contract Expenses [Sum of all Sub
Dept]
Total for the month
----------

As of now, I have a range that sums the value, and then I Copy -
Paste Special- Value to another Range. - Which is quite manual in
nature.

If such an operation could be automated, it would have been easier for
me to handle the bulk data

shriil
Click to expand...
 
Back
Top