Change Sheet Name when copying cell

[BeerBeer101]

Hi again,
copying from cell to cell (down) and changing the filename:

B3 - links to a sheet called #1.xlsx
B4 - links to a sheet called #2.xlsx
and on down the line it goes. is there a copy function that will ad 1 number to the sheet name (i have about 1200 sheets total) as opposed to having to manually change the 1, 2, 3, 4 etc etc...
appreciate the help!

 

[Candyman8019]

You could try doing this with a macro something like this:

Sub fill()
For I = 3 To 1200
ActiveSheet.Range("B" & I).Formula = "='b:\completed\[#" & I - 2 & ".xlsx]Input Sheet'!$D$1"
Next I
End Sub

 

[Ottsel]

If the number of sheets were to change in the future this might be an ideal setup as well:

Option Explicit

Sub BeerBeer101()

    Dim Numsheet As String
    Dim i As Integer
    
    'B:\Completed\
    Application.DisplayAlerts = False
    Numsheet = InputBox("How many sheets do you need to reference?")
        If Numsheet = "" Then
                Application.DisplayAlerts = True
                Exit Sub
            Else
            For i = 3 To Numsheet + 2
                ActiveSheet.Range("B" & i).Formula = "='B:\Completed\[#" & i - 2 & ".xlsx]Input Sheet'!$D$1"
            Next i
        End If
    Application.DisplayAlerts = False
    
End Sub

 

[Ottsel]

Application.DisplayAlerts = False

Oops, this one at the bottom should be "true". Didn't proof read it enough.