Replicated adding/deleting rows on seperate sheets

[hlep]

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:

Please Login or Register  to view this content.

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.

[StevenM]

Could you upload a mockup of your workbook?

[hlep]

Here's an example WB.

Example.xlsm

[StevenM]

Allow me to go over some technical details.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

is the same as:

Please Login or Register  to view this content.

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".

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

[hlep]

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

[StevenM]

Allow me to give one example.

Please Login or Register  to view this content.

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?

[hlep]

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.

[hlep]

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

[StevenM]

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:

Please Login or Register  to view this content.

Yes?

How would you add the above information to your worksheet?

Look at the following code:

Please Login or Register  to view this content.

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.

[hlep]

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

[hlep]

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

[StevenM]

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