2 different macros to run on two different sheets in a workbook

[azkhan]

Hi There,

I have two macros (see attached macro1 & macro2) in two different sheets (Process Wise TAT & Dept Wise TAT) in a workbook. Also I have numerous sheets with details of process activities and their turn around time (TAT). In sheets Process Wise TAT & Dept Wise TAT i have set few formulas to fetch data from other sheets and do certain calculations to display end result.

I want the attached macros to hyperlink sheets with details. However macro1 is working fine but macro two is throwing error (highlighted in Yellow). Please can someone help me fix this.

[Marcol]

Why post screenshots?

You will get a better response if you post the code for each macro using code tags (#)

Type or Paste your macro then click > go advanced

Highlight your code and click the # button

e.g.

Please Login or Register  to view this content.

Cheers

[azkhan]

Thanks Marcol. Another learning as a new user.

Here are the macros:

macro1:

Please Login or Register  to view this content.

macro2:

Please Login or Register  to view this content.

[Marcol]

I am finding it difficult to work out what you want done.

Could you post a sample workbook to clarify things?

It should clearly illustrate your problem and not contain any sensitive data.

[azkhan]

Hi,

Please find attached workbook that contains sampe worksheets.

What i have in work book:

- Numerous sheets that contain details of the projects.
- Two sheets:
1. Process Wise TAT: this sheet contains TAT (turn around time) of the process activities that are carried out to complete a project. Data is being pulled from project sheets( 100001 - 100003) using few formulas.

Note: Data start point destination cells are same for all sheets with project details.

2. Dept Wise TAT: this sheet contains Time taken for complettion by each Dept (Maker & Checker) for a project. Data is being pulled from project sheets( 100001 - 100003) using few formulas.

I am trying to get done the following:

1. As and when a sheet (project details) is added, hyperlink should be listed automatically in Process Wise TAT and Dept Wise TAT (E.g. B4:B10000).

2. In every sheet with project details a return link should be created. For Process Wise TAT link with text 'Back to Process Wise TAT' in E5 and for Dept Wise TAT link with text 'Back to Dept Wise TAT' in B5.

Note: There should be any to and from hyperlinks between the Process Wise TAT and Dept Wise TAT sheets.


Additional requirement:

As I have set formulas in Process Wise TAT and Dept Wise TAT to fetch details from other sheets, I would like these formulas to run automatically if possible.

I would be grateful if someone can help me with this.

Many thanks in advance.

Cheers,
Az.

[royUK]

I too can't tell what you are doing or want. The example isn't much help because you haven't even added the code. At a guess,maybe

Please Login or Register  to view this content.

[azkhan]

Hi Roy,

Here's my workbook with codes. when I tried the codes I have mentioned earlier worked fine with the sample workbook and did what I wanted (hyperlinking back and forth). But when I used the same codes on the original workbook I am getting an error on th e following code for sheet 'Dept Wise TAT' but the code for sheet 'Process Wise TAT' is working fine.

Please Login or Register  to view this content.

[royUK]

There's no attachment & what error are you getting?

Which sheet will have the button?

[azkhan]

Hi Roy,

Would be grateful if I can get help on the second requirement as well. Just so you know, I don't know how to write codes myself but with the help of my colleague (who knows basic coding) I tweaked the code I got from the link below to meet my requirements.

http://www.ozgrid.com/VBA/sheet-index.htm

[azkhan]

Oops! forgot the attachement. Sorry about that. Here it is...

[royUK]

It seems to work for me

[azkhan]

what error are you getting?

Please see ataached.

Which sheet will have the button?

Just to make it simpler i have removed the button codes.

[royUK]

I can't duplicate the error in your example. Are the sheets named differently in the other workbook?

[azkhan]

Hi Roy,

Looks like it got auto-corrected and it is working fine now. Thanks for all your efforts.

Could you please help me with second requirement if possible?

Additional requirement:

As I have set formulas in Process Wise TAT and Dept Wise TAT to fetch details from other sheets, I would like these formulas to run automatically if possible.

[royUK]

I don't understand your second requirement,unless Calculation is set to manual then formulas should update

[azkhan]

Sorry for a vague requirement description.
In 'Process Wise TAT' sheet I have formulas (C4 to K4 and rows down) for pulling details from other project sheets. Likewise in 'Dept Wise Sheet' I have formulas from C4 to H4 and rows down.

Whenever i add a new project sheet to the workbook, i need to write and run these formulas manually. I was just wondering if there is a way to have these formulas run automatically if a new sheet is added.

E.g.:

In Process Wise TAT sheet if a new project sheet is added (100004) cell C7 should pick the formula from cell C6 ('100003'!$C$3) and replace the sheet name in the formula with new sheet name ('100004'!$C$3) or any other way to auto-insert this formula.

I have tried to put it the best way possible however if it is still confusing please let me know.

Cheers,
Az

[azkhan]

Would highly appreciate if someone can help me with this please.

[royUK]

I suggest that you start a new thread for this other question, with a link to this one