Excel Multi-Tab Scripting

Lfdr4435

New Member
Joined
Nov 27, 2011
Messages
1
I have been tasked with doing a retail over/short Spreadsheet. The spreadsheet will be 13 tabs with 12 being the months and the 13th being the yearly summary. On each sheet will be the cashiers name.

I need to be able to script the addition of a new cashier and have them added alphabetically to all 13 sheets and at the same time keep all currently entered information with the appropriate person

End user only wants to use Excel


Any help is appreciated.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,912
Perhaps something like this in the ThisWorkbook code module.

Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim actionRange As Range, actionAddress As String
    Dim uiName As String, uiAction As Long
    Dim strPrompt As String, strDefault As String
    Dim nameCell As Range
    Dim linkedSheets As Sheets, oneSheet As Worksheet
    
    Set linkedSheets = ThisWorkbook.Worksheets
    actionAddress = "A:A"
    For Each oneSheet In linkedSheets
        If oneSheet.Name = Sh.Name Then
            Set actionRange = Sh.Range(actionAddress)
            Set actionRange = Application.Intersect(actionRange, Target)
        End If
    Next oneSheet
    
    If Not actionRange Is Nothing Then
        If Target.Cells.Count = 1 Then
            Set nameCell = Target.Cells(1, 1)
            strDefault = CStr(nameCell.Value)
            
            If strDefault = vbNullString Then
            strPrompt = "Enter the new name"
            Else
            strPrompt = "Edit the name."
            End If
            
            uiName = Application.Proper(Application.InputBox(strPrompt, Default:=strDefault, Type:=2))
            If uiName = "False" Then GoTo Canceled
            
            If strDefault = vbNullString Then
                strPrompt = "Add " & uiName & " as a new name."
                uiAction = MsgBox(strPrompt, vbOKCancel)
            Else
                strPrompt = "Yes - change " & strDefault & " to  " & uiName & "."
                strPrompt = strPrompt & vbCr & vbCr & "No - add " & uiName & " as a new name."
                uiAction = MsgBox(strPrompt, vbYesNoCancel)
            End If
            If (strDefault = uiName) And (uiAction = vbYes) Then uiAction = vbCancel
            
            Select Case uiAction
                Case vbNo, vbOK
                    Rem new name
                    With nameCell.EntireColumn
                        .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = uiName
                    End With
                Case vbYes
                    Rem edit name
                    nameCell.Value = uiName
                Case vbCancel
                    GoTo Canceled
            End Select
            
            On Error GoTo Canceled
            Application.EnableEvents = False
            
            With nameCell.EntireColumn
                With Range(.Cells(1, 1), .Cells(.Rows.Count).End(xlUp))
                    linkedSheets.FillAcrossSheets .Cells
                End With
            End With
            
            For Each oneSheet In linkedSheets
                With oneSheet
                    With Range(.UsedRange, .Cells(1, 1))
                        .Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
                    End With
                End With
            Next oneSheet
        End If
Canceled:
        Application.EnableEvents = False
        Target.Offset(0, 1).Select
        Application.EnableEvents = True
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,798
Messages
5,598,105
Members
414,213
Latest member
suxrule

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top