Macro To Add New Worksheet

[Paul Sheppard]

I have a spreadsheet with a worksheet for each month, so as a new month begins I add a new worksheet using a macro

Each worksheet has 5 columns:

A = Vendor, B = Date, C = Debits, D = Credits, E = Balance

Row 30 contains the totals for columns C, D, & E, cell A30 contains the text Totals

The macro I use is as follows


VBA:

Sub AddSheet() 
    Dim NoOfSheets, YearTab As Integer 
    Dim LastSheet, MonthTab, NewYearTab, NewSheetTab As String 
    NoOfSheets = 0 
     
     'Count how Many Sheets
    For Each Sh In ActiveWorkbook.Sheets 
        NoOfSheets = NoOfSheets + 1 
    Next Sh 
     
    LastSheet = Sheets(NoOfSheets).Name 
    Sheets(NoOfSheets).Copy After:=Sheets(NoOfSheets) 
    MonthTab = Left(LastSheet, 3) 
    YearTab = Right(LastSheet, 2) 
     
    If MonthTab = "Jan" Then NewSheetTab = "Feb" 
    If MonthTab = "Feb" Then NewSheetTab = "Mar" 
    If MonthTab = "Mar" Then NewSheetTab = "Apr" 
    If MonthTab = "Apr" Then NewSheetTab = "May" 
    If MonthTab = "May" Then NewSheetTab = "Jun" 
    If MonthTab = "Jun" Then NewSheetTab = "Jul" 
    If MonthTab = "Jul" Then NewSheetTab = "Aug" 
    If MonthTab = "Aug" Then NewSheetTab = "Sep" 
    If MonthTab = "Sep" Then NewSheetTab = "Oct" 
    If MonthTab = "Oct" Then NewSheetTab = "Nov" 
    If MonthTab = "Nov" Then NewSheetTab = "Dec" 
    If MonthTab = "Dec" Then NewSheetTab = "Jan": YearTab = YearTab + 1 
     
    If YearTab < 10 Then NewYearTab = "0" & YearTab 
    Sheets(NoOfSheets + 1).Select 
    Sheets(NoOfSheets + 1).Name = NewSheetTab & NewYearTab 
     
     'formula in E2
    Range("E2").Select 
    ActiveCell.FormulaR1C1 = "='" & LastSheet & "'!R[28]C" 
    Range("E3").Select 
     
     
     ' clear cells
    Range("c2:D2 ").Select 
    Selection.ClearContents 
    Range("A3:D28 ").Select 
    Selection.ClearContents 
    Range("A2").Select 
End Sub

The problem I have is occaisionally extra rows are added so the totals may not be in row 30

Is there any way that the macro can be changed so that it looks for the word Total in column A and then reads the contents of the corresponding cell E? to transfer that total to cell E2 on the next worksheet

Also how can I make the clear cells part of the macro so that it clears cell down to the row immediately above the row with Totals in cell A?

Thanks in advance for any help

Paul