Creating worksheets from a database

[PaulVeilleux]

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.

 

[Dave O]

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

 

[Dave O]

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

 

[Dave O]

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