Excel VBA For Each Sheet add a new column

[JasonEnsor]

Hi Guys,

I have been given a spreadsheet to look at that currently takes a colleague about 4 hours a week to setup and format. I am slowly working through the process in my mind to see how i can automate as much of it as possible.

In the current workbook there are tabs setup from August 2012 to July 2013 (as this holds yearly data) Then a summary sheet is created that collates information from the each tab. for each month there is a column that holds % information regarding calculation values from that month.

My first thought is that for every tab in the workbook to create a column in the summary tab with the column heading being the name of the sheet. So i would have a tab called Aug 12 which would then mean my summary sheet has a column called Aug 12. I will then automate the formatting of these columns.

I am assuming i could do something like

For Each worksheet in workbook
Columns.add
worksheet.name = selected column and row
Next

Any thoughts on if this is a valid approach or ideas on how best to approach this. I can supply test worksheets if need be


Regards

Jason

 

[SkipVought]

Jason,

Don't know how far you are into your design, but I'd strongly advise against a sheet for each month. What a mess! You're shooting yourself in the foot by making the summarization of your data difficult. Typically you want to have a consolidataed data source rather than a semented data source, like ONE SHEET for the source data, from which a summarization could be done using a PivotTable of MS Query or even some simple aggregation formulas.

But if you're already committed, and it seems that that's where you probably are, you'll need some fancy VBA and other slight-of-hand to pull it off. You could completely reconstruct the Summary sheet each month or you could compare current sheet tabs with the summary month headings, add the column(s)/formulas/data for missing month(s). I might do something like this to begin with...

Sub test()
    Dim ws As Worksheet, rHD As Range, rFound As Boolean, rng As Range, iCol As Integer
        
    Set rng = Intersect(Sheets("Summary").Rows(1), Sheets("Summary").UsedRange)
        
    For Each ws In Worksheets
        Select Case ws.Name
           Case "Summary"
           Case Else
                rFound = False
                For Each rHD In rng
                    If rHD.Value = ws.Name Then
                        rFound = True
                        Exit For
                    End If
                Next
                
                If Not rFound Then
                    With Sheets("Summary")
                        iCol = .UsedRange.Columns.Count + 1
                        .Cells(1, iCol).Value = ws.Name
                    End With
                End If
        End Select
    Next

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JasonEnsor]

Hi Skip,

Thanks for those thoughts, sadly like most procedures i am asked to work on the data already comes in a predefined format and it becomes my job to explore how best to deal with the issues caused by poor design and lack of foresight. I am sure your code snippet and advise will come in handy for future projects though. I am trying to explain to higher management that alot of the 'fixes' i am putting in place would not be needed if the data was delivered in a better format...alas it's like banging my head against a brick wall.

Regards

Jason

 

[SkipVought]

The code is to address what is; that is to add a column with the new date tab name.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}