VBA to replace part of formula in multiple cells across a row, using sheetname(s)

[jisearle]

I am using Windows 10, Microsoft 2016
I have an Excel file with multiple worksheets. Each worksheet has a LName as a name. (200 sheets)
I also have a 'Data' tab. On my 'Data' tab I need 200 rows of data each row using the name of a worksheet in several formulas across the row.

Example of formulas in the 'Data' sheet: '=MasterForm!$D$2' in A1; =MasterForm!$G$2 in B1, etc... (across several columns)
I would like to copy the formulas in row 1 down the spreadsheet (200 rows) and have each line use the name of the subsequent sheet.

Desired Result:
Row 1: =MasterForm!$D$2; =MasterForm!$G$2...
Row 2: =Akau!$D$2; =Akau!$G$2...
Row 3: =Andrew!$D$2; =Andrew!$G$2...

Sheetnames: MasterForm; Akau; Andrew; ...

I've been doing this row by row with the Replace function, but I'm sure there is a better way to do this with VBA.

Thank you in advance for your help.

 

[Dim Me as xlNoob]

Hi,

How many cells do you want to reference from each sheet? The following code will work but you will have to write into the code which cells you want to copy into the Data sheet, and also will only work if you want to copy the same cell references from every sheet if you get what I mean.

Sub writeFormulae()
    Dim sh As Worksheet
    
    iRow = 2 ' To start after a header
    
    For Each sh In ActiveWorkbook.Sheets
        If sh.Name = "Data" Then GoTo nextsh
        Worksheets("Data").Cells(iRow, 1).Value = sh.Name
        Worksheets("Data").Cells(iRow, 2).Formula = "=" & sh.Name & "!$D$2"
        Worksheets("Data").Cells(iRow, 3).Formula = "=" & sh.Name & "!$G$2"
        iRow = iRow + 1
nextsh:
    Next sh
End Sub

 

[jisearle]

I get a run-time error (Run-time error '9': Subscript out of range) on the following line of code:
"Worksheets("Data").Cells(iRow, 1).Value = sh.Name"

I have 80 fields to reference in each row. (A bit crazy, I know)
Each sheet has the same cell references.

Since the 80 fields are all in row 1 do I still use 'iRow, 1', 'iRow, 2', etc... or should I be referencing columns instead. I am not a programmer, by any means so I'm just trying to logic through the code.

Thank you for your help.

 

[Dim Me as xlNoob]

Right you might have to change Worksheets("Data") to match the name of your 'Data' tab exactly? I just thought you had a sheet called "Data".

80 references are a lot to write out in the script indeed. This can probably still be done, is there any sort of pattern to what cells you want copied? Like always 3 columns apart or something (eg. D, G, J, M, P... etc)? Worst case is you put all the columns you need into an array and then loop through the array to write the formulae. I can do up an example.

 

[Dim Me as xlNoob]

This is probably the best way I can think of doing this. You'll have to list all the columns you want to reference in the columns() array, but then the code will sort everything else out for you.

    Dim sh As Worksheet, columns() As Variant
    
    iRow = 2 ' To start after a header
    columns = Array("D", "G", "H", "J", "K", "Z", "AD", "AR", "AT", ... , "ZZ") ' You need to put all columns used in here
    
    For Each sh In ActiveWorkbook.Sheets
        If sh.Name = "Data" Then GoTo nextsh
        Worksheets("Data").Cells(iRow, 1).Value = sh.Name
        For i = 1 To UBound(columns) + 1
            Worksheets("Data").Cells(iRow, i + 1).Formula = "=" & sh.Name & "!$" & columns(i - 1) & "$2"
        Next i
        iRow = iRow + 1
nextsh:
    Next sh

Did you manage to fix the runtime error by adjusting the data sheet name?

 

[jisearle]

Hi Dim Me,
Sorry I didn't get back to you. I was distracted from this project and taken down other rabbit holes. I was able to fix the runtime error by adjusting the data sheet name. I spoke to the end user who was working with this file and we managed to simplify the process quite a bit. Thank you so much for your time answering my questions.

 

[Dim Me as xlNoob]

No worries thanks for the feedback. Glad you hear you have a solution anyway.