Results 1 to 4 of 4

Thread: Naming multiple ranges using variables

  1. #1

    Naming multiple ranges using variables



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,

    I'm brand new to all of this and trying to create a budget tracking spreadsheet using some simple macros. Each month has its own sheet, with 10-15 different event breakdowns of funds and expenses. I have created a macro that calculates the total budget, expenditures, remaining budget, etc for each event. The macro works great when I run it on the first range of data, but when I run it on a second range, the total values calculated from this second range are inserted into the first range. I attempted to name the range using the Row Number to differentiate, but that hasn't worked yet. Each event has different row counts and rows are constantly shifting. Any advice would be much appreciated! Thanks!

    Code:
    Sub Total()
    '
    ' Total Macro
    '
    ' Keyboard Shortcut: Ctrl+T
    '
    'sum amounts in Other Funding column (F)
    
    Dim rowNumberValue As String
    
    rowNumberValue = ActiveCell.Row
    
    ActiveWorkbook.Names.Add Name:="RowNumber", RefersTo:=rowNumberValue
    
     Cells(rowNumberValue, 6).Select
     selection.Offset(-1, 0).Select
     
    Range(selection, selection(-1).End(xlup)(2)).Select
    ActiveWorkbook.Names.Add Name:="MyRange.RowNumber", RefersTo:=selection
    Cells(rowNumberValue, 6).Select
    ActiveCell.FormulaR1C1 = "=SUM(MyRange.RowNumber)"
    
    'sum Budget + Other Funding column (G)
    
    selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:RC[-2])"
    
    'sum amounts in Spent Column (H)
    
    columnNumbervalue = ActiveCell.Column
    
    selection.Offset(0, 1).Select
    
    rowNumberValue = ActiveCell.Row
    
     Cells(rowNumberValue, 8).Select
     selection.Offset(-1, 0).Select
     
    Range(selection, selection(-1).End(xlup)(2)).Select
    ActiveWorkbook.Names.Add Name:="MyRange1.RowNumber", RefersTo:=selection
    Cells(rowNumberValue, 8).Select
    ActiveCell.FormulaR1C1 = "=SUM(MyRange1.RowNumber)"
    
    'calculate remaining amount in budget colum (I)
    
    selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
    
    'sum amounts in In-Kind Donation Columm (J)
    
    columnNumbervalue = ActiveCell.Column
    
    selection.Offset(0, 1).Select
    
    rowNumberValue = ActiveCell.Row
    
     Cells(rowNumberValue, 10).Select
     selection.Offset(-1, 0).Select
     
    Range(selection, selection(-1).End(xlup)(2)).Select
    ActiveWorkbook.Names.Add Name:="MyRange2.RowNumber", RefersTo:=selection
    Cells(rowNumberValue, 10).Select
    ActiveCell.FormulaR1C1 = "=SUM(MyRange2.RowNumber)"
       
    End Sub
    Last edited by Bob Phillips; 2015-01-23 at 11:22 AM. Reason: Added code tags

  2. #2
    Post your workbook, it will help to see what is going on.

  3. #3
    Here is my working copy. Be gentle--I know it's a little rough, but as I said I'm teaching myself as I go. FYI--There will eventually be a button in column C next to the "Event Totals" cell that will start the 'Total' macro, so that is the intended start place. You'll notice that the Designated Donation and Spend Totals (F & I) for the End of Year Banquet and Monthly Lunch are populated with the totals from the Beer Tasting Tour, as this is the last event the macro was run on. I've taken out the other months and events to simplify it, but there will be 10-15 events per month, otherwise I would do the totals manually.
    Attached Files Attached Files

  4. #4
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    My suggestion would be to save yourself a lot of work and pain, just keep all of the data for the whole year in one single Table, and then use a Pivot Table to produce your Summary Report.
    Take a look at the attached workbook.

    I have taken your data and copied it to a sheet called Data and modified it.
    I created a table of the modified data and called it Data
    I added a extra column at the end called Month, with a formula of
    =TEXT([@Date],"mmm")
    This was just to make it easier to produce data by month within the Pivot Table without having to do grouping by date


    I created a small Table called Events with a Header called Events, containing a list of the different event types. (plus a few extra)
    I changed the heading in the first column of the table to Events, and then I was able to place in the data validation of the Events column
    =INDIRECT($B$2)
    and the drop down shows the list from that table - to which you can add more headings as necessary

    Then, the sheet called Report is just a simple Pivot Table in tabular format, to show the totals for each event.
    There is a Report Filter to be able to select Month, or any range of months.
    So, no VBA to maintain, no separate sheets for each month of data.

    Since you are happy with VBA, I have added one piece of event code to the Sheet Report, so that upon activating the sheet the data in the PT gets refreshed automatically.
    Code:
    Private Sub Worksheet_Activate()    
      Me.PivotTables(1).PivotCache.Refresh
    End Sub
    Your file has circular references on your hidden sheet Line Item Expenses.
    I have left if for you to copy and modify all of this data to the Table layout I have set up.

    I hope that this helps
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •