+ Reply to Thread
Results 1 to 22 of 22

Create New WorkBooks based on conditions from main workbook

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,803

    Create New WorkBooks based on conditions from main workbook

    Hello everybody

    I have main workbook named "Mainwb" in the first sheet named "Main" I have range("A2:A6") which has values like that (Example 1, Example 2 and so on ) as a sample ...

    I want the following : to export range("A2:A6") values to new workbooks with the same name (Example 1 , Example 2 and so on) and the new WBs to be in folder created if not found with a name (Results).

    Secondly : The new WBs should contain sheets based on the values mentioned .. Say we export (Example 1) as new WB .. the code should search in Mainwb in all sheets --except sheet("Main") -- search for the rows containing that value and copy these rows to the new WB.. with the headers !!

    Another point the sheets in the new WB holds the same name which the results copied from .. If the value Example 1 found in sheet ("A") then the new WB should contain a sheet with the same name ("A")
    I feel it is difficult but I hope to be solved
    Attached Files Attached Files
    Last edited by YasserKhalil; 11-16-2014 at 08:06 AM.

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,803

    Re: Create New WorkBooks based on conditions from main workbook

    Anybody help me about that even if half solution?

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,803

    Re: Create New WorkBooks based on conditions from main workbook

    Any idea about what can I do to achieve that task?

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,803

    Re: Create New WorkBooks based on conditions from main workbook

    I waited a long time watching any reply .. I need your help about that

  5. #5
    Registered User
    Join Date
    10-21-2014
    Location
    VIET NAM
    MS-Off Ver
    2007
    Posts
    9

    Re: Create New WorkBooks based on conditions from main workbook

    Hi,
    Try my best but do not understand what you mean

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,043

    Re: Create New WorkBooks based on conditions from main workbook

    Hi, YasserKhalil,

    I waited a long time watching any reply
    Quote Originally Posted by Forum Rules
    If your question has not been answered within a day
    You ruin your chances of getting any answers on pushing your thread on a weekend. Please remember this is the free help section, if you are desperately waiting for a solution please consider the Commercial Services part on EF.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,803

    Re: Create New WorkBooks based on conditions from main workbook

    I can ask for one request
    I want to export the values of range("A2:A6") as new workbooks!

  8. #8
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,043

    Re: Create New WorkBooks based on conditions from main workbook

    Hi, YasserKhalil,

    you would need to copy and paste/pastespecial when a new workbook is added. Code for the range being located on Sheets MySampleSheet may look like this, no errorhandling is included:
    Please Login or Register  to view this content.
    Ciao,
    Holger

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,803

    Re: Create New WorkBooks based on conditions from main workbook

    Thanks a lot Mr. HaHoBe to be the first who reply my thread
    It seems that I can not clarify my request well

    I want to create new WBs depending on the range A2:A6 to a folder named (Results)
    The (Results) folder should have five Wbs with the following names Example 1 , Eample 2 , Example 3 and so on
    Attached Files Attached Files

  10. #10
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,043

    Re: Create New WorkBooks based on conditions from main workbook

    Hi, YasserKhalil,

    maybe try:
    Please Login or Register  to view this content.
    Ciao,
    Holger

  11. #11
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,803

    Re: Create New WorkBooks based on conditions from main workbook

    Mr. HaHoBe
    Great solution. Your code is a very big step and a very good result ..
    Thank you very much for this wonderful solution..

    Another point : I want to deal with the new WBs by deleting the sheet if Column J does not have (WorkBook Name)
    say In Example 1 workbook which was created .. to search in sheets for the string (Example 1) if the sheet does not contain that string in column J .. then the sheet to be deleted .. If the whole workbook doesnot contain that string the WB to be deleted

    I know it is difficult request but I'm sure you will be able to deal with that big problem

  12. #12
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,043

    Re: Create New WorkBooks based on conditions from main workbook

    Hi, YasserKhalil,

    I would recommend to check the worksheets prior to creating workbooks like I did. But Iīm going to stick with that approach for now.

    Please Login or Register  to view this content.
    Ciao,
    Holger

  13. #13
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,803

    Re: Create New WorkBooks based on conditions from main workbook

    Mr. Holger I appreciate your great effort and great help
    You are fantastic , wonderful amazing ...
    Now the file is about to be perfect

    The last point : I promise you to be the last

    I want the new WBs that were created to find the string in each sheet and delete others
    Say in Workbook that was created (Example 1) .. I want to delete the rows that does not contain the string (Example 1) except for titles to remain as it is
    In other words to remain the data related to Example 1 only in each sheet!

    Thanks advanced for the great help you offered me Mr Holger

  14. #14
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,043

    Re: Create New WorkBooks based on conditions from main workbook

    Hi, YasserKhalil,

    The last point : I promise you to be the last
    Might be, what I definitely can say: itīs one point too much for me in this thread. Use the loop through the sheets and the condition that sheet will not be deleted, add an Autofilter to that sheet, criteria would be in Column 10 as "<>*Example 1*", get the last visible row, use all visible cells in Column J in the range from Row 2 to the last visible row to delete the entire rows, disable Autofilter. Itīs as easy as this description.

    Ciao,
    Holger

  15. #15
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,803

    Re: Create New WorkBooks based on conditions from main workbook

    Hi Mr. Holger
    Thank you for this description .. The problem is that I'm just a beginner and I can not do it by myself..
    I know you are exhausted and tired of me .. I'm sorry...But you give me very good results till now.
    Waiting for some dude to finish this last point

  16. #16
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,043

    Re: Create New WorkBooks based on conditions from main workbook

    Hi, YasserKhalil,

    you could at least open one of the newly created workbooks, turn on the macro recorder and do the steps as they are listed (for Example 1). The very wrong attitude is to state
    The problem is that I'm just a beginner and I can not do it by myself.
    The process of using an AutFilter with a fixed name could have brought in by you. I must admit that the macro recorder will not help on loops or with ranges or criterias but that would have been an easy one if the code had been provided.

    Please Login or Register  to view this content.
    Ciao,
    Holger

  17. #17
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,803

    Re: Create New WorkBooks based on conditions from main workbook

    Great Mr. Holger I don't know how to thank you for this fantstic code ..
    As for Example 1 WB it is perfect excellent ...
    But the Example 2 WB is not so good as no headers in Sheet("A") , Sheets("C") and Sheets("D") ..
    thank you very much for your valuable time you spent to solve my problem

  18. #18
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,803

    Re: Create New WorkBooks based on conditions from main workbook

    Mr. Holger
    I do my best trying to achieve my target in a nother approach:
    Look at the following I can get my results at first sheet only in the new WBs

    Please Login or Register  to view this content.
    I try to abandon filter and use Rows delete .. But I can't get it correctly
    This is the last point which will achieve my task

  19. #19
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,043

    Re: Create New WorkBooks based on conditions from main workbook

    Hi, YasserKhalil,

    good effort but you must remember to fully qualify the ranes you work on.

    Please Login or Register  to view this content.
    will always refer to the ActiveSheet, you would need to alter this code line to
    Please Login or Register  to view this content.
    to refer to each of the worksheets independently.

    If iI use the prefix i to any variable that would be of Type Integer, I would expect a small L for Long there. But thatīs minor as you correctly declared the Variable (in case you have more data than 32.767).

    My version of the code:
    Please Login or Register  to view this content.
    As you may see the code is basicly identical to the one you posted.

    Ciao,
    Holger

    Ciao,
    Holger

  20. #20
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,803

    Re: Create New WorkBooks based on conditions from main workbook

    Mr. Holger
    You give me important lessons in vba .. I learned a lot from your code
    thank you very much for your time and effort . I appreciate your help and it is my honour to be a follower to you my professor

    I found the solution in a line
    I have to activate ws to loop through the sheets
    Please Login or Register  to view this content.

  21. #21
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,043

    Re: Create New WorkBooks based on conditions from main workbook

    Hi, YasserKhalil,

    one way to get past only working on ActiveSheet. But any Selection or Activating will take time, the more you do so the longer the running time of any macro will be (plus itīs unnecessary in about 99% of all cases where itīs used).

    Ciao,
    Holger

  22. #22
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,803

    Re: Create New WorkBooks based on conditions from main workbook

    Thanks a gain for valuable information.
    I did not notice your post #19
    You are very helful my professor.. and I'm sorry if I did not clarify my requests well and for asking for every request independtly
    Have a nice time my Professor

+ 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. Using Vba create another workbook then update the formulas of main workbook
    By rename in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-29-2012, 06:12 AM
  2. Generate separate workbooks from main Workbook
    By robbie58 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2012, 05:08 PM
  3. Create new workbooks based on data from current workbook
    By remedina in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2012, 02:03 PM
  4. Close all workbooks except for the main workbook
    By Khaos176 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-21-2009, 02:48 PM
  5. Importing data from several existing workbooks into main workbook
    By Incoherent in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-22-2005, 05:15 AM

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