+ Reply to Thread
Results 1 to 3 of 3

Inputbox and activate sheet

  1. #1
    Registered User
    Join Date
    10-03-2008
    Location
    Canberra Australia
    Posts
    10

    Inputbox and activate sheet

    Hi All

    Was wondering if you could help. I want to by using an input box, ask the sheet that requires reformating, and then select the sheet and re format it.

    I have the following code, but I keep getting errors and can't seem to nut out what the problem is.


    Private Sub FORMAT_Click()

    Call Sheetname

    ' Delete the blank columns, and the Balance column

    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("N:N").Select
    Selection.Delete Shift:=xlToLeft

    'Insert 2 new columns, name and copy format for heading


    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Account"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Entity"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Classification"
    Range("G1").Select
    Selection.Copy
    Range("D1:F1").Select
    Range("F1").Activate
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False


    End Sub

    Sub Sheetname()

    'indentify the sheet that requires reformating

    Dim Stn As String

    Stn = Application.inputbox("What is the name of the sheet you wish to format?")

    ThisWorkbook.Worksheets(Stn).Activate
    Range("A1").Select

    End Sub

  2. #2
    Registered User
    Join Date
    10-03-2008
    Location
    Canberra Australia
    Posts
    10

    Re: Inputbox and activate sheet

    Sorry tried to get the code into the indented quote marks but couldn't edit it.

  3. #3
    Registered User
    Join Date
    10-03-2008
    Location
    Canberra Australia
    Posts
    10

    Re: Inputbox and activate sheet

    Don't worry I was able to get it to work. I just need to change the reference in the section section to activeworksheet.range rather the colunm.

    Thanks

    Carrie

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1