Copying rows to new sheet referencing master

[SPABS]

Hi there, first post so hopefully I am doing this correctly.

I am trying to create a process with another program (Revit) for creating formatted schedules (tables). I have plugins that update parameters in our program with an excel file and back.

Basically, I want to have a master sheet and copy each row to a new sheet. I have that and have it works, but I want those copied values to equal the master if it changes and that's the part I am struggling with. This is what I have now.

Sub RowToSheet()
Dim xRow As Long
    Dim i As Long
    With ActiveSheet
        xRow = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To xRow
            Worksheets.Add(, Sheets(Sheets.Count)).Name = "Row " & i
            .Rows(2).Copy Sheets("Row " & i).Range("A1")
            .Rows(i).Copy Sheets("Row " & i).Range("A2")
        Next i
    End With
End Sub

For reference, the master from the sample I am using now has 517 rows with 168 columns and when I make the 517 sheets, I want all values to equal the first if it updates so that I can have tables that play nice with the first program.

Hopefully that makes any sense.

 

[aaewalsh]

Try this

Sub RowToSheet()
    Dim xRow As Long
    Dim i As Long
    With ActiveSheet
        xRow = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To xRow
            Worksheets.Add(, Sheets(Sheets.Count)).Name = "Row " & i
            ' Fill the values in new sheet with formula to reference corresponding cell in the master sheet
            Sheets("Row " & i).Range("A1:A" & Columns.Count).Formula = "=INDIRECT(""Sheet1!""&ADDRESS(ROW(),COLUMN()))"
            .Rows(i).Copy Sheets("Row " & i).Range("A2")
        Next i
    End With
End Sub

 

[SPABS]

Try this

Sub RowToSheet()
    Dim xRow As Long
    Dim i As Long
    With ActiveSheet
        xRow = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To xRow
            Worksheets.Add(, Sheets(Sheets.Count)).Name = "Row " & i
            ' Fill the values in new sheet with formula to reference corresponding cell in the master sheet
            Sheets("Row " & i).Range("A1:A" & Columns.Count).Formula = "=INDIRECT(""Sheet1!""&ADDRESS(ROW(),COLUMN()))"
            .Rows(i).Copy Sheets("Row " & i).Range("A2")
        Next i
    End With
End Sub

Not quite unfortunately, it basically just gave me the result I had before minus some other information. I'll try to show off a bit more of what I am trying to do so others can see as well. So exporting from revit gives me this spreadsheet.

And what I have done made a sheet for each row

But if I change C5 n the master it doesn't change on its new sheet. Technically it is fine, but ideally I want to be able to be able to update it without redoing my formatted tables.

The code you linked did try to reference, but it referenced everything on the entire document each time and still left it as a pasted value rather than a referenced value/formula.

While what I am trying to do is more like this

I just don't want to do this 514 times haha.

Thank you for replying! Really apprciated.