Macro formula to Replicate VBA Recording from one tab to all other tabs

[Lowbob131]

Hello Board,
If anyone knows how to do the following that would be very helpful.

I have a large excel with 100 different tabs which currently includes many hardcodes. Most of the tabs have the same structure which is helpful. I want to create a new tab where I can consolidate all similar datapoints that are currently included in all those tabs and make the excel dynamic where if I change a datapoint in that new tab this changes for example tab number 51.

What I want to add are the following steps:
1. Record a Macro with the following steps: I go in tab 1, copy paste cell L10 to a new tab I created as Value, Copy that Value from the new tab and paste it as a link to the original L10 cell in tab 1, therefore replacing the original hardcoded value as a link input from the new tab I just created.
2. Take this Recorded Macro Code and include an other code that does the exact same steps to all the other 100 tabs in the excel. Basically copying L10 cell in all tabs 1-100, copying pasting as a value L10 in the new tab I created and re-link these values I just copied in the new tab as links to all the original L10 cells in all tabs 1-100. In the new tab, it can put all the hardcoded L10 values from all tabs 1-100, one below the other (populate all cells in that new tab from B1 - B100)



2 other questions:
1. Is there a quick way using a macro, to copy paste all the tab names from tabs 1-100 to a new tab as Values so that I can use an Indirect formula so that I dont have to copy past each individual tab's name manually into the new tab?

2. On step 2 above, is there any way to write in the VBA Code to only do this steps in tabs that are in between Tab3 >>> and <<<Tab55 for example. So it will pick cells L10 for all tabs that are in between those 2 tabs (i.e. Tab4 all the way to Tab54) ?
I have seen this SUM formula to be working and takes and sums the same cell across different tabs: =SUM('Tab3>>>:<<<Tab55'!L10) --> this formula picks up all L10 cells in tabs that are between Tab3 and Tab55 and it sums them up. Is there a similar formula for the macro to only do this macro between the tabs that are located between these 2 tabs?

Thank you for your help!! Please let me know if anything is unclear

 

[bebo021999]

Without sample file, I tried to follow your requirement though not 100% matchs.

Book2.xlsm

drive.google.com

Option Explicit
Sub test()
Dim startWS&, endWS&, i&, lr&, celink As String, ws As Worksheet
startWS = 1 ' adjust to actual sheet index start
endWS = 55 ' adjust to actual sheet index end
celink = "L10" ' adjust to actual cell address in each sheet need to link

'Add a new sheet named "Master"
If Not Evaluate("=ISREF(Master!A1)") Then
    ActiveWorkbook.Worksheets.Add before:=Sheets(1)
    ActiveSheet.Name = "Master"
End If
Sheets("Master").Cells.ClearContents

For i = startWS To endWS ' loop from sheet 1 to sheet 55
    If i <= Sheets.Count Then
        Set ws = Sheets(i)
        If ws.Name <> "Master" Then ' And i <> 10 and i <> 11 and ... if sheet 10,11 was excluded
            With Sheets("Master")
                lr = .Cells(Rows.Count, "B").End(xlUp).Row ' the last used cell in column B
                .Cells(lr + 1, "B").Value = ws.Range(celink).Value ' get the value from sheet(i)
                .Cells(lr + 1, "A").Value = ws.Name ' get the sheet(i) name
                ws.Range(celink).Formula = "=Master!B" & lr + 1 ' create link to Master sheet
            End With
        End If
    End If
Next
End Sub

 

[Lowbob131]

Hi bebo - Many thanks for your reply!
Apologies I'm not an expert at VBA and I have a hard time replicating your formula.
If we have already set up the Master tab and I want to replicate this macro to different cells, what are the steps to do it?
I tried to copy the code you sent and change the cell reference and it wasn't working.

I have pasted a link to a sample excel so you have an example of what I need to do

Excel -Macro xlsm.xlsm

Master Tab Name ,Cell 1 value,Tab name,Cell 2 value Tab name ,Cell 3 value

docs.google.com

In this excel, what is the macro I need to put so I have the same steps as above and do this for these 3 cells?
Also is there a way to tell the VBA to take only the cells that are in the tabs between R>>> tab and <<<L tab and only pick up the cells between those tabs rather than all the excel.


If I want to use this code in another excel, can you please clearly highlight to me what parts of the code I need to change to adjust it to my new excel ?

I.e. where do I change the original cell reference (Cell 1 , Cell 2, Cell 3)
If tab is not called "Master" in my other excel, how do I change the code so that it does the macro in Tab called "Transfer" for example
Where do I change the location it puts the pasted values, for example instead of A2 / B2 in the Master tab now it should be in E4 / F4
How do I replicate this macro to run in other tabs that are in Between tabs "A>>>" and "<<<B" rather than the original "R>>>" and "<<<L"


Sorry I m not an expert in VBA. Thank you very much, please let me know if any questions on this.

 

[bebo021999]

Could you input the expected value manually in to Master sheet?
I need to see what the output data in Master sheet look like.

 

[Lowbob131]

Could you input the expected value manually in to Master sheet?
I need to see what the output data in Master sheet look like.

It’s exactly the same way you did it before in the excel you sent me, so tab name and cell value on the Master tab and then that cell value (number) is getting linked back to the original cell as a link reference. Same thing you did originally.

Only difference it’s done for 3 other cells in these tabs and it would only need to pick up the tabs between the two mentioned above.

Thank you!

 

[Lowbob131]

Hi Bebo,

Please see below 2 links. 1st is the original problem and 2nd excel shows how it should look at the end based on macros. Please let me know how I can adjust the macro formulas for this purpose. Let me know if anything is unclear. Thank you

Excel -Macro Problem.xlsm

Master Tab Name ,Cell 1 value,Tab name,Cell 2 value Tab Name ,Cell 3 value

docs.google.com

Excel -Macro Answer.xlsm

Master Tab Name ,Cell 1 value,Tab name,Cell 2 value Tab1,234324,Tab1,151 Tab2,2411,Tab2,12 Tab3,45433,Tab3,5 Tab4,123,Tab4,358 Tab5,1552,Tab5,713 Tab6,123123,Tab6,144 Tab7,6542,Tab7,1574 Tab Name ,Cell 3 value Tab1,123421 Tab2,13435645 Tab3,16555512365 Tab4,7568562 Tab5,3451231 Tab6,12475686 Ta...

docs.google.com