Replicated adding/deleting rows on seperate sheets

I'm trying to tweak some code I found that will add/delete rows on a seperate sheet when I add/delete rows in my summary sheet.

Here's what I'm trying to use:

 Sub AddDelete()
Dim rX As Range, rY As Range, i&
With Sheets("Financial Statements Summary")
    Set rX = .Range("Summary_Format", .Cells(Rows.Count, 4).End(xlUp))
End With
With Sheets("Company1")
    Set rY = .Range("Company1_Format", .Cells(264, Columns.Count).End(xlToLeft))
End With

For i = 1 To rX.Count
    If rX(i) <> rY(i) Then
        If rX(i) <> rY(i + 1) Then
            rY(i).EntireRow.Insert
            rY(i).Value = rX(i).Value
        Else
            rY(i).EntireRow.Delete
        End If
    End If
Next
End Sub

Both Summary_Format and Company1_Format are the same # of rows and columns. They are financial statements. I'm pretty new to code and any help is appreciated.

Could you upload a mockup of your workbook?

Here's an example WB.

Example.xlsm

Allow me to go over some technical details.

Dim rX As Range, rY As Range, i As Long

In my opinion, it is important to make your code as readable as possible.

Since rX is a range of the "Financial Statements Summary" sheet, perhaps you could call it rFSS or some such.
And since rY is a range of the ""Company 1" sheet, perhaps you could call it rC1 or some such.

With Sheets("Company1")

"Company1" is not the same as "Company 1" (which was the name of your sheet you uploaded).

With Sheets("Financial Statements Summary")
    Set rX = .Range("Summary_Format", .Cells(Rows.Count, 4).End(xlUp))
End With

It is easy to overlook (I've done it often), but you need a dot before "Rows.Count"
With the dot, "Rows" belongs with the "With" statement, without the dot, "Rows" belongs with the Active Worksheet.

In addition:

.Cells(.Rows.Count, 4).End(xlUp)

is the same as:

.Cells(.Rows.Count, "D").End(xlUp)

Because D is the 4th column. (In my opinion, "D" is more readable than "4".)

Now because there is nothing in column D, the above statement is the same as "D1".

Set rX = .Range("Summary_Format", .Cells(Rows.Count, 4).End(xlUp))

"Summary_Format" is B4:B12, and .Cells(Rows.Count, 4).End(xlUp) is "D1", the result is rX = B4:D12.

With Sheets("Company 1")
    Set rY = .Range("Company1_Format", .Cells(264, Columns.Count).End(xlToLeft))
End With

You need a dot before "Columns", furthermore since there is no data in row 264,

.Cells(264, Columns.Count).End(xlToLeft) = A264.

Since "Company1_Format" = B4:B12, that with A264, makes rY = A4:B264.

As for your worksheet, it doesn't make sense that you have four identical worksheets. Company 1's worksheet should be different from Company 2's worksheet, and both should be different from Company 3's worksheet. And if your Summary worksheet only has a summary, then it won't have all the details from the other worksheets. On the other hand, a Master worksheet might contain all the data from the company worksheets. So first you need to determined the relationship of your worksheets. Once you know the relationship, you can talk about adding and deleting items. But without knowing the structure and relationship of your worksheets, it is hard for me to give any general answers on those matters.

Thank you for your help StevenM. I agree, making the code easy to read is ideal and a good habit I need to get into. Thanks for going over some basic technical details, those always seem to mess me up one way or another. I attached a new WB with some detail and explanation as to why I'm setting up the WB this way. It's mainly b/c there is a single holding company for all the individuals and I want to see how the money is flowing between them all.


Example.2.xlsm

Allow me to give one example.

Sub AddThenDeleteRow()
    Dim sFind As String, rgCell As Range, newRow As Long
    
    ' Let's say you wanted to add a row above the Item "Liabilities & Equity" in column B.
    sFind = "Liabilities & Equity"
    With Worksheets("Financial Statements Summary")
        Set rgCell = .Range("B:B").Find( _
                What:=sFind, _
                LookIn:=xlValues, _
                Lookat:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=True)
        ' rgcell will be the cell where sFind is found
        ' If it was not found, rgCell Is Nothing
        ' If it was found, the Not rgCell Is Nothing is true
        If Not rgCell Is Nothing Then
            ' This inserts a row above the rgCell.row
            rgCell.EntireRow.Insert
            newRow = rgCell.Row - 1
            MsgBox "New row added is row: " & newRow
        End If
        ' Now delete the new row
        .Rows(newRow).Delete
    End With
End Sub

What was the point of this exercise? I used information inside the worksheet to orient me. I added a row above "Liabilities & Equity" in column B. So I knew two things about the worksheet, I knew that a cell would have the phrase: "Liabilities & Equity" and I knew it would be found in column B.

Another possibility would be to add a row below the last "From Comp." in column B. So I would search from the bottom up, look for the first cell to have "From Comp." and then insert a row below it.

Obviously you want to do more than just add a row, you want to add new information to that new row. And you want to add the same (or similar) information to a Company worksheet also.

If it is an task you do often, you might consider writing a function which does this.
Before we can write such a function, we need to know a number of things.
What information is being added?
Where on the FSS worksheet does it need to be added?
Which company, and where on the Company worksheet does it need to be added?

The information that is being added on the FSS would be new information that is being added on the individual sheets. An example would be if I needed to add a new COGS item to Company 2, I would add that new line in the FSS Income Statement, and that new line would be added on all company sheets.

The individual company sheets are grabbing information from a larger, 15 year, financial model, ie there are no actual functions or calculations on the indivudial company sheets other than sums, those numbers are in another sheet. But consistency is something I'm trying to keep, so the added row would initially be added in the FSS, then that would be replicated on all company sheets.

The financial statements are really around 300 lines long, and I was hoping there would be a way to insert a line somewhere within the statements, and have a macro that would be able to find where the new line was inserted, and then add a row in the individual company sheets.

I'm now able to insert a partial row as I need to. It would be great if it is possible to make it more automated, ie not have to type in VBA code where the row needs to be inserted. I don't know if it's even possible to have it somehow search as to where a new row was inserted. Nonetheless it's making progress.



Example.3.1.xlsm

I think I understand what you are trying to do, but it might be easier for me to show me if I had a new workbook.

Your last example has two sheets.
You might consider uploading a new workbook.
This time, Sheets 1 & 2 are before sheets (before the changes are made)
And Sheets 3 & 4 are after sheets (what 1 & 2 would look like after the changes are made).
You can then hilight the changes in sheets 3 & 4.
Then I will write a macro which will make sheets 1 & 2 look like 3 & 4 (but without the hilighting).
Try to make your example look as realistic as you can.
For example, you don't want to add: "added row"
Rather, you want to add something like:

	    	YTD	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec

From Comp. x	7	1	1	1	1	1	1	1	1	1	1	1	1

Yes?

How would you add the above information to your worksheet?

Look at the following code:

Sub CreatevArray()
    Dim vArray As Variant
    ReDim vArray(1 To 1, 1 To 15)
    vArray(1, 1) = "From Comp. x"
    vArray(1, 2) = 1
    vArray(1, 3) = 9    ' = YTD
    vArray(1, 4) = 1    ' = Jan
    vArray(1, 5) = 2    ' = Feb
    vArray(1, 6) = 3    ' = Mar
    vArray(1, 7) = 4    ' = Apr
    vArray(1, 8) = 5    ' = May
    vArray(1, 9) = 6    ' = Jun
    vArray(1, 10) = 7   ' = Jul
    vArray(1, 11) = 8   ' = Aug
    vArray(1, 12) = 9   ' = Sep
    vArray(1, 13) = 10  ' = Oct
    vArray(1, 14) = 11  ' = Nov
    vArray(1, 15) = 12  ' = Dec
End Sub

It doesn't do anything, but it sets up information to be added to a worksheet.
Columns B:P are 15 columns.

What you really need is code that will add vArray to your workbook, yes?

After a couple examples, you will figure out how to write this code yourself.

Steven, thanks again for your help. I've attached a new WB that is hopefully easier to understand. The #'s in Example 3.1 were just for me to see if I was inserting the correct row, no real value in them. Sorry for not making that clear.

Anyways I feel I'm making progress. Just got a copy of Walkenbach's Power Programming with VBA, and hopefully it'll expedite the VBA learning process.

I really couldn't tell you if vArray is what I'm looking for. No experience with it, but I'm doing some reading and research on it.


NewExample.xlsm

I've got it to a point where it's usable and is helpful. Still wondering if there is a way to avoid having to find the row that needs to be inserted by manually adding text.

Example.3.2.xlsm

Each worksheet you upload is so different, it is hard to make any suggestions.