• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Reset workbook to include the text "Tail #" in cell A! on all sheets

Hello Experts

I am using the following macro to set up a worksheet based of an model input and input a actual tail #. I was wondering if the resetsheets sub could be amended to place the following text on cell A1 "Tail #" Thanks

Sub SelectModel()
   Dim strSheet As String, strTail
   Dim wksKeep As Worksheet, wks As Worksheet
   Do
      strSheet = InputBox("Enter an Aircraft Model to use: 135KL, 145LR, or 145LR NO ROW 13", "Select Aircraft Model")
      If strSheet = "" Then
         Exit Sub
      Else
         On Error Resume Next
         Set wksKeep = Sheets(strSheet)
         On Error GoTo 0
         If wksKeep Is Nothing Then
            MsgBox "Invalid name - please try again (Check spelling)"
         End If
      End If
   Loop While wksKeep Is Nothing
   Application.DisplayAlerts = False
   For Each wks In ThisWorkbook.Worksheets
      If wks.Name <> wksKeep.Name Then wks.Visible = xlSheetHidden
   Next wks
   Application.DisplayAlerts = True
   strTail = InputBox("Enter a TAil Tumber")
   wksKeep.Range("A1").Value = strTail
End Sub

Sub resetsheets()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
   wks.Visible = xlSheetVisible
Next wks
End Sub
0
gecf343b1
Asked:
gecf343b1
  • 3
1 Solution
 
SiddharthRoutCommented:
You mean like this?

Sub resetsheets()
    Dim wks As Worksheet, strTail As String
    strTail = InputBox("Enter a TAil Tumber")
    For Each wks In ThisWorkbook.Worksheets
        wks.Visible = xlSheetVisible
        wks.Range("A1").Value = strTail
    Next wks
End Sub

Open in new window


Sid
0
 
SiddharthRoutCommented:
Sorry , I misunderstood.

Is this what you want?

Sub resetsheets()
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        wks.Visible = xlSheetVisible
        wks.Range("A1").Value = "Tail #"
    Next wks
End Sub

Open in new window


Sid
0
 
Saqib Husain, SyedEngineerCommented:
Change this line
   wksKeep.Range("A1").Value = strTail

to

   wksKeep.Range("A1").Value = "Tail # " & strTail
0
 
gecf343b1Author Commented:
Thanks
0
 
SiddharthRoutCommented:
You are welcome :)

Sid
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now