Macro to edit a macro

What is the code for?

Perhaps it could be changed to work without a hard-coded sheet name?

Hi Chax

Perhaps use sheet code names or sheet index...what's the process?

The code you posted doesn't appear to do what you describe.

Can you post the code that grabs and compiles information from each worksheet?

Please explain the whole process with a clear descriptive summary - as requested by Jaslake.
Please don't assume that you need to change the code inside the macro. Since you're asking for advice let us decide what the optimum solution might be and don't get hung up on what you currently have.

It sounds like you might want to compile a list of sheet names from a second workbook but until you explain then I for one am unclear.

My original macro is setup for a template to reset everything if a huge error occurs (and it definitely does), and what it does exactly is changes
='[EMIT Pile Records.xlsx]1'!B$69 I have 200 or so formulas, and the macro goes back and changes the 1 to whatever number is needed and it also removes the $ in order for me to be able to copy and paste the formula into other areas.
.

It resets it all back to the generic 1 to 200 setup basically.




1
2
3
4
P-005
6
P-007
P-008
9
P-010


That is an example of the first 10 sheets of a workbook. The macro needs to be dynamic, because the 9 will eventually change to P-010 once the object is installed.

So the code looks like this.

='[EMIT Pile Records.xlsx]1!B69
='[EMIT Pile Records.xlsx]2'!B69
='[EMIT Pile Records.xlsx]3'!B69
='[EMIT Pile Records.xlsx]4'!B69
='[EMIT Pile Records.xlsx]P-005'!B69
='[EMIT Pile Records.xlsx]6'!B69


BUT. What I need is for the macro to be dynamic, and when the sheet names are changed I need for the macro to change as well.

Like you see in the 5th sheet. If the macro could change to P-005 and stay like that.. THen it would be fantastic.

I have the first macro completed, but no idea how to make it dynamic, or make a second.

Hope that is more clear and concise.

But what are you trying to do? It still isn't clear.

Are you trying to gather information from several workbooks and put it into a master workbook?

As I said please explain your whole process step by step and say what you want to achieve. Don't assume anything, least of all what any macro code might be.

This can be done by looping through all the worksheets, so a name change shouldn't matter.

Can you clarify what ranges you are dealing with?

Untitled.pngI am gathering information from one workbook. A "Field Pile" workbook. Then my "Install SUmmary" workbook is retrieving the information from the field pile workbook.

All I need is ONE column of coding done, and then I can copy and paste that column across my rows to complete the rest.

I can only tell so much and show so much as I signed a confidentiality agreement,, but I can easily put the ranges in myself.

It's hard to post any code without knowing the ranges involved.

For example where do the formulas go and what ranges should they refer to.

I've looked at the code and that isn't clear.

Perhaps this will give you an idea of how it could be approached.

Please Login or Register  to view this content.