How to Automatically Update Report in Excel

Siena 0 Reputation points
2023-04-20T21:01:18.1366667+00:00

I have a multi-tab workbook that I use every month to create a monthly report for the Sales & Marketing Department. On one tab, I update columns of data, then the other sheets pull from this data. How can I automatically pull the next line of data into the other tabs without needing to update each cell every month? I was trying to figure out automate/macros but I just got things to change to one specific line rather than every time I run it, pulling from the next line in the sequence. I want to be able to update the data tab, push a button (or a couple of buttons) and have it pull the new month's data into each cell rather than have to manually update 40 cells at the beginning of each month (leaving room for my human errors)... Any and all help would be greatly appreciated!

Microsoft 365
Microsoft 365
Formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
3,964 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,541 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,583 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ESHAN SENGUPTA 0 Reputation points
    2023-04-21T19:34:01.4266667+00:00

    Here is an example of what your desired macro code might look like:

    Sub Try()
    'Get the next row or column of data
    Dim newData As Range
    Set newData = curRange.Offset(1, 0) 'or curRange.Offset(0, 1) for a column
    'Update the cells in the other tabs
    Sheets("Tab2").Range("A1").Value = newData.Value
    Sheets("Tab3").Range("B2:C4").Value = newData.Offset(0, 1).Resize(3, 2).Value
    'etc. for each tab and range of cells to update
    'Select the new data for the user
    newData.Select
    End Sub
    

    Go to the Developer tab, click the "Insert" button, and then pick "Button" from the Form Controls section to attach the macro to a button. Then, choose your macro from the list by selecting "Assign Macro" with the right-click menu on the button. Once the macro is configured and linked to a button, all you need to do is change the data in the first tab once a month, click the button, and the other tabs will be updated automatically.

    0 comments No comments

  2. Jorge Garza 0 Reputation points
    2023-04-24T21:52:54.3166667+00:00

    For a more usable answer it will be necessary more context of how your workbook is built but in general it is usable; if you have a "principal tab" with data that go to other sheets, in formula use general reference; for example =sum(A:A) or =sum(1:1) for sum all column A or all row 1 instead of =sum(A1:A3) and when you put data on A4 you need to change it to =sum(a1:a4). Another solutions are to name a specific range or table and in all your reports call to that range or table (only edit that named range not each formula) or work with pivot tables and refresh them all at once. As i previously say, it will be necessary more information about your workbook to define the best solution.

    0 comments No comments