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

Hi, YasserKhalil,

I waited a long time watching any reply

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

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

Hi, YasserKhalil,

maybe try:

Please Login or Register  to view this content.

Ciao,
Holger

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

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

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

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

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