Macro To Add New Worksheet

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

I hesitate to reply as my VB is not the best, but there are no other replies..

Try adding

Dim rowNumber as Integer
' find the row where the total is – 
For Each c In Worksheets("LastSheet ").Columns(1).Cells
   If c.Value = "Total" Then
    rowNumber = c.Row
  End If
Next c

' copy the total
Worksheets("LastSheet ").Columns(1).Cells(rowNumber,5).Copy

and then paste into cell on the new sheet


Hope this helps
Willow

I notice a few others have replied since I started looking at this. Anyway....

I think you are saying that normally A30 contains Totals, but sometimes it is cell A31 or A32 etc. If I can assume that ONE cell in column A at or below A30, then the following code will find the cell for you.

Range("A29").Select
    Do While Selection.Value <> "Totals"
       Selection.Offset(1, 0).Select
    Loop
    myrow = Selection.Rows
    mycell = "e" & myrow
    Sheets(NoOfSheets + 1).Range("E2").Value = mycell.Value

Matt