Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
selecting a single sheet from a volume of sheets in a workbook
Hi All,
I have 30 and above sheets in a work book and like that I am having 5 such books. The sheets are named as 201, 202, 203 ....etc as per the contents in that particular sheets. (201, 202 .....are the P.O nos.). all the work sheets are of having similar format of datas. Now what I need is if I want to look the details of one single sheet (say 324) I have go all the sheets one by one and it is hard to find out. If any body give me a solution so that if I type a particular no. (forms part of the name of the sheet) that sheet should appear for me. Help please. TT |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
selecting a single sheet from a volume of sheets in a workbook
No News wrote: If any body give me a solution so that if I type a particular no. (forms part of the name of the sheet) that sheet should appear for me. Hi No News If your really want to do it this way, add this code to each sheet: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo nosheet If Target.Address = "$A$1" Then Sheets(Range("A1").Value).Activate End If Exit Sub nosheet: MsgBox ("No sheet with the name " & Range("A1").Value & " found") End Sub Amend the $A$1 to the cell you want to use. Alternatively, you could right-click the navigation arrows at the bottom left of the screen (next to Sheet1 in a new workbook) and select your sheet from there. Regards Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
selecting a single sheet from a volume of sheets in a workbook
Dear Steeve.
Thanks for your immediate reply. For your 2nd option. Since I have more than 100 Sheets It is difficult for me to select For your 1st option:- Since I was not so familier with excel, I do not know where to add these codes to each sheet. Can you explain please. Thanks in advance. "Scoops" wrote in message ups.com... No News wrote: If any body give me a solution so that if I type a particular no. (forms part of the name of the sheet) that sheet should appear for me. Hi No News If your really want to do it this way, add this code to each sheet: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo nosheet If Target.Address = "$A$1" Then Sheets(Range("A1").Value).Activate End If Exit Sub nosheet: MsgBox ("No sheet with the name " & Range("A1").Value & " found") End Sub Amend the $A$1 to the cell you want to use. Alternatively, you could right-click the navigation arrows at the bottom left of the screen (next to Sheet1 in a new workbook) and select your sheet from there. Regards Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
selecting a single sheet from a volume of sheets in a workbook
No News wrote: Dear Steeve. For your 1st option:- Since I was not so familier with excel, I do not know where to add these codes to each sheet. Can you explain please. Hi No News Pasting the code into 100 sheets would be painful itself, so: Press Alt+F11 to open the vb editor. Click Insert Module. Paste the following code into the window that opens: Sub SheetFind() Dim mySheet As String On Error GoTo nosheet mySheet = InputBox("Please enter the Sheet name to activate", "GoTo Sheet") Sheets(mySheet).Activate Exit Sub nosheet: MsgBox ("No sheet with the name " & mySheet & " found") End Sub ***End of code*** The Input box isn't very elegant but functional. To run the macro: From the Excel toolbar click Tools Macro Macros SheetFind. You might want to investigate placing a custom button on your toolbar and assigning the macro to it so that it runs with a single click (right-click a toolbar and Customize...) Regards Steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
selecting a single sheet from a volume of sheets in a workbook
Dear Steve,
When running the macro it stops at the following line with error Dim mySheet As String Please advise what todo "Scoops" wrote in message ups.com... No News wrote: Dear Steeve. For your 1st option:- Since I was not so familier with excel, I do not know where to add these codes to each sheet. Can you explain please. Hi No News Pasting the code into 100 sheets would be painful itself, so: Press Alt+F11 to open the vb editor. Click Insert Module. Paste the following code into the window that opens: Sub SheetFind() Dim mySheet As String On Error GoTo nosheet mySheet = InputBox("Please enter the Sheet name to activate", "GoTo Sheet") Sheets(mySheet).Activate Exit Sub nosheet: MsgBox ("No sheet with the name " & mySheet & " found") End Sub ***End of code*** The Input box isn't very elegant but functional. To run the macro: From the Excel toolbar click Tools Macro Macros SheetFind. You might want to investigate placing a custom button on your toolbar and assigning the macro to it so that it runs with a single click (right-click a toolbar and Customize...) Regards Steve |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
selecting a single sheet from a volume of sheets in a workbook
No News wrote: Dear Steve, When running the macro it stops at the following line with error Dim mySheet As String Please advise what todo Hi No News The error is in this line (Isuspect that it is in red in your code): mySheet = InputBox("Please enter the Sheet name to activate", "GoTo Sheet") The problem has come from the formatting of the line in the posting. Make sure that "GoTo Sheet") is on the same line, not split as in the original post and remove the " (double quote) that the vba editor will have added after the last close bracket - it should be "GoTo Sheet") not "GoTo Sheet ") " Regards Steve |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
selecting a single sheet from a volume of sheets in a workbook
Well Steve. That works well. Thanks.
But now I want to have a menu button on the toolbar. But I could not follow the information provided by you as below. I can get - Right click on the tool bar - Cutomise - Then what to do " You might want to investigate placing a custom button on your toolbar and assigning the macro to it so that it runs with a single click (right-click a toolbar and Customize...) " Help me pls. "Scoops" wrote in message oups.com... No News wrote: Dear Steve, When running the macro it stops at the following line with error Dim mySheet As String Please advise what todo Hi No News The error is in this line (Isuspect that it is in red in your code): mySheet = InputBox("Please enter the Sheet name to activate", "GoTo Sheet") The problem has come from the formatting of the line in the posting. Make sure that "GoTo Sheet") is on the same line, not split as in the original post and remove the " (double quote) that the vba editor will have added after the last close bracket - it should be "GoTo Sheet") not "GoTo Sheet ") " Regards Steve |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
selecting a single sheet from a volume of sheets in a workbook
No News wrote: Help me pls. Hi No News With the sheet containing the macro open: Right-click the toolbar Customize Click the Command tab Scroll down the Categories and click Macros Drag the Smiley face onto the toolbar of your choice Right-click the Smiley Assign Macro Click SheetFind, click OK, click Close. Click the Smiley to run the macro. If you want to remove the Smiley, Customize and drag it back into the dialog box. You can also change the way it looks: Customize, right-click Change Button Image. Have fun. Regards Steve |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
selecting a single sheet from a volume of sheets in a workbook
Fantastic Steve.
It has helped ver..................ry much. and thanks a lot. If any further help is needed I can contact you. and Can I introduce your name to my various friends of beginers to learn more. Thanks and regards, No News. "Scoops" wrote in message ups.com... No News wrote: Help me pls. Hi No News With the sheet containing the macro open: Right-click the toolbar Customize Click the Command tab Scroll down the Categories and click Macros Drag the Smiley face onto the toolbar of your choice Right-click the Smiley Assign Macro Click SheetFind, click OK, click Close. Click the Smiley to run the macro. If you want to remove the Smiley, Customize and drag it back into the dialog box. You can also change the way it looks: Customize, right-click Change Button Image. Have fun. Regards Steve |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
selecting a single sheet from a volume of sheets in a workbook
No News wrote: Fantastic Steve. It has helped ver..................ry much. and thanks a lot. If any further help is needed I can contact you. and Can I introduce your name to my various friends of beginers to learn more. Thanks and regards, No News. Hi No News Thanks for the feedback. I'm not very often able to view and help out on these newsgroups (it's a slow Friday afternoon at work) but there are always plenty of people here who willing to help (and a lot of them with much greater expertise than me), so just keep posting and practising and you'll be fine. Regards Steve |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
selecting a single sheet from a volume of sheets in a workbook
Thank you steve.
Well noted your advise. And let GOD's grace be with all of us. Thanks and regards, No News "Scoops" wrote in message ups.com... No News wrote: Fantastic Steve. It has helped ver..................ry much. and thanks a lot. If any further help is needed I can contact you. and Can I introduce your name to my various friends of beginers to learn more. Thanks and regards, No News. Hi No News Thanks for the feedback. I'm not very often able to view and help out on these newsgroups (it's a slow Friday afternoon at work) but there are always plenty of people here who willing to help (and a lot of them with much greater expertise than me), so just keep posting and practising and you'll be fine. Regards Steve |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
selecting a single sheet from a volume of sheets in a workbook
Now that you know how/where to place a macro, try this one from Bob Phillips
Sub BrowseSheets() Const nPerColumn As Long = 38 'number of items per column Const nWidth As Long = 13 'width of each letter Const nHeight As Long = 18 'height of each row Const sID As String = "___SheetGoto" 'name of dialog sheet Const kCaption As String = " Select sheet to goto" 'dialog caption Dim i As Long Dim TopPos As Long Dim iBooks As Long Dim cCols As Long Dim cLetters As Long Dim cMaxLetters As Long Dim cLeft As Long Dim thisDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As OptionButton Application.ScreenUpdating = False If ActiveWorkbook.ProtectStructure Then MsgBox "Workbook is protected.", vbCritical Exit Sub End If On Error Resume Next Application.DisplayAlerts = False ActiveWorkbook.DialogSheets(sID).Delete Application.DisplayAlerts = True On Error GoTo 0 Set CurrentSheet = ActiveSheet Set thisDlg = ActiveWorkbook.DialogSheets.Add With thisDlg .Name = sID .Visible = xlSheetHidden 'sets variables for positioning on dialog iBooks = 0 cCols = 0 cMaxLetters = 0 cLeft = 78 TopPos = 40 For i = 1 To ActiveWorkbook.Worksheets.Count If i Mod nPerColumn = 1 Then cCols = cCols + 1 TopPos = 40 cLeft = cLeft + (cMaxLetters * nWidth) cMaxLetters = 0 End If Set CurrentSheet = ActiveWorkbook.Worksheets(i) cLetters = Len(CurrentSheet.Name) If cLetters cMaxLetters Then cMaxLetters = cLetters End If iBooks = iBooks + 1 .OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5 .OptionButtons(iBooks).text = _ ActiveWorkbook.Worksheets(iBooks).Name TopPos = TopPos + 13 Next i .Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24 CurrentSheet.Activate With .DialogFrame .Height = Application.Max(68, _ Application.Min(iBooks, nPerColumn) * nHeight + 10) .Width = cLeft + (cMaxLetters * nWidth) + 24 .Caption = kCaption End With .Buttons("Button 2").BringToFront .Buttons("Button 3").BringToFront Application.ScreenUpdating = True If .Show Then For Each cb In thisDlg.OptionButtons If cb.Value = xlOn Then ActiveWorkbook.Worksheets(cb.Caption).Select Exit For End If Next cb Else MsgBox "Nothing selected" End If Application.DisplayAlerts = False .Delete End With End Sub Gord Dibben MS Excel MVP On Fri, 30 Jun 2006 19:42:44 +0530, "No News" wrote: Fantastic Steve. It has helped ver..................ry much. and thanks a lot. If any further help is needed I can contact you. and Can I introduce your name to my various friends of beginers to learn more. Thanks and regards, No News. "Scoops" wrote in message oups.com... No News wrote: Help me pls. Hi No News With the sheet containing the macro open: Right-click the toolbar Customize Click the Command tab Scroll down the Categories and click Macros Drag the Smiley face onto the toolbar of your choice Right-click the Smiley Assign Macro Click SheetFind, click OK, click Close. Click the Smiley to run the macro. If you want to remove the Smiley, Customize and drag it back into the dialog box. You can also change the way it looks: Customize, right-click Change Button Image. Have fun. Regards Steve Gord Dibben MS Excel MVP |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
selecting a single sheet from a volume of sheets in a workbook
Dear Gord Dibben
There you are. As I am in India, I could able to see your message today only and it works for me to meed my needs in a highly sophisticated angle and it is somthing wonderfull. Welldone. and thank you for your help. These type of messages are the root of the newgroups. Thanks. "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Now that you know how/where to place a macro, try this one from Bob Phillips Sub BrowseSheets() Const nPerColumn As Long = 38 'number of items per column Const nWidth As Long = 13 'width of each letter Const nHeight As Long = 18 'height of each row Const sID As String = "___SheetGoto" 'name of dialog sheet Const kCaption As String = " Select sheet to goto" 'dialog caption Dim i As Long Dim TopPos As Long Dim iBooks As Long Dim cCols As Long Dim cLetters As Long Dim cMaxLetters As Long Dim cLeft As Long Dim thisDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As OptionButton Application.ScreenUpdating = False If ActiveWorkbook.ProtectStructure Then MsgBox "Workbook is protected.", vbCritical Exit Sub End If On Error Resume Next Application.DisplayAlerts = False ActiveWorkbook.DialogSheets(sID).Delete Application.DisplayAlerts = True On Error GoTo 0 Set CurrentSheet = ActiveSheet Set thisDlg = ActiveWorkbook.DialogSheets.Add With thisDlg .Name = sID .Visible = xlSheetHidden 'sets variables for positioning on dialog iBooks = 0 cCols = 0 cMaxLetters = 0 cLeft = 78 TopPos = 40 For i = 1 To ActiveWorkbook.Worksheets.Count If i Mod nPerColumn = 1 Then cCols = cCols + 1 TopPos = 40 cLeft = cLeft + (cMaxLetters * nWidth) cMaxLetters = 0 End If Set CurrentSheet = ActiveWorkbook.Worksheets(i) cLetters = Len(CurrentSheet.Name) If cLetters cMaxLetters Then cMaxLetters = cLetters End If iBooks = iBooks + 1 .OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5 .OptionButtons(iBooks).text = _ ActiveWorkbook.Worksheets(iBooks).Name TopPos = TopPos + 13 Next i .Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24 CurrentSheet.Activate With .DialogFrame .Height = Application.Max(68, _ Application.Min(iBooks, nPerColumn) * nHeight + 10) .Width = cLeft + (cMaxLetters * nWidth) + 24 .Caption = kCaption End With .Buttons("Button 2").BringToFront .Buttons("Button 3").BringToFront Application.ScreenUpdating = True If .Show Then For Each cb In thisDlg.OptionButtons If cb.Value = xlOn Then ActiveWorkbook.Worksheets(cb.Caption).Select Exit For End If Next cb Else MsgBox "Nothing selected" End If Application.DisplayAlerts = False .Delete End With End Sub Gord Dibben MS Excel MVP On Fri, 30 Jun 2006 19:42:44 +0530, "No News" wrote: Fantastic Steve. It has helped ver..................ry much. and thanks a lot. If any further help is needed I can contact you. and Can I introduce your name to my various friends of beginers to learn more. Thanks and regards, No News. "Scoops" wrote in message oups.com... No News wrote: Help me pls. Hi No News With the sheet containing the macro open: Right-click the toolbar Customize Click the Command tab Scroll down the Categories and click Macros Drag the Smiley face onto the toolbar of your choice Right-click the Smiley Assign Macro Click SheetFind, click OK, click Close. Click the Smiley to run the macro. If you want to remove the Smiley, Customize and drag it back into the dialog box. You can also change the way it looks: Customize, right-click Change Button Image. Have fun. Regards Steve Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting Last Sheet | Excel Worksheet Functions | |||
Combining data from cells from several excel sheets to a new sheet | Excel Discussion (Misc queries) | |||
Multiple sheets as data for a single sheet | Excel Worksheet Functions | |||
Lookup values in multipul sheets and show value in another sheet | Excel Worksheet Functions | |||
Save a single sheet from a workbook | Excel Discussion (Misc queries) |