VBA Help Requested - Rolling up Tabs

[crmurray84]

Hello everyone-

I hope everybody is doing well. I have been spinning my wheels on trying to figure out how to best finish coding this Excel macro, and I figured at this point I should just reach out to the experts! I have attached a (scrubbed) sample picture of what I am working on for an internal customer.

At a high level, the customer will be entering data in the "Assumptions List" tab, which will make copies of a "template" tab, name these copies based on the data in cell C, and fill out relevant portions of the copied tabs. All of these tabs that are copied are moved in between a "Start" and "End" tab in order to provide high level rollup summaries easily.

This methodology has worked very well so far, however, I have recently been tasked to revise the macro to allow for "sub" rollups. To make a long story short, I have the following tabs (listed below). The customer would like the ability to have dynamic subrollups of the groups of tabs with letters listed at the end. For example, in this instance, the macro would sum up the data from all of the lines in each 255705 tab (A through B) and each 259601 (A through J) tab and produce a consolidated "template" outside of the normal "Start" and "End" range so as not to impact the Summary tabs that currently exist.

For some reason, I cannot for the life of me figure out how to code this into a macro that will be dynamic and allow the functionality for different users and sets of data. Any help would be greatly appreciated. Thanks in Advance-- have a great weekend everybody!!

[TABLE="width: 208"]
<colgroup><col></colgroup><tbody>[TR]
[TD]255705A[/TD]
[/TR]
[TR]
[TD]259601A[/TD]
[/TR]
[TR]
[TD]259601B[/TD]
[/TR]
[TR]
[TD]259601C[/TD]
[/TR]
[TR]
[TD]259601D[/TD]
[/TR]
[TR]
[TD]259601E[/TD]
[/TR]
[TR]
[TD]255705B[/TD]
[/TR]
[TR]
[TD]259601F[/TD]
[/TR]
[TR]
[TD]259601G[/TD]
[/TR]
[TR]
[TD]259601H[/TD]
[/TR]
[TR]
[TD]259601I[/TD]
[/TR]
[TR]
[TD]259601J[/TD]
[/TR]
[TR]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rhodie72]

that is a tiny table there... can't make out the issue with it. Why not post your code that you have so far into a

"

"

page? Somebody may be able to help you with it then.

 

[crmurray84]

Sorry about that! Not sure why the image was so tiny. Here is the code I have so far that is attempting to perform the rollup functionality... the code I have written so far creates "Start" and "End" tabs for each rollup --- and then attempts to sort the tabs so that each Start goes before the individual tabs and each end goes after the corresponding tabs.

Sub RollupCreation()

'Unhide the template Tab
Worksheets("BaseModel").Visible = True
Application.ScreenUpdating = False


Dim NumberFiles As Integer, FilesCreated As Integer, ColumnNumber As Integer, 
Let ColumnNumber = 11                           'start from column E
Let TabsSkipped = 0




'Create cost center worksheets and name them based on the code lists on the worksheet named Assumptions List
Dim N As Integer
Let N = 37 'start from Row 37
Let ReverseN = 0
Let TabCreated = 0
For N = 37 To 86        'search the first 50 cells in the cost center input area
    RowNumber = 86 - ReverseN
    TabName = Worksheets("Assumptions List").Cells(N, ColumnNumber)
    TNS = TabName + " START"
    TNE = TabName + "END"
    
    Dim ii As Integer, blnFound As Boolean
    blnFound = False
    
    With ThisWorkbook
        For ii = 1 To .Sheets.Count
            If .Sheets(ii).Name = TNE Then
                blnFound = True
                TabsSkipped = TabsSkipped + 1
'                MsgBox "Tab named " + TabName + " exists in this workbook already. Skipping."
                Exit For
            End If
        Next ii
        
        If blnFound = False Then
         
            
    
    If Len(TabName) <> 0 Then
        Application.DisplayAlerts = False                      'Turn off the alert of "A formula or sheet you want to move or copy contains the name "...", which already exists on the destination worksheet."
        Sheets("BaseModel").Copy Before:=Worksheets("END")    'make a copy of worksheet
        Application.DisplayAlerts = True                       'Turn on the alert
        ActiveSheet.Name = TabName                     'Rename worksheet with entity code
        ActiveSheet.Cells("1", "A").Value = TabName
        ActiveSheet.Cells("2", "A").Value = TabName + " START"
        ActiveSheet.Cells("3", "A").Value = TabName + "END"
        
        
        TabCreated = TabCreated + 1
        Application.DisplayAlerts = False                      'Turn off the alert
        Sheets("START").Copy Before:=Worksheets("END")    'make a copy of worksheet
        Application.DisplayAlerts = True                       'Turn on the alert
        ActiveSheet.Name = TNS                     'Rename worksheet with entity code
        TabCreated = TabCreated + 1
        ActiveSheet.Cells("1", "A").Value = TabName             'Fill Tab Name Out
        Application.DisplayAlerts = False                      'Turn off the alert 
        Sheets("END").Copy Before:=Worksheets("END")    'make a copy of worksheet
        Application.DisplayAlerts = True                       'Turn on the alert
        ActiveSheet.Name = TNE                        'Rename worksheet with entity code
        TabCreated = TabCreated + 1
        ActiveSheet.Cells("1", "A").Value = TabName             'Fill Tab Name Out
        
        End If
    ReverseN = ReverseN + 1
  
        End If
    End With




 Next N
   
'Unhide the template Tab
Worksheets("BaseModel").Visible = False
Application.ScreenUpdating = True
    
'Worksheets("Assumptions List").Cells("2", "C") = "at: " & Format(Now, "yyyymmddhhmm")
Worksheets("Assumptions List").Activate


MsgBox "In total " & TabCreated & " tabs have been created."


Call SortWorksheets








End Sub