Populate cells using dynamic named range, VBA

[Paradigm_Shift]

I have a sheet containing 2 lists of categories for income and expenses. These lists are named inc and exp, respectively, and are contained in a sheet called "Lists." They are dynamic ranges, meaning every time a value is added, the named range adjusts itself to include the new value(s). [=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)]

I have 2 tables summarizing 12 months of data. The tables use sumif to find all occurrences of each category and sum them. There's 1 table for income, 1 for expenses. Each is a 2 column table, with all the categories for income in column A and their total for 12 months in column B. Same for Expenses in D & E.

To populate the categories in column A & D, I am currently using the simple =Lists!A1 =Lists!A2 (inc range), =Lists!B1 =Lists!B2 (exp range) and so on... the problem is when a value is added to either list, while the named range adjusts, I have to manually drag the formulas in Column A & B down 1 more cell to include the new value.

How can I use VBA to look for the inc and exp ranges (which will change in size), then populate each table with the most recent categories?

Thanks in advance for responses.

 

[Jerry Sullivan]

You could use Worksheet_Change event code in your Lists Sheet
Anytime a cell in Column A or B of Lists changes, the code would clear the cells in Columns A & D of your summary tables, insert rows with formulas or delete rows to match the new items counts, then copy the current values of Ranges inc and exp into the tables.

Do you want some help with the code, or just looking for this outline of an approach?

 

[Paradigm_Shift]

I'm not sure how I could use the Worksheet_Change to adjust the values in my tables...could you give a generic example??

 

[Jerry Sullivan]

Sure. First, could you clarify whether your Tables are true Tables (formerly "ListObjects" before xl2007) or just Worksheet Ranges?

 

[Paradigm_Shift]

Just basic worksheet ranges. If you can explain the code to get the categories to change in the table dynamically, I can get the formulas in the adjacent column to "drag down" with VBA.

 

[Jerry Sullivan]

Just basic worksheet ranges. If you can explain the code to get the categories to change in the table dynamically, I can get the formulas in the adjacent column to "drag down" with VBA.

Here is a quick example of how to copy the entire columns from your Lists to your Summary. You'll need to modify this to account for any differences in Headers or Row numbers between the sheets, but it should give you an idea of how Worksheet_Change works.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    On Error GoTo CleanUp
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Range("A:A").Copy
    Sheets("Summary").Range("A1").PasteSpecial (xlPasteValues)
    Range("B:B").Copy
    Sheets("Summary").Range("D1").PasteSpecial (xlPasteValues)
CleanUp:
    Application.EnableEvents = True
End Sub

 

[Jerry Sullivan]

Just in case you haven't used Worksheet Event code before, below are instructions.
You set them up just like other macros except they need to be copied into the code space for that specific worksheet instead of a standard VBA code module.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code).

1. Copy the code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

 

[Paradigm_Shift]

Well, here is the code after I edited for my Sheet names and ranges:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    On Error GoTo CleanUp
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Range("A:A").Copy
    Sheets("CategoryReport").Range("B4").PasteSpecial (xlPasteValues)
    Range("B:B").Copy
    Sheets("CategoryReport").Range("E4").PasteSpecial (xlPasteValues)
CleanUp:
    Application.EnableEvents = True
End Sub

I tried placing it in the "Lists" sheet since the Range("A:A").Copy line seems to pull the list (am I right?) and without a sheet reference, I figured the code would need to be in the "Lists" sheet.

However, nothing happened when I saved the document. The columns in "CategoryReport" were not populated with the 2 lists...

Where does this code need to be placed??

 

[Jerry Sullivan]

You copied the code to the correct place.
It's not working because you are copying the entire column, then trying to paste it into cells on the 4th row (so there is not enough room on the sheet).

That's what I meant by "You'll need to modify this to account for any differences in Headers or Row numbers between the sheets"

If you go back to my first outline, the steps you want to create are:
1. Clear all cells below Columns A4 & D4 of your summary
2. Insert rows in Cols B and E with formulas or delete rows to match the new items counts.
3. Copy the current values of Ranges inc and exp into the tables at A4, D4.

 

[Jerry Sullivan]

One other concept that is important to understand...

However, nothing happened when I saved the document. The columns in "CategoryReport" were not populated with the 2 lists...

The Worksheet_Change event code is triggered immediately after you change 1 or more Cells in your Lists Sheet. It doesn't wait until the next time you save the document.