+ Reply to Thread
Results 1 to 24 of 24

Create a new files from different sheets

  1. #1
    Registered User
    Join Date
    02-23-2021
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2016
    Posts
    30

    Question Create a new files from different sheets

    I have one main excel file that I update and have around 1000 rows.

    From this sheet I create one pivot table for each sales person, so I have 11 sheets as it is 11 different people. (they will have their own sheet for their orders)

    Now my question is. Can I easy create a new excel file for each sheet in the main file, so I can send it to them with email, when I have updated the main file.

    So I would like to take each sheet and create a new excel file for each, so that would be 11 excel files.

    I would like to have it as a macro, so it would be an easy take every day when I update the main file.

    Dont know if it is possible but I take all advice how to make this work.

  2. #2
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Create a new files from different sheets

    Please Login or Register  to view this content.
    There are a few things you need to make sure before using the above VBA code:

    Create a folder where you want to get all the resulting files.
    Save the main Excel file (which has all the worksheets that you want as separate files) in this folder.
    Once you have this done, then you can put the above VBA code in the file and test it..

    All this was found here: new files from sheets
    If you find the suggestion or solution helpful, please consider adding reputation to the post.

  3. #3
    Registered User
    Join Date
    02-23-2021
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2016
    Posts
    30

    Re: Create a new files from different sheets

    Thank you so much cubangt.

    Do you know how I can make the new files "read only" files?
    I am going to send the files out to each sales person, but I dont want them to be able to change the pivot table.

  4. #4
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Create a new files from different sheets

    ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly us this before your Application.ActiveWorkbook.Close False

    Like this:

    Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
    Application.ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly
    Application.ActiveWorkbook.Close False

  5. #5
    Registered User
    Join Date
    02-23-2021
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2016
    Posts
    30

    Re: Create a new files from different sheets

    I added the xlReadOnly to the but it did not want to work.

  6. #6
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Create a new files from different sheets

    Let me try and see what happens for me.. do you have a sample file you can upload?

  7. #7
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Create a new files from different sheets

    So if you change it to this:

    Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx", ReadOnlyRecommended:=True
    Application.ActiveWorkbook.Close False

    It will prompt the user that the file is read only, but still allows them to open it without.
    The prompt is :

    The author would like you to open this as read-only unless you need to make changes. Open as Read-Only?

    If thats what you are wanting use the above.

    If you want to set the actual file properties to read-only

    then add this instead:

    For Each ws In ThisWorkbook.Sheets
    ws.Copy
    Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx", ReadOnlyRecommended:=True
    Application.ActiveWorkbook.Close False

    SetAttr FPath & "\" & ws.Name & ".xlsx", vbReadOnly
    Next

  8. #8
    Registered User
    Join Date
    02-23-2021
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2016
    Posts
    30

    Cool Re: Create a new files from different sheets

    Here is a small file that I created. It has 4 sheets.

    Sheet 1 - Has all the info about the orders we have booked for the day

    I am supposed to send one file each for all the sales staff (11 of them) in my example I only have 3.

    Aaron, Chad, Chip - is supposed to ONLY see their own sheet, so that is why I wanted to copy it to its own excel file, so I can send them out, one by one to the sales person.
    They are not supposed to see the other sales staff sales, so if I leave the filter, they can easy change to another name.

    So I would like to save each file as a read only file, so they cant change the filter, when I send them out.

    Ps......... if I could get help from someone to create a macro to send out the email to each of them to would be terrific.
    It would be 22 files (one for orders and one for quotes for each salesperson) so it would be 11 email sent out.

    Super grateful for all help I can get as I am still learning Excel.
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Create a new files from different sheets

    So i got it to create the individual sheets, but since you are using a pivot tables for each person, the data source will point to your "Sheet1" and potentially break the files you send out.. since they will not have access to that sheet for the data.
    Is there a reason you went with pivot table for each? Not saying its wrong, but may not be the route to go for splitting it out and sending via email.

    Are they allowed to see all the columns or they are only to see a summary of their own data?
    Cause you can always apply filtering on the main sheet and create your sheets based on those results and then send out the individual sheets that way...

  10. #10
    Registered User
    Join Date
    02-23-2021
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2016
    Posts
    30

    Re: Create a new files from different sheets

    I did try to apply filter, but I did not know how to move them automatic to the new sheets as I am going to add new info every day to the Sheet1 and then update the other sheets with the info for each salesperson.

    Yes they are allowed to see all columns, just not the other salespeople orders (company policy)

    So every day I will add yesterdays orders to the main file. Update the sheets for each salesperson and send them out in an email, and I don't want to create new sheet everyday for the salespeople, the sheets should look the same every day so they know it is always the same info they get and that the report is easy for them to read.

    I am open do to anything, it do not have to be a pivot table, I did just think that it was the easiest way to do it as I am not so good at writing Macro code.
    I do learn by recording macro and then try to read them and change them a bit.

    Thank you for your feedback

  11. #11
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Create a new files from different sheets

    im working on a possible solution based on your response, so may be later today before i can upload. Since you will be updating daily, you will be emailing them out daily as well? If you have outlook , you can have the macro send the email for you once you complete the update and run the macro..

    Macro can filter, update each sheet, then email each person their own file..

  12. #12
    Registered User
    Join Date
    02-23-2021
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2016
    Posts
    30

    Re: Create a new files from different sheets

    Yes I need to send them out every day, first thing in the morning and yes we use outlook.

    I also have the same file but instead of order it will be quote, but still the same columns and the same people.

    I am super grateful for your help, thank you so much. It makes my work a lot easier and faster.

  13. #13
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Create a new files from different sheets

    Ok here is a possible solution, just run the "RunCreation" macro and it will filter by user and create read only files in the same directory that you have the file
    Attached Files Attached Files

  14. #14
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Create a new files from different sheets

    let me see if i can provide you the email code as well, but at least you have something to work with now that does the first part for you

    So if you are going to email them the results, do you need the file saved? or can the sheet be sent without creating the file?
    just curious if you need both or just one

    Here is a link to a site that have a good example of sending the email with the sheet

    send-emails-outlook
    Last edited by cubangt; 05-13-2021 at 02:57 PM.

  15. #15
    Registered User
    Join Date
    02-23-2021
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2016
    Posts
    30

    Re: Create a new files from different sheets

    Super thanks cubangt. I will add it and try to do all 11 sheets now.

    i have not thought about if I want to save the files before I email them or not. I guess I can email them without creating a file. I will try it out tomorrow. You don't know how much time you have saved for me.
    Thank again.

  16. #16
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Create a new files from different sheets

    Your welcome, if you have questions just post back.

  17. #17
    Registered User
    Join Date
    02-23-2021
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2016
    Posts
    30

    Red face Re: Create a new files from different sheets

    Hello again and I am so sorry but I need some help again. I have tried to figure it out myself, but no luck.


    I have a sheet2, that I never included in the first test file, that also need to be included, it has info that I do a lookup for to sheet1, but it do not need to be copied but maybe it has to be
    excluded from the macro as I get an error message now.

    So I have two sheets open from the beginning Sheet1 and Sheet2, then I run the macro Run_creation and it works,
    it creates a new sheet for all the sales people and then it stops and the error message comes up and say:

    Run-time error '1004'
    Cannot access read-only document 'sheet2'. , then Excel open one new document called sheet2, and then it stops so it never copies the new "salespeople" sheet in to the folder.

    If I try to hide the sheet2, I get this message:
    Run-time error '1004'
    Copy method of Worksheet class failed.

    I know that is has to do something with that second sheet that I need with info, but I cant figure out what do to.
    If I delete the sheet2, it works, but then some of the info in sheet1 disappear as it is linked to sheet2.

    Please if you can help me again. I hope this is my last question for this week

  18. #18
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Create a new files from different sheets

    can you provide a sample file again with this new sheet.. and if you dont need a new "sheet2" created.. then it will probably just be a exclusion in the creation process..

  19. #19
    Registered User
    Join Date
    02-23-2021
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2016
    Posts
    30

    Re: Create a new files from different sheets

    Sorry about that, missing sheet 2.

    The columns in sheet1 that are yellow, has info from sheet 2. and it is still column D I want to filter and create new sheet from and create new files for each salesperson so I can send them out.
    Attached Files Attached Files

  20. #20
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Create a new files from different sheets

    So really nothing has changed as to which files/sheets you want created.. ONLY for the sales person in Column D. and Sheet2 is merely a reference sheet to populate A and B in Sheet 1

  21. #21
    Registered User
    Join Date
    02-23-2021
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2016
    Posts
    30

    Re: Create a new files from different sheets

    Yes that is correct. I just did not add sheet2 in the first document. I just did type in the info from sheet2 to sheet1, when I did the test file.

  22. #22
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Create a new files from different sheets

    try this new file update
    Just had to update the logic to exclude sheet 2 from creation and deletion
    If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then

    As for the error about not being able to access the files in read only status, since the files are created and stored in the same folder as read only, they need to be deleted before you run the process again..
    Now if you dont need actual files being created, then we can do away with that and not have that issue
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    02-23-2021
    Location
    Edmonton, Canada
    MS-Off Ver
    Excel 2016
    Posts
    30

    Re: Create a new files from different sheets

    Perfect, now it works.

    No I dont need the sheet1 and sheet2 to be created again, just the sales people in column D.

    Have a great weekend and super thank to you.

  24. #24
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Create a new files from different sheets

    no problem

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. copy data from closed files to multiple sheets and create report
    By KalilMe in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-05-2021, 04:53 AM
  2. [SOLVED] create sheets based on extensions files and copy to each months
    By ABDELFATTA in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-07-2021, 06:04 PM
  3. Macro to create 2 separate PDF files of 2 sheets - saved in same location
    By Mattyevs1000 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-17-2017, 10:46 AM
  4. Macros between sheets (without create new files)
    By testadimissile in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-17-2017, 02:29 AM
  5. Replies: 2
    Last Post: 09-19-2013, 04:22 AM
  6. Automatically create and fill sheets and then save as seperate files
    By Focus_Kevin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-01-2010, 11:26 AM
  7. [SOLVED] Create seprate files from multiple sheets in excel
    By Shuvro Basu in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-03-2006, 05:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1