Programmatically transfer data from master sheet to sub sheets with conditions

{43 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.

RSS 43 Comments…

 Share your views
  1. I love the solution....you are really an expert. Can you advise me any book for macros, which is easy to understand.

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

    Thanks

  3. 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

  4. 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.

  5. 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...

  6. Hello, Thanks for the prompt response here is the link:

    http://1drv.ms/1r8bzCa

  7. 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.

  8. 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.

  9. 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.

  10. 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.

  11. 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.

  12. 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.

  13. 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

  14. Hi,

    Since you posted the same question at this link, I have answered it there.

  15. 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?

  16. Hi Ashish,

    I need to split this all data master up by territory. I can't figure out how to do it. Can you take a look?

    https://drive.google.com/open?id=0By7HYcEnrN7BanI2UC00enk0N28

    • 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.

  17. 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

  18. 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

Leave a Comment

Your email address will not be published.

*

*