• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting data to new workbooks

B

brightyoyo

New Member
Hi I am looking for a macro that will export my data into two new workbooks based on the information in column E (primary or secondary). Here is a sample and I included a file


https://www.dropbox.com/s/af6bvmoaluikb94/7064378F.xlsx

[pre]
Code: 
C1	4642.5	      1447.28	              180             PRIMARY
C2	5252	        446.5	                0	      PRIMARY
C3	4572.7	      1257.25	               90	      PRIMARY
C7	5095.45	        996.2	              270	      SECONDARY
C8	5140	      1177.44	               90	      SECONDARY
C9	5050.15	      1177.44	               90	      SECONDARY
[/pre]

Also I would like the new workbooks to have the same name as the beginning file with _primary or _secondary added.


Thank You
 
Hi, brightyoyo!

Should this help?

http://chandoo.org/wp/search/?q=Extracting%20data%20to%20new%20workbooks

Regards!

PS: If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.

PS2: I did that and a I got a lot of links.
 
Hi, brightyoyo!

Check this, you'll only have to change the column value and the grouping condition:

http://chandoo.org/forums/topic/split-data-in-to-multiple-files-as-per-cell-value

Just advise if any issue arises in the fixing process.

Regards!
 
Hi SirJB7

I am going to try and modify it to fit my needs, but I still need to filter it to get the primary and secondary together. Also this macro is exporting columns and I need it to do rows. So, how to I get it to find the rows no matter how many primary or secondary there are.


Thanks
 
Hi, brightyoyo!

I was just adapting it for you when looking for another thing I run into this:

http://www.rondebruin.nl/win/s3/win006.htm

Job's done, I guess.

Regards!
 
Hi SirJB7,

I came across that earlier. Is there a way to make it create a new workbook for my primary and secondary criteria.
 
Hi, brightyoyo!


But that is what the workbook does with the 3rd button in cell A5.


In the module ModSplitInWorkbooks do:


a) Change this:

Set My_Range = Range("A11:D" & LastRow(ActiveSheet))

to this:

Set My_Range = Range("A1:E" & LastRow(ActiveSheet))


b) Change this:

FieldNum = 1

to this:

FieldNum = 5

and check what was written just before:

'This example filters on the first column in the range(change the field if needed)

'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......


Regards!


PS: Just in case you haven't analyzed all the modules (but I assume you did it), just keep these two:

ModSplitInWorkbooks

ModFunctions
 
Hi, brightyoyo!

You don't need to specify them as it creates a subfolder with date and time on current path and there it creates the files upon the values of column key (E, or 5) in this case. In your case 2 files will be created, PRIMARY.xlsx and SECONDARY.xlsx.

Regards!
 
Hi SirJB7

The macro works, except I had to change

Code: 
Set My_Range = Range("A1:E" & LastRow(ActiveSheet)) I was getting an error with LastRow

to

Set My_Range = Range("A:E")

However, the new workbooks are created in my documents folder. Is there a way to have them created in the folder that the intial file resides.
 
Hi, brightyoyo!


As you could have seen in the code, which begins with:

-----

[pre]
Code: 
'This example will create a new folder for you and will create
'a new workbook with the data of every unique value in this folder.
'The workbooks will be saved with the Unique value in the new folder.
-----

there is only one place where it defines the path:

-----

[code]'Fill in the pathfolder where you want the new folder with the files
'you can use also this "C:UsersRontest"
MyPath = Application.DefaultFilePath

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "" Then
MyPath = MyPath & ""
End If

'Create folder for the new files
foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & ""
MkDir foldername[/pre]
-----

so if you want to change the default path for your actual workbook path change this:

-----

Application.DefaultFilePath

-----

to this:

-----

ThisWorkbook.Path[/code]

-----


I think it'd be very helpful reading the code documentation (commented lines).


Regards!
 
Hi, brightyoyo!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top