VBA: reference a cell value in a different worksheet with alternative worksheet

[Soleil2438]

Hello to you all,

I'm new to using VBA in excel (the firt time I use it) and i try to create a summary sheet (sheet.name = "BD") with informations that are present on differents sheets. But my problem is that I don't know in advence the name or the number of sheet the excel will have. (they are all in the same workbook)

In the first colum I have the liste of all the sheet name. So I can use them as reference since they are random. = It's the «Sub listenom( )» and the code work for the moment
On the colum B, I need to report the information of the cell "B1" of each sheet = it's the «Sub feuille_de_route ( )» code and that don't work for the moment
On the colum C, the cell "C2" of each sheet
Colum D = "C4"of each sheet
Colum E = "C3"of each sheet
Colum F = "G2" of each sheet
Colum G = "C5" of each sheet

So far, that what I was able to do:

Sub Listenom()
Dim i As Integer   

    Range("A2").Select

    For i = 7 To Sheets.Count  
        ActiveCell.Value = Sheets(i).Name 
        ActiveCell.Offset(1, 0).Select 
    Next i
End Sub

Sub Feuille_de_route()
Dim B As Integer
    
 For B = 7 To Sheets.Count
    ActiveCell.Formula2R1C1 = "='" & Sheets(Selection.Offset(0, -1).Select).Name & "!R[-2]C"
    ActiveCell.Offset(1, 0).Select 
 Next B

End Sub

Are you able to help me please ? what am I doing wrong ?
Thank a lot !!

 

[myall_blues]

Try this. It's always easier to work with real cell addresses than RC references.

Sub Listenom()
    Dim i As Integer
    For i = 7 To Sheets.Count
        Cells(i - 5, 1).Value = Sheets(i).Name
    Next i
End Sub
Sub Feuille_de_route()
    Dim i As Integer
    For i = 7 To Sheets.Count
        Cells(i - 5, 2).Formula = "='" & Cells(i - 5, 1).Value & "'!B$1"
        Cells(i - 5, 3).Formula = "='" & Cells(i - 5, 1).Value & "'!C$2"
        Cells(i - 5, 4).Formula = "='" & Cells(i - 5, 1).Value & "'!C$4"
        Cells(i - 5, 5).Formula = "='" & Cells(i - 5, 1).Value & "'!C$3"
        Cells(i - 5, 6).Formula = "='" & Cells(i - 5, 1).Value & "'!G$2"
        Cells(i - 5, 7).Formula = "='" & Cells(i - 5, 1).Value & "'!C$5"
    Next i
End Sub

 

[Peter_SSs]

A slightly different approach for the second procedure involving less individual steps.

Sub Feuille_de_route()
  Dim i As Long, rws As Long
  
  rws = Sheets.Count - 6
  With Range("B2:G2").Resize(rws)
    .Formula = Array("#'!B$1", "#'!C$2", "#'!C$4", "#'!C$3", "#'!G$2", "#'!C$5")
    For i = 1 To rws
      .Rows(i).Replace What:="#", Replacement:="='" & .Cells(i, 0).Value, LookAt:=xlPart
    Next i
  End With
End Sub

 

[Alex Blakenburg]

And on the totally opposite end of the spectrum ie more steps but as a first time VBA user that may find it easier to follow.
It tests if the sheet name exists in case you make any manual changes.

Sub Feuille_de_route()
    Dim shtSummary As Worksheet
    Dim rngNames As Range, rCell As Range
    Dim lastRow As Long, lastRowB As Long, rowSum As Long
    Dim sheetName As String
  
    Set shtSummary = Worksheets("BD")
    With shtSummary
        lastRow = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngNames = .Range("A2:A" & lastRow)
        lastRowB = .Range("B" & Rows.Count).End(xlUp).Row
        If lastRowB > 1 Then
            .Range("B2:G" & lastRowB).ClearContents
        End If
    End With
  
    For Each rCell In rngNames
        sheetName = rCell.Value
        If Evaluate("isref('" & sheetName & "'!A1)") Then
            rowSum = rCell.Row
            shtSummary.Cells(rowSum, "B").Formula = "='" & sheetName & "'!" & "B1"
            shtSummary.Cells(rowSum, "C").Formula = "='" & sheetName & "'!" & "C2"
            shtSummary.Cells(rowSum, "D").Formula = "='" & sheetName & "'!" & "C4"
            shtSummary.Cells(rowSum, "E").Formula = "='" & sheetName & "'!" & "C3"
            shtSummary.Cells(rowSum, "F").Formula = "='" & sheetName & "'!" & "G2"
            shtSummary.Cells(rowSum, "G").Formula = "='" & sheetName & "'!" & "C5"
        End If
    Next rCell

End Sub

 

[Soleil2438]

Thanks a lot for the yours answers !! That exactly what I wanted to do

Since then I tried to combine the 2 codes together and that work for the moment . I tried to add an Hypelinks so I can easily go on a worksheet, but the line dones't work.
I think it around the «address» ou «SubAdress», because I see the "underline" to say there is a Hyperlinks on each sheetname, but when I clic ont it nothing is happening.

Sub Actualiser()
  Dim i As Long, rws As Long
 
    Range("A2").Select
    For i = 7 To Sheets.Count
        ActiveCell.Value = Sheets(i).Name
        ActiveCell.Offset(1, 0).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="", ScreenTip:="", TextToDisplay:=Sheets(i).Name
    Next i
 
  rws = Sheets.Count - 6
  With Range("B2:G2").Resize(rws)
    .Formula = Array("#'!B$1", "#'!C$2", "#'!C$4", "#'!C$3", "#'!G$2", "#'!C$5")
    For i = 1 To rws
      .Rows(i).Replace What:="#", Replacement:="='" & .Cells(i, 0).Value, LookAt:=xlPart
    Next i
  End With
End Sub

where is my mistake ? Thank

 

[Peter_SSs]

Try this

Sub Actualiser_v2()
  Dim i As Long
  
  Application.ScreenUpdating = False
  For i = 1 To Sheets.Count - 6
    With Cells(1 + i, 1)
      .Value = Sheets(6 + i).Name
      ActiveSheet.Hyperlinks.Add Anchor:=Cells(i + 1, 1), Address:="", SubAddress:="'" & .Value & "'!A1", ScreenTip:="", TextToDisplay:=.Value
      .Offset(, 1).Resize(, 6).Formula = Array("#'!B$1", "#'!C$2", "#'!C$4", "#'!C$3", "#'!G$2", "#'!C$5")
      .EntireRow.Replace What:="#", Replacement:="='" & .Value, LookAt:=xlPart
    End With
  Next i
  Application.ScreenUpdating = True
End Sub

 

[Soleil2438]

Try this

Sub Actualiser_v2()
  Dim i As Long
 
  Application.ScreenUpdating = False
  For i = 1 To Sheets.Count - 6
    With Cells(1 + i, 1)
      .Value = Sheets(6 + i).Name
      ActiveSheet.Hyperlinks.Add Anchor:=Cells(i + 1, 1), Address:="", SubAddress:="'" & .Value & "'!A1", ScreenTip:="", TextToDisplay:=.Value
      .Offset(, 1).Resize(, 6).Formula = Array("#'!B$1", "#'!C$2", "#'!C$4", "#'!C$3", "#'!G$2", "#'!C$5")
      .EntireRow.Replace What:="#", Replacement:="='" & .Value, LookAt:=xlPart
    End With
  Next i
  Application.ScreenUpdating = True
End Sub

that work !! Thank

 

[Peter_SSs]

You are welcome. Thanks for the confirmation.