Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating worksheets from a database
I recently wrote code that automatically creates a new worksheet using data from an account column in the excel database worksheet. The newly created worksheet is used to store extra data about clients in the base worksheet called "Database". The database worksheet and "extra" worksheets are all contained in one workbook.xls file, making portabilty of the document easy. The database workshhet looks something like this last first address city state zip phone account By placing the cursor on a specific record and clicking on the create supplement worksheet button, the new worksheet is created and it is named using the account number from the appropriate column. It all works fine until I try to write code to automatically call up one of the created worksheets. I can not seem to get code written to call up the worksheet based on the account number from the account column. Any guru's out there with an idea? Include your phone number if you care to. I will call you on my nickel. Paul Veilleux Garland, Texas 972-278-2031:) I am legally blind. Sometimes I prefer to talk than read. -- PaulVeilleux ------------------------------------------------------------------------ PaulVeilleux's Profile: http://www.excelforum.com/member.php...o&userid=37899 View this thread: http://www.excelforum.com/showthread...hreadid=574338 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating worksheets from a database
Hi, Paul-
This is a snippet of code I wrote for another project that does the same thing: land the cell pointer in a range that contains tab names, then jump to that tab. Please note because of word wrapping on this Usenet interface, some line breaks may need to be adjusted in the VBA editor. Sub Jump() Dim GoToTab As String 'Validation: ensure the cell pointer is in the correct range 'Suppose your list of tabs occurs in cell A10 thru A20. Use this to make sure the cellpointer 'is in the proper range. There are more elegant ways to do this if the range will expand: 'for instance, use a Named Range that will expand as the selection expands, then use additional 'validation to make sure the cell pointer is within the range. This will work for now. If ActiveCell.Column < 10 Or ActiveCell.Row < 10 Or ActiveCell.Row 20 Then MsgBox "To jump to a tab, place the cell pointer on a tab inside the valid range and then press the Jump button." End End If GoToTab = ActiveCell.Value 'unhide the sheet, if it is hidden Sheets(GoToTab).Visible = True 'go to tab Sheets(GoToTab).Select Range("a1").Select End Sub Dave O |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating worksheets from a database
Argh! Tested this code on existing data but forgot to change it back
to fit the example of data in A10:A20. The column reference should be 1, not 10, as below: Sub Jump() Dim GoToTab As String 'Validation: ensure the cell pointer is in the correct range 'Suppose your list of tabs occurs in cell A10 thru A20. Use this to make sure the cellpointer 'is in the proper range. There are more elegant ways to do this if the range will expand: 'for instance, use a Named Range that will expand as the selection expands, then use additional 'validation to make sure the cell pointer is within the range. This will work for now. If ActiveCell.Column < 10 Or ActiveCell.Row < 10 Or ActiveCell.Row 20 Then MsgBox "To jump to a tab, place the cell pointer on a tab inside the valid range and then press the Jump button." End End If GoToTab = ActiveCell.Value 'unhide the sheet, if it is hidden Sheets(GoToTab).Visible = True 'go to tab Sheets(GoToTab).Select Range("a1").Select End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating worksheets from a database
Argh! Tested this code on existing data but forgot to change it back
to fit the example of data in A10:A20. The column reference should be 1, not 10, as below: Sub Jump() Dim GoToTab As String 'Validation: ensure the cell pointer is in the correct range 'Suppose your list of tabs occurs in cell A10 thru A20. Use this to make sure the cellpointer 'is in the proper range. There are more elegant ways to do this if the range will expand: 'for instance, use a Named Range that will expand as the selection expands, then use additional 'validation to make sure the cell pointer is within the range. This will work for now. If ActiveCell.Column < 1 Or ActiveCell.Row < 10 Or ActiveCell.Row 20 Then MsgBox "To jump to a tab, place the cell pointer on a tab inside the valid range and then press the Jump button." End End If GoToTab = ActiveCell.Value 'unhide the sheet, if it is hidden Sheets(GoToTab).Visible = True 'go to tab Sheets(GoToTab).Select Range("a1").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help needed on creating Excel database | Excel Discussion (Misc queries) | |||
creating mutiple worksheets | Excel Discussion (Misc queries) | |||
Creating multiple reports from a database | Excel Discussion (Misc queries) | |||
Links to External Database | Excel Discussion (Misc queries) | |||
PLEASE HELP!? Creating a simple database with excel, minor setback | Excel Worksheet Functions |