View Single Post
 
Old 04-23-2012, 01:30 PM
cjaye cjaye is offline Windows XP Office 2007
Novice
 
Join Date: Mar 2012
Posts: 6
cjaye is on a distinguished road
Default

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
Reply With Quote