Invoices details

[tofimoon4]

Dear sir,with best regards,i have in attached file ( main ) sheet and
about (26) sheets with nos.(1-18),each no.equal to (agent ) name as an
abbreviation to decrease the areas,i ask your goodselves if there is a
code to take me directly to any agent sheet when i press its no. or name
from the ( main) sheet? i mean ( if i want to go to (R) sheet i must
press (R) from the scheduale in main sheet ), iwant to do this because i
have about (50) agents with serial nos. and it is difficult to guess
there sheets nos.without taking a look to the scheduale.this is the
first . the second , is it possible to amend the code to transfer (2or3)
invoices details to each sheet with (2or3)agents names?i mean one sheet
for (2or3) agents.
Thanking you in advance.


+-------------------------------------------------------------------+
|Filename: new.zip |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=163|
+-------------------------------------------------------------------+

 

[Don Guillett]

From one I use on a menu sheet. Right click the sheet tab>view
code>copy/paste this. Since you have the sheet number in column M this works
if you click on any number in column M

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column <> Columns("M").Column Then Exit Sub
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(target)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
'GetWorkbook ' calls another macro to do that
Else
Application.GoTo Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

 

[tofimoon4]

Dear sir,many thanks for your interest ,i pasted the code in (main sheet
module) but got nothing when i have pressed any no. in (M) column.
I think i have a mistake in somewhere.
I need your advice,please.
Thankyou again.

 

[Don Guillett]

DOUBLE_click

 

[tofimoon4]

Dear sir,i dont know what and where is the problem , i have clicked any
no. from (M) column and nothing changed.
Please ,can you (when you have a time) paste the code on my example to
show me how it works?
I am so sorry for insistence .

 

[Patrick Molloy]

right click on the SHEET TAB then from the pop-up menu select 'View Code'
paste into the code window Don Guillett's code. it works

As before, you're file is not available to those of us who do not or cannot
register with thecodepage.com

 

[Don Guillett]

Maybe you had linewrap which you should have been able to correct? Your
excel version?
Try this where I have accounted for line wrap. If this doesn't work, send
your file directly to my address along with the way you have it. NO reason
for it not to work

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> Columns("M").Column Then Exit Sub
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
'GetWorkbook ' calls another macro to do that
Else
Application.GoTo Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

 

[Don Guillett]

After you sent your file, I see that you failed to follow my instructions,
also mentioned by Patrick, to right click the SHEET tab. NOT a regular
module.. Simply move the code to the MAIN sheet module.

 

[tofimoon4]

Thank you very much my sir for this solution,really you are giant ,it
works very well now.

 

[tofimoon4]

Dear sir,with my regards and admiration,i want to ask if is it possible
to modify your excellent code to include (J) column in addition to (M).
Thanks in advance.

 

[Don Guillett]

As I mentioned before, it would be better to NOT bother with the sheet
numbers. Instead just use the a,b,c, etc to name the sheets.Then you simply
do away with L & M columns and rewrite my code to goto the sheet NAME
clicked in column J.

OR, better yet, design the file to have ONLY one sheet where you use
data>filter>autofilter to see the individuals, as needed. You would do away
with the total column and use SUBTOTAL at the top instead.

 

[tofimoon4]

Dear sir,with my appreciation to your suggestion,i have built my program
(as you have seen) like my example,and it is difficult to change (some
or all) its contents now,i asked your goodself in my request --(if is it
possible--) because this change will help me more.
Thanks in advance.

 

[Don Guillett]

No time like the present to learn better ways. Laziness is NO excuse. Use
this instead.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
If Target.Column = Columns("M").Column Then _
WS = Trim(Target.Value)
If Target.Column = Columns("J").Column Then _
WS = Columns("L").Find(Target, _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Offset(1, 1)
If WS = "" Then Exit Sub
On Error Resume Next
If Sheets(WS) Is Nothing Then
'GetWorkbook ' calls another macro to do that
Else
Application.GoTo Sheets(WS).Range("b4")
End If
Application.DisplayAlerts = True
End Sub

 

[tofimoon4]

Dear sir,with my regards,more than (55)viewers through more than latest
(30) pages means : you are the hero,you are the excellent.
Thank you very much for your experience.