Macro to edit a macro

[Chax]

I am working with a project right now with sheets from 1 - 280, but my problem is that when a new project starts the sheets will be renamed to something else.. EX. P1110-01.

I do not want to make a new macro every single time I work with a project, because I work with like 600 projects.

Can anyone point me in a direction to create a macro so that when the sheets are renamed, it retrieves the new sheet names and edits the old macro. Not sure if it is possible though.

Sub PileLocationFix_INSTALLandRECEIVED()

'

' PileLocationFix_INSTALLandRECEIVED Macro

' Fixes the #REF problems.

'

' Keyboard Shortcut: Ctrl+q

'

    ActiveCell.FormulaR1C1 = "='[Pile Records.xlsx]1'!R70C[-1]"

    Range("C6").Select

    Selection.AutoFill Destination:=Range("C6:C306"), Type:=xlFillDefault

    Range("C6:C306").Select

    ActiveWindow.SmallScroll Down:=-12

    ActiveWindow.ScrollRow = 288

    ActiveWindow.ScrollRow = 285

    ActiveWindow.ScrollRow = 279

    ActiveWindow.ScrollRow = 268

    ActiveWindow.ScrollRow = 245

    ActiveWindow.ScrollRow = 218

    ActiveWindow.ScrollRow = 207

    ActiveWindow.ScrollRow = 194

    ActiveWindow.ScrollRow = 182

    ActiveWindow.ScrollRow = 173

    ActiveWindow.ScrollRow = 167

    ActiveWindow.ScrollRow = 161

    ActiveWindow.ScrollRow = 156

    ActiveWindow.ScrollRow = 152

    ActiveWindow.ScrollRow = 148

    ActiveWindow.ScrollRow = 146

    ActiveWindow.ScrollRow = 142

    ActiveWindow.ScrollRow = 141

    ActiveWindow.ScrollRow = 137

    ActiveWindow.ScrollRow = 133

    ActiveWindow.ScrollRow = 125

    ActiveWindow.ScrollRow = 120

    ActiveWindow.ScrollRow = 114

    ActiveWindow.ScrollRow = 106

    ActiveWindow.ScrollRow = 97

    ActiveWindow.ScrollRow = 87

    ActiveWindow.ScrollRow = 78

    ActiveWindow.ScrollRow = 67

    ActiveWindow.ScrollRow = 59

    ActiveWindow.ScrollRow = 51

    ActiveWindow.ScrollRow = 42

    ActiveWindow.ScrollRow = 34

    ActiveWindow.ScrollRow = 27

    ActiveWindow.ScrollRow = 19

    ActiveWindow.ScrollRow = 10

    ActiveWindow.ScrollRow = 6

    Range("C6").Select

    ActiveCell.FormulaR1C1 = "='[Pile Records.xlsx]1'!R[64]C[-1]"

    Range("C7").Select

    ActiveCell.FormulaR1C1 = "='[Pile Records.xlsx]2'!R[63]C[-1]"

.

.

.

.

.

    ActiveCell.FormulaR1C1 = "='[Pile Records.xlsx]278'!R[-210]C[-1]"

    Range("C281").Select

    ActiveCell.FormulaR1C1 = "='[Pile Records.xlsx]279'!R[-211]C[-1]"

    Range("C6:C281").Select

    Range("C281").Activate

    Selection.Copy

    Range("B6").Select

    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _

        SkipBlanks:=False, Transpose:=False

    Range("D6:BP6").Select

    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _

        SkipBlanks:=False, Transpose:=False

End Sub

[Norie]

What is the code for?

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

[jaslake]

Hi Chax

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

[Chax]

The code grabs information from each sheet, and compiles it all into one sheet in another notebook.

My problem is... I have to rename these workbooks AND sheets within the workbook.

I have a template built, and I know to keep all workbooks open but other employees do not. So, if they make a mistake.... No matter what project they are on I want it to be able to be fixed at the stroke of a couple keys, instead of it taking me 20 minutes to fix for EACH of the projects.

I am kind of new and have had this thrown at me, so I am sorry that I dont know all of the technical terms.

[Norie]

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?

[Chax]

='[Co 4713 Pile Records.xlsx]1'!B$70

Is the original code that is being changed by the macro. The macro is changing the 1 to whatever it needs from 1 - 200, and it is removing the $.

I am doing this so that I will copy the column and then highlight the entire row I am using and pasting it there.. And after that it gives me a 200 by 200 table of information

[Chax]

What I need to macro to do is... Change the code INSIDE the original macro.

I HAVE to rename the sheets as they signify a specific location on a project.

Is this possible?

[Richard Buttrey]

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.

[Chax]

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.

[Richard Buttrey]

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.

[Norie]

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?

[Chax]

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.

[Norie]

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.

Dim wbPile As Workbook
Dim wsPile As Worksheet
Dim wbSummary As Workbook
Dim wsSummary As Worksheet
Dim rng As Range

    Set wbSummary = ThisWorkbook
    Set wsSummary = ThisWorkbook.Sheets("Summary")
    
    Set rng = wsSummary.Range("C6")
    
    Set wbPile = Workbooks.Open("C:\Pile Records.xlsx")
    
    For Each wsPile In wbPile.Worksheets
        rng.Formula = "='[" & wbPile.Name & "]" & wsPile.Name & "'!B70"
        Set rng = rng.Offset(1)
    Next wsPile

[Chax]

Ok I have taken a picture of the ranges. They go from A70 to BO70.


Here is a photo for more visual help.


Untitled.png

It is grabbing information from the pile record workbook, and this is what it looks like:

Untitled1.png

[Chax]

Also, thank you for taking the time to help me out. It is deeply appreciated.