We help IT Professionals succeed at work.

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

gecf343b1
gecf343b1 asked
on
176 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

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
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Change this line
   wksKeep.Range("A1").Value = strTail

to

   wksKeep.Range("A1").Value = "Tail # " & strTail

Author

Commented:
Thanks
You are welcome :)

Sid
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.