Forum Discussion

Kristele's avatar
Kristele
Copper Contributor
Aug 03, 2023

Automate to a master sheet Excel

Hey all,

 

My manager wants me to create automation whereby data entered into individual sheets (or tabs) gets automatically exported to a separate master sheet (or account) without manual interference.

 

She asked if I could create some automation for this, but I have no clue (Excel novice), and if they can use tabs, they want some of the tabs to be private so they can only be viewed by the line manager, employee or have the master tab be private. If that's impossible, they want a separate master document that the answers automatically feed into. 

 

For context, it's an HR questionnaire that they want to be able to send to employees, have employees fill them out, and have their line manager go over the sheet. Senior management can then oversee all of these responses in one master document as they automatically feed into the master.

 

Please help! Leiman's terms are appreciated.

excel
Excel on Mac
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Aug 04, 2023

    Kristele 

    To achieve the automation you described in Excel on Mac, you can use VBA (Visual Basic for Applications) to write a macro that automatically transfers data from individual sheets (tabs) to a separate master sheet. Here is a step-by-step guide to help you get started:

    1. Open your Excel workbook and press "Option + F11" to open the Visual Basic for Applications (VBA) editor.
    2. In the VBA editor, click on "Insert" in the menu and then choose "Module." This will create a new module where you can write your macro.
    3. In the module window, paste the following VBA code:

    vba code:

    Sub TransferDataToMasterSheet()
    Dim wsMaster As Worksheet
    Dim wsData As Worksheet
    Dim lastRowMaster As Long
    Dim lastRowData As Long
    Dim rngCopy As Range
    
    ' Set the worksheet objects for the master and data sheets
    Set wsMaster = ThisWorkbook.Sheets("MasterSheet") ' Replace "MasterSheet" with the name of your master sheet
    Set wsData = ThisWorkbook.Sheets("DataSheet") ' Replace "DataSheet" with the name of your data sheet
    
    ' Find the last row with data in the master sheet and data sheet
    lastRowMaster = wsMaster.Cells(Rows.Count, "A").End(xlUp).Row
    lastRowData = wsData.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Set the range to copy from the data sheet
    Set rngCopy = wsData.Range("A2:D" & lastRowData) ' Replace "A2:D" with the range of your data
    
    ' Copy the data to the master sheet
    rngCopy.Copy wsMaster.Cells(lastRowMaster + 1, 1) ' Paste data below the last row in the master sheet
End Sub
    1. Customize the code to fit your specific sheet names and data range. Replace "MasterSheet" and "DataSheet" with the actual names of your master sheet and data sheet, respectively. Adjust the range "A2:D" to match the range of your data on the data sheet.
    2. Close the VBA editor and go back to your Excel workbook.
    3. Press "Option + Q" to close the VBA editor.
    4. Now, you can run the macro by pressing "Option + F8" to open the macro dialog box, selecting "TransferDataToMasterSheet," and clicking "Run."

    The macro will copy the data from the data sheet and paste it into the master sheet automatically. You can place the macro on a button or assign it to a keyboard shortcut for easy access.

    Regarding the privacy requirement, Excel does not have built-in access control to make certain tabs private for specific users. If you need to restrict access to certain tabs, you might consider using password protection to protect individual sheets. However, password protection can be easily removed by anyone with access to the password.

    Alternatively, you can consider using Excel Online or SharePoint to create a web-based form with access control features, where employees can enter data, and managers can review responses in a central location. This would offer more robust access control and collaboration capabilities compared to a local Excel file. The text and the steps was created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

Resources