Saving Previous Data before editing

[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

 

[Bernard Liengme]

Why not a separate column for each month?
best wishes

 

[Otto Moehrbach]

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

 

[slah]

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 -

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

 

[Don Guillett]

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 -

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

 

[ebloch]

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

 

[Don Guillett]

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)

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 -

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