Programmatically transfer data from master sheet to sub sheets with conditions

{ 45 Comments }

A user inputs raw material purchase data in a master sheet.  This data is entered by date and vendor.  One may want to view individual vendor sheets at a monthly level.  In the attached file, i have presented two solutions – a macro based one and a formula based one.

The data layout and instructions are very clearly mentioned in the this workbook.

You may also refer to the a similar article at the following link.

Leave a Comment Cancel reply

Your email address will not be published.

*

  • I love the solution….you are really an expert. Can you advise me any book for macros, which is easy to understand.

  • Do you have any instrusctions to help show how you set the macro up, this is exactly what i need.

    Thanks

  • Hi Ashish, Is it possible to use Vlookup to match 2 colums in 1 sheet with 2 column in other sheet to get desired result.
    Regards,
    Manoj Singh

  • Hi Ashish,
    Your solutions are really helpful. I need one solution to get sheet updated based on master sheet. If I delete or add something to master sheet, other sheet should not show error and update automatically.

  • Hello, I have an excel file where I need to move data from one sheet to another. I have tried to adjust a workbook I found on your site but have not had any luck with it. Basically here is my issue.
    In my Master sheet there are 7 columns:
    A = ID
    B = Type
    C = Code
    D = AMT (just a number with several decimal places)
    E = AMTType (Money being Given back or Deducted) should contain -90 in cell
    F = Check
    G = Cost Center
    I will populate the id number of an employee and upon typing it in the Cost Center will generate automatically… the rest of the data will be populated manually, but what I would like is to have a macro send the data to the sheets based on the cost center my issue is that the cost center will be like this (Saturn Company Inc) and the tab will only say Saturn or Flex Internal Inc and the tab will say FII… another thing is that it has to leave the data that is already in each tab … It should leave it as historical and only populate the new entries on each tab and delete the data from the master sheet once its is completely sorted out to each tab…

  • Hello Ashish thanks for the response… I get the following error when I update the Bridge sheet: “Unable to get the Match property of the WorksheetFunction class” and it highlights the following line: filter_criteria = Application.WorksheetFunction.Index(Worksheets(“Bridge”).Range(“A1:B” & bridge_rows), Application.WorksheetFunction.Match(Worksheets(n).Name, Worksheets(“Bridge”).Range(“B1:B” & bridge_rows), 0), 1) when I hover over the line it points to the “US_Semimonthly – STEL2> Stellar Salary Paygroup” Company Name in the Bridge sheet… Attached is the file…

    http://1drv.ms/1mofD0K

    Thank You

    • Hi,

      That error occurs because the EPG sheet has no entry in the Master and Bridge worksheet. For the VBA code to work fine. all tabs after the Bridge tab, should have an entry in the Master as well as Bridge worksheet. If you delete that sheet, then the code runs fine.

  • Hello Ashish thanks for the reply… Couldn’t get back to you before as I was on vacation… I would like to know if there is a way to just leave that sheet as I will have a formula that will get the Cost Center from there and will be updating the sheet every week… Another few changes have been made and wonder if you can assist… Basically once the macro transfers the data to each sheet instead of deleting the master sheet just empty the content from Columns A and D… also once the data is transferred to export the sheets Elementum, Flex International, Lynx, Saturn and Stellar as their own workbooks in CSV format? Here is the new file: http://1drv.ms/1nHeCAR

    • Hi,

      Make the following two changes in the code

      1. Change For n = 3 To ThisWorkbook.Worksheets.Count to For n = 3 To bridge_rows-1
      2. Change Worksheets(“Master”).Range(“A7:G” & rng.Rows.Count + 5).Clear to Worksheets(“Master”).Range(“A7:D” & rng.Rows.Count + 5).Clear

      Once this is working to your satisfaction, then we will solve the last part – transferring each worksheet to a CSV file.

      Hope this helps.

  • Hello Ashish to issues happen when I made those changes… data will not transfer from the Master sheet to the Saturn and Stellar sheets and it still clears the contents of Columns A:D when it should only clear A & D…

    • Hi,

      It is working fine for me. Data got transferred to the Saturn and Stellar sheets. For clearing data from only two columns – A and D, replace this line

      Worksheets(“Master”).Range(“A7:D” & rng.Rows.Count + 5).Clear

      with these two lines

      Worksheets(“Master”).Range(“A7:A” & rng.Rows.Count + 5).Clear
      Worksheets(“Master”).Range(“D7:D” & rng.Rows.Count + 5).Clear

      Hope this helps.

  • Ok got it all working properly… What could we add to export each of the sheets as separate CSV files?

    • Hi,

      You may refer to my solution in this workbook. This code will paste data in individual worksheets (what was already happening in the solution shared earlier) & create a CSV of each worksheet and stored those in the Data Files folder . In the VBA code, just change this portion

      C:\Users\mathu_000\Desktop\Data Files\

      to the actual path where you create the Data Files folder.

  • Hello Ashish,

    I have a few issues concerning the file. I have 5 different Pay groups in the file. If I have a total of 8 employee belonging to 2 different pay groups, all tabs will populate with the same data as in my Master sheet with all 8 employees when ONLY the 2 Pay Group Tabs should be populated
    New CSV files will be created for the 5 Pay Groups, when only 2 Files should be created.
    I would also like to add is for the data in the master to be transferred to the historical tab before the master sheet is emptied. So if I run the report this week and only have 4 employees in the master those four should be put in the Historical and then every time I run it for the new employees to be added to the historical tab below the previous employees so we can keep a historical of employees there.

  • Hello
    I have a excel file has a many sheets
    I want to using data sheet’s A column code If A column’s code match Sheet 1 cell a1 and Sheet 2 cell a1 code then copy.And data’s must copied last row. And one more condition. Same data’s not copied 2 or more times
    Here is the my example file
    http://share.gogo.mn/zUpTwAUC4q56091435994234/OFF-Open AR report – Apr’2015.xlsm
    I am searching for this many month’s
    Thank you

    • Hi,

      In cell A4 of sheet2, enter this array formula (Ctrl+Shift+Enter) and copy down/right

      =IFERROR(INDEX(data!$A$2:$F$15,SMALL(IF(data!$A$2:$A$15=Sheet2!$A$1,ROW(data!$A$2:$A$15)-ROW(data!$A$1)),ROW(1:1)),MATCH(A$3,data!$A$1:$G$1,0)),””)

      Format column C:D as numbers.

  • Dear Ashish
    Thank you for your formula. But i want to use your programmatically transfer data to respective sheets.
    When I run the macro, I get the error : Run time error ‘1004’ Unable to get the Match propertyof the WorksheetFunction class
    pos = Application.WorksheetFunction.Match(Application.WorksheetFunction.Text(Worksheets(“master data”).Range(“D” & n).Value, “mmmyyyy”), _
    Worksheets(Worksheets(“master data”).Range(“B” & n).Value).Range(“C2:AX2”), 0) + 2
    Can you fix this error
    Thank you

  • Hello. I am having trouble with the spreadsheet. When I click on a cell the formula shows: {=IF(ISERROR(INDEX(‘Main List’!$A$1:$F$45,SMALL(IF(‘Main List’!$F$1:$F$45=MID(CELL(“filename”,Q3),FIND(“]”,CELL(“filename”,Q3))+1,256),ROW(INDIRECT(“$1:$”&COUNTA(‘Main List’!$F$1:$F$45)))),ROW(3:3)),MATCH(C$1,’Main List’!$A$1:$F$1,0))),””,INDEX(‘Main List’!$A$1:$F$45,SMALL(IF(‘Main List’!$F$1:$F$45=MID(CELL(“filename”,Q3),FIND(“]”,CELL(“filename”,Q3))+1,256),ROW(INDIRECT(“$1:$”&COUNTA(‘Main List’!$F$1:$F$45)))),ROW(3:3)),MATCH(C$1,’Main List’!$A$1:$F$1,0)))}

    but when I click into the formula, the {} disappears from the end and if I hit enter the values changes. Is there a step I am missing to make the formula run correctly?

    • Hi,

      Try this

      1. Open a new tab in the workbook and rename that tab as Atlanta. The spelling should be the same as what appears in the Territory column of your All Master data worksheet
      2. Copy A4:RE4 from the All Master worksheet and paste in cell A1 of the Atlanta worksheet
      3. In cell A2 of the Atlanta worksheet, enter this array formula (Ctrl+Shift+Enter)

      =IFERROR(INDEX(‘All Data Master’!$A$5:$RE$364,SMALL(IF(‘All Data Master’!$E$5:$E$364=MID(CELL(“filename”,P1),FIND(“]”,CELL(“filename”,P1))+1,256),ROW(INDIRECT(“$1:$”&COUNTA(‘All Data Master’!$E$5:$E$364)))),ROW(1:1)),MATCH(Atlanta!A$1,’All Data Master’!$A$4:$RE$4,0)),””)

      4. Copy this formula and to the right (till column RE)
      5. Right click on the Atlanta worksheet > Move or Copy > Create a Copy > OK
      6. Rename the new sheet as Boston. The data on the Boston worksheet should now update automatically

      Hope this helps.

  • dear sir;

    I am from maintenance dept. there is 150 machine & history card of each machine need to maintain life long

    I want such code/Formula that split my common data sheet(History Card) into respective machine (i.e. work sheet create for each machine) & same auto update when enter data in History Card sheet

  • Sir, I could use your assistance. I have read several of your helps and replies. I have tried to modify a few, but am still unable to get the results I am looking for.

    I am trying to get data from the filled cells of a row (i.e. A5:S5) on the Master worksheet to copy to one of the three corresponding worksheets depending on which one of three selections is made from the dropdowns in the column C. Each matching row should copy to the next available row in the corresponding sheet. This should occur as new data is entered periodically on the master sheet. I don’t know if the 200 rows on the master sheet will be enough so I need the ability to add more rows as needed.

    I apologize. I know you have basically answered this question numerous times, but I just can’t seem to wrap my mind around it enough to apply it to my needs.

    https://drive.google.com/file/d/1AxMgEzDXQ9EFytBC5HbT__wssJZBIMVU/view?usp=sharing

      • Thank you for looking at this. I have added the two entries I currently have for the year. They currently on populate two of the tabs. If you need more please let me know and I’ll make up some data to add.

        • Hi,

          You may download my solution workbook from hereherehere. Right click on the PAs worksheet > Move or Copy > Create a Copy. Rename the new worksheets to “Type Form” entry appearing in column C of the Master worksheet.

          • Sir, I was hoping you could help me again. There appears to be an issue in the spreadsheet. As soon as you place a “type form” in cell C11 and > whichever type form was selected its corresponding sheet will go blank removing all entries. For example if I have all entries as CAPF27 as soon as I place CAPF27 in C11 the corresponding sheet will loose all data. If I have CAPF27 in all previous entries and place CAPF2 or something else in C11 the CAPF27 sheet data will remain until I select CAPF27 in a greater than C11 cell. This is for all type forms selected in any cell from C11 on in the C column.
            see spreadsheet at link below. Thank you again for all your previous help.

            https://drive.google.com/file/d/134ZMXcTDH1189uw6VjTwWlhrhgR-ozYs/view?usp=sharing

  • Thanks for the solution. In this solution when I transfer data , the master sheet data get deleted which I don’t want. I also want to have data in master sheet after transferring data. Any solution?