Solved: Insert a column based on columns header

[JimS]

I need to insert a column based on the previous column’s header and then add a Sum Formula in that newly inserted column for every row that has data on the sheet.

I need to do this on every sheet whose name begins with the name “Sheet” [ie: Sheet1, Sheet1 (2), Sheet1 (3), etc]. There are other sheets in the workbook but their names will not begin with “Sheet” and therefore do not need to be touched.

The quantity of sheets that begin with the name “Sheet” can vary from 1 to 35.
The number of columns on each sheet can also vary, meaning that the macro would need to scan out until there is no more data.

I have attached an example file.

I need to insert a column after each column that’s heading ends with “MBytes Written/sec” and then I need to sum the previous 2 columns (columns before the newly inserted column) for each row that has data.

The newly inserted columns need to have a header added – a concatenation formula.
=CONCATENATE (LEFT(F1,7)," Total Mbytes") where F1 would change with each newly inserted column to be the previous column

Thanks for any and all ideas…

Jim

[mdmackillop]

[VBA]
Option Explicit
Sub AddCols()
Dim Cols As Long
Dim Rws As Long
Dim i As Long
Dim sh As Worksheet
For Each sh In Sheets
With sh
If Left(.Name, 5) = "Sheet" Then
Cols = .Cells(1, .Columns.Count).End(xlToLeft).Column
Rws = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
For i = Cols + 1 To 3 Step -1
If Right(.Cells(1, i - 1), 18) = "MBytes Written/sec" Then
.Columns(i).Insert
.Cells(1, i) = Left(.Cells(1, i - 1), 7) & " Total MBytes"
.Cells(2, i).Resize(Rws).FormulaR1C1 = "=R[0]C[-2]+R[0]C[-1]"
End If
Next
End If
End With
Next
End Sub

[/VBA]

[p45cal]

beaten to it, but:[VBA]Sub blah()
For Each sht In ActiveWorkbook.Sheets
If Left(sht.Name, 5) = "Sheet" Then
For Each cll In sht.Rows(1).Cells
If Right(cll.Value, 18) = "MBytes Written/sec" Then
sht.Columns(cll.Column + 1).Insert
With Intersect(sht.Columns(cll.Column + 1), sht.UsedRange)
.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
.Cells(1).Value = Left(.Cells(1).Offset(, -1).Value, 8) & " Total Mbytes"
End With
End If
Next cll
End If
Next sht
End Sub
[/VBA]

[JimS]

Thanks to both of you. Works perfect...