Macro to update tab reference

[TS656577]

Looking for some help to use a macro (via button press) that would update the tab references in cell E8 (and thus E9-12). I have other cells as well that would need updated (for example, A14 has a different word, thus E14-E18 would need updated, and so on).

The tab references are the text shown in cell A8. It is based on user text input which is why it's not hardcoded. So for example, I'd like that after a macro, the code of E8 says ='Hello'!$D$1. Any help is appreciated.

 

[MARK858]

As a start try the below (please note that the single apostrophe's won't appear as Hello is a single word, they will if it is something like Hello World)

Sub ts656577()
Range("E8").Formula = "='" & Range("A8").Value & "'!D1"
Range("E8:E12").SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
End Sub

A14 has a different word, thus E14-E18 would need updated, and so on)

You need to provide a lot more info about your layout to provide any code for the rest of the cells (and preferably some sample data we can copy and paste into Excel, the board has an Addin called XL2BB you can use for doing this XL2BB - Excel Range to BBCode )

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

As Mark has said we may need more detail about the other ranges & formulas that need updating. However, I have taken a guess about what id happening. Test with a copy of your workbook.

Sub AdjustFormulas()
  Dim rA As Range, newR As Range
 
  For Each rA In Columns("E").SpecialCells(xlFormulas).Areas
    Set newR = rA.Cells(1).Offset(, -4)
    rA.Replace What:="'" & newR.Address(0, 0) & "'", Replacement:="'" & newR.Value & "'", Lookat:=xlPart
  Next rA
End Sub

 

[MARK858]

I prepared a guess as well (although uglier)...

Sub ts656577()
    Dim i As Long
    For i = 8 To Cells(Rows.Count, "A").End(xlUp).Row Step 6
        Cells(i, "E").FormulaR1C1 = "='" & Cells(i, "A").Value & "'!R1C[-1]"
        Cells(i, "E").Offset(1).Resize(4).FormulaR1C1 = "=R[-1]C"
    Next
End Sub

 

[TS656577]

All, thank you for your responses. I know I was a bit generic so thank you all for making a stab at a solution. I did have better luck with the code below despite getting an error when running the macro:
Sub ts656577()
Dim i As Long
For i = 8 To Cells(Rows.Count, "A").End(xlUp).Row Step 6
Cells(i, "E").FormulaR1C1 = "='" & Cells(i, "A").Value & "'!R1C[-1]"
Cells(i, "E").Offset(1).Resize(4).FormulaR1C1 = "=R[-1]C"
Next
End Sub

I have two sheets. One is the TestExample version. As I said, this didn't allow for the user to be able to modify anything. All of the "equipment items" were hardcoded but it wasn't representative of every project. I have filled out the resin tab to show you how it populates the Equipment Plan tab and how I want it to ultimately work in the end.

The Rev2 is the current "in process" version. I want it to be more versatile by being able to put the items needed in the "Project Scope" tab, which then populates the equipment plan tab. There is also a macro that when run, will copy/rename the VendorBlank tab to whatever is in the Project Scope Tab. Thus i feel like I need a second macro to then update all of the references in the E column (and L) to autopopulate with the newly renamed tabs.

I hope this makes sense. I don't think the minisheet will work so hopefully i'm allowed to use wetransfer?

https://wetransfer.com/downloads/1e18a3c872e569f61efa8571125870d620240207171820/a409036e9e4c79b1e161c6b88d9d477a20240207171848/b00bf1 [wetransfer.com]

 

[TS656577]

Thursday night bump

 

[MARK858]

I am afraid that the site you used is not one that I am willing to download from.
I would suggest either posting using XL2BB as suggested or uploading the file to either www.box.com or www.dropbox.com and I will have a look at some stage.
Remember to mark the file for sharing and post the link it creates if posting on either site.

 

[TS656577]

Here you go!

Copy of Capital Equipment Project Workbook - Rev2.xlsm

Shared with Dropbox

www.dropbox.com

Capital Equipment Project Workbook - TestExample.xlsm

Shared with Dropbox

www.dropbox.com

 

[MARK858]

Your error is due to a worksheet event code you have being triggered when the macro runs, you also get Ref errors because you haven't created the sheets yet.

I haven't done anything with column L as it is set up for an amount and not a sheet name (You can start a new thread for whatever you need there if necessary)

Sub ts656577()
    Dim i As Long
  
    With Application
        .EnableEvents = False
        .DisplayAlerts = False
    End With
  
    With Sheets("Equipment Plan")
        For i = 8 To .Cells(Rows.Count, "C").End(xlUp).Row Step 6
            If .Cells(i, "A").Value <> "" Then
                .Cells(i, "E").FormulaR1C1 = "=IFERROR('" & .Cells(i, "A").Value & "'!R1C[-1],"""")"
                .Cells(i, "E").Offset(1).Resize(4).FormulaR1C1 = "=R[-1]C"
           End If
        Next
    End With
  
    With Application
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub