Make changes to many sheets

[nberryman]

I have 18 departments each of these have a workbook
containing 1 sheet for each month of the year and an annual
sheet.

The company now want to add another column to each sheet
to calculate an average

Can I use Vb to go through each sheet, bearing in mind that
they could have 28, 30 or 31 days, and add a formula to the
end?

Oh, the sheets are protected as well

I am happy that I can record a macro to implement the changes but have no idea how to loop through each sheet, work out the number of days (and therefore the start column) for the workbook.

Each sheet is named from April to March if that helps

Thanks for any help in advance

Neil Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[dwilson01]

This sounds like a relatively simple request, however, I recently got into a similar situation where I was going back and forth with someone where I would send something that worked on a test file, but didn't work on the actual files they wanted to perform the operation on. It would be helpful if you could send me a sample of the files that is completely representative of your situation. If you are willing to do this, I am sure I can create a solution. Otherwise, try starting with

Sub AddAnnual()

Dim oSheet As Object, rng As Range
Dim lColumn As Integer, fRow As Integer

For Each oSheet In ActiveWorkbook.Sheets
  ActiveSheet.UsedRange
  lColumn = ActiveSheet.UsedRange.Columns.Count + 1
  fRow = 1
  Do
    fRow = fRow + 1
    Set rng = ActiveSheet.UsedRange.Rows(fRow)
    Cells(fRow, lColumn) = Application.WorksheetFunction.Sum(rng)
  Loop While Not fRow >= ActiveSheet.UsedRange.Rows.Count
Next oSheet

End Sub

There are several assumptions made here and I only tested this on a very small data set. The assumptions are that the columns A, B, C... are the days of the month, 1, 2, 3... and that the first row to be summed is row 2. If you need more help, let me know or send a sample file to dave.wilson@asl-tk.com

Regards,

Dave

 

[dwilson01]

Add this line under the [bold]For Each[/bold] statement

oSheet.activate

This will activate the sheet you are working on. Also, If you put a line like

Application.ScreenUpdating = False
Application.ScreenUpdating = True

at the begining and end of the routine, the program will run faster