Hi,
I am a complete beginner when it comes to Excel VBA and am really struggling to find a solution to my problem. Here is in essence, what my problem consists of:
I have a .xlsm file that is made of several spreadsheets. Let's say I have a main spreadsheet titled "Main Sheet", and a secondary sheet titled "Other Sheet One" that will contain a subset of the rows of "Main Sheet".
Let's say Main Sheet consists of a table that looks like:
-------------------------------------
| Animal | Color_ | Number | Food__ |
------------------------------------
| Dog___ | Green_ | 1_____ | Egg___ |
------------------------------------
| Cat___ | Pink__ | 10____ | Cheese |
------------------------------------
| Mouse_ | Red___ | 11____ | Bread_ |
------------------------------------
| Bird__ | Green_ | 100___ | Milk__ |
------------------------------------
| Fish__ | Blue__ | 101___ | Rice__ |
-------------------------------------
And a formula just beneath it that takes the cumulative sum of column 'C' (number) in cell 'C7' (let's say that the table fills rows 1 through 6).
You'll have to excuse the underscores. I couldn't figure out how to align them with whitespace.
Anyways, and let's say that "Other Sheet One" is composed of just the header of "Main Sheet" (that is, the row [Animal, Color, Number, Food]) located at row 1, and the same cumulative sum formula immediately beneath it in column 'C' (thus it would be in 'C2').
I wish to be able to, either whenever I update "Main Sheet" or maybe have a button existing in either "Main Sheet" or "Other Sheet One" which when I click (whichever would be easier/better), would clear currently existing values of "Other Sheet One", then update "Other Sheet One" to fill up with rows (starting from just below the header and above the cumulative sum formula in "Other Sheet One") from "Main Sheet" that have a color equal to "Red", "Green", or "Blue".
Would this be possible to do? This problem is very difficult for me, because I am unable to figure out how to extract row data based on multiple criteria to another spreadsheet, while shifting existing formulas down as those extracted rows are pulled in. Also, because the solution would need to take care of updating "Other Sheet One" in the scenario of adding new or deleting old rows from "Main Sheet" (by what I assume would be accomplished by the deleting of all values of "Other Sheet One", and writing all the new rows in from "Main Sheet").
Help would be greatly appreciated. Thanks in advance.
Bookmarks