Microsoft Excel - Trying to run subs after selecting sheets also in separate sub

Hello all,

I am trying to accomplish the following, and would like some input:

I have a macro which creates a workbook containing 22 sheets then it renames the sheet according to 21 different locations and the 22nd sheet is renamed "summary"
I also have made Macros for 2 types of tables  that may be found in the locations multiple times.
Each location has different numbers of locations (and would need to have separate counts for each location)
here is an example

sheet1 is created and renamed city1, then has 5 stores and 3 kiosks put on that sheet
sheet2 is then created renamed city2 then has 3 stores and 5 kiosks put on that sheet.

I have a Macro which creates the sheets, I also have a macro which can create the lists for stores and a macro to create the lists for kiosks.

Without putting up code for proprietery reasons, does what I am asking make sense, or do you need me to clarify?

You can keep your Macro in module one. You must use the worksheet change event in the worksheet itself, not a module.
Let me explain:

I will provide a file to go along with the explanation. In the worksheet, range A5:A25 is filled with numbers.
If you enter a numeric value in cell D10, range G5:G10 will copy what is in A5:A25. If you delete the value in D10, then the contents of range G5:G10 will be deleted.

http://www.4shared.com/file/89145449/43c8c38/Change_Value_of_D10.html

Whatever your original code was to run your Macro, put that back into its original form.

Since you are using Excel 2007, this is what you need to do:

• 1) Click on the Developer tab.
• 2) Click on the Visual Basic icon.
• 3) On the left pane window, double-click the sheet where you need your code to run.
• 4) Now, at the top of the code window you will see (General) with a drop down, and (Declarations) with a drop down.
• 5) Click the drop down by (General) and select Worksheet.
• 6) Now in the code window you will see Private Sub Worksheet_SelectionChange(ByVal Target As Range)
• 7) Remove the word "Selection". You want to remove "Selection" because that means when you click on a cell in the worksheet something will happen. You do not want that, you want to enter a value in D10. It should now read Private Sub Worksheet_Change(ByVal Target As Range)
• 8) This is where you want the code...

Private Sub Worksheet_Change(ByVal Target As Range)   

If Target.Address = "$D$10" Then   

Call MyMacro   

End If   

End Sub 

• 9) Meaning, when you change the value in D10, the worksheet change event will "Call" your Macro.

Hi Stephen P, I have read your post many time, but still unclear. Please attach your sample file with explain in that. Pichart

Hi Stephen P,

Today have a little time to see your, question again..

One suggestion is you should call the 2nd code (to crate the table, immediately the first code create the new work sheet...

If you use the looping code, then this will be short and much more easy. I think this is not difficult at all.

For I have no idea about the way your macro work, then cannot suggest where to insert the code...

If you attach your file, or you code...this will be more easy to give support.

Pichart Y.

How to attach the file...