Thanks for the reply macropod. I'm currently working on (struggling) some vba code to copy previous mileage to a history sheet for each month, however, I think I may consider your suggestion of putting the formulas on another sheet... I might then be able to insert/hide the history columns on the data sheet (the user's preference)..since inserting columns which would push data & formulas over would not be an issue if the formulas were on a different sheet.
Here's my current vba code
Code:
'Prior to the Previous Mileage column on the Main sheet being updated with the Current Mileage column (performed once at beginning of month),
'this section copies the Previous Mileage to the Mileage History sheet to add to the mileage history
'which helps Deanna better track/manage the mileages.
'Holds last month/year (ex. December, 2011) for Mileage History sheet column heading
LDate = (MonthName(DatePart("m", CurDate - 30))) & ", " & DatePart("yyyy", CurDate)
Lc = Lastcol(Sheets("Mileage History")) + 1 'finds the first empty column on the Mileage History sheet
Set sourceRange = Sheets("Main").Columns("C:C") 'sets Main.previous mileage column range
Set destrange = Sheets("Mileage History").Columns(Lc) 'sets Mileage History column range to copy prev mileage to
sourceRange.Copy destrange 'copy prev mileage
'Adds a column heading of previous month, year, centers expands column width
With destrange.Range("A1")
.Value = LDate
.HorizontalAlignment = xlCenter
.ColumnWidth = 11
End With