Ask sheet to take values from the copy of a sheet

[2_nisia]

Hello,

I have an excel workbook that has some hidden sheets. I have linked some cells of sheet B to some cells of sheet A.
So when someone fills in some specific cells of sheet A, then Sheet B will be updated automatically.

The problem is that this workbook is shared with other people. And to make sure that the formulas will not be ruined, the users get a copy of sheet A and a copy of sheet B to fill in. The sheets are also renamed, so the names are the following:
Sheet A is originally named Matrix
Sheet B is originally named Taks_NER

The user is asked to register a name, so he picks the name "House". The Sheet A will get copied and that copy will be named House, and sheet B will also be copied and the copy will be named Taks_House.


So my question is: Is there a way to tell sheet Taks_House, to copy the cells not from sheet A (Matrix) but from the copy of sheet A (House)?
The problem will coding might be, that I dont know what name the user will pick.


Thank you in advance!

 

[Alex Blakenburg]

I am not clear on how many users will be creating sheets in the same workbook.
Is it more than 1 ?
What are you going to do with those sheets ?

You can check for sheet name starting with Taks_ which doesn't end in NER or Taks_ which are visible.
This would pick up Taks_House. From that you can get House as the new name for Matrix.

There are other options but we would need an answer to the above 3 question.

 

[Alex Blakenburg]

Just to get the discussion started, this has 2 options one relying on excluding NER (option 1 commented out) and one relying on excluding Hidden sheets.

Sub FindSheet()

    Dim wsCurr As Worksheet
    Dim wb As Workbook
    Dim wsNewMatrix As Worksheet, NewMatrixName As String
    
    Set wb = ActiveWorkbook
    
    For Each wsCurr In wb.Worksheets
        Debug.Print wsCurr.Name
        ' Option 1
        ' If Left(wsCurr.Name, 4) = "Taks" And Right(wsCurr.Name, 3) <> "NER" Then
        ' OR
        ' Option 2
        If Left(wsCurr.Name, 4) = "Taks" And wsCurr.Visible Then
            NewMatrixName = Mid(wsCurr.Name, InStr(wsCurr.Name, "_") + 1)
            'Debug.Print NewMatrixName
            MsgBox "New Matrix Sheet Name --> " & NewMatrixName
        End If
    
    Next wsCurr

End Sub

 

[2_nisia]

I am not clear on how many users will be creating sheets in the same workbook.
Is it more than 1 ?
What are you going to do with those sheets ?

You can check for sheet name starting with Taks_ which doesn't end in NER or Taks_ which are visible.
This would pick up Taks_House. From that you can get House as the new name for Matrix.

There are other options but we would need an answer to the above 3 question.

Each user will download my excel file in the personal computer, so its gonna be 1 user at a time at 1 excel file.

In that excel file, they have a choice to make: what is their building type.
It can be a house, an office, a shop. So based on this choice, my VBA code goes and finds the relevant matrix for house, office or shop, it unhides it -> makes a copy and names that copy-> hides the original matrix again

Then, on the (copy and renamed) matrix, they go and fill in some cells, e.g. size of building, number of occupants etc.

So I want the taks_House to be linked to that copied tab of the matrix, but since I have gave them the option to rename it, it is difficult for me to link the two tabs now cause i dont know what kind on name they will choose. I also considered not to give them the option or rename it, but it will still create a copy of taks_NER and automatically rename it as taks_NER(1)

 

[Alex Blakenburg]

Sorry I forgot to come back to this, will have a look tomorrow.