Splitting out data to create multiple files

**pauldaddyadams** · 07-01-2018, 12:23 PM

HI,

I have an excel spreadsheet which will have data for lots of accounts.

I would like a macro which would split out this data automatically to separate files.

In my example I have three profit centres:
PC 1
PC 2
PC 3

I would like three files created if possible. I have previously had a macro that did something similar so I am hoping that this can be done.

One requirement would be that the file is saved based on the account name e.g. "TB PC 1".XLS (TB needs to be editable as there is something exact I need it to be but I am unsure at this stage what it is)

Can anyone guide me in any way please?

Paul

**PCI** · 07-01-2018, 12:58 PM

TB needs to be editable

Editable in the macro or an input mesage to the user ???

**pauldaddyadams** · 07-01-2018, 01:02 PM

Hi,

Ideally yes. It wouldn't have to be here but this is the preferred choice.

If its within the script and I know where then that would be ok (I am not very good in VB)

**xladept** · 07-01-2018, 01:36 PM

If you really want to save in 1997-2003, we'll have to modify the code - otherwise try:

Please Login or Register  to view this content.

**pauldaddyadams** · 07-01-2018, 01:57 PM

Hi,

Code it looking good!

It doesn't have to be the old version so the code is ok in regards to that - thank you.

How does it work in terms of where it is where it is saved? Currently I opened the file from my desktop and that's where is has stored the new files.

Is there any way it can retain the first 1-6 rows?

**xladept** · 07-01-2018, 02:05 PM

Sorry about the omission:

Please Login or Register  to view this content.

*If you want to save in another place we need to specify the path now coded in red above.

**pauldaddyadams** · 07-01-2018, 02:13 PM

Thank you for sorting the rows 1-6.

In regards to the ws.Parent.Path I will come back in regards to this point - I need to know the correct path name

**xladept** · 07-01-2018, 02:56 PM

Please Login or Register  to view this content.

When you get it you can enter it in the red above

**pauldaddyadams** · 07-01-2018, 04:15 PM

Hi,

Your post in number 8, how is that different to 6?

I notice in the code you have this:
PC = Array("PC 1", "PC 2", "PC 3")

In my actual data I have approx. 800 different "PC"s - would I need to list them as this wouldn't be possible. Is there any way to make this dynamic?

**PCI** · 07-01-2018, 04:20 PM

See next code
Adjust WkPath if not correct

Please Login or Register  to view this content.

**pauldaddyadams** · 07-01-2018, 04:33 PM

Hi,

I have tried this (attached) and I couldn't get it to work. In the message box its asking for the user to input something - what is this?

In my example you will see the PCs have different random names.

The macro would need to either be based on each unique PC number (column C) or PC Name (column D) - Column C would be the preference.

**PCI** · 07-01-2018, 04:38 PM

In the message box its asking for the user to input something - what is this?

This the TB to be editable

**pauldaddyadams** · 07-01-2018, 04:41 PM

Apologies for me not understanding - what should I put in this would it be column C so a "c"?

**PCI** · 07-01-2018, 04:48 PM

No enter TB or something else you want

**PCI** · 07-01-2018, 04:52 PM

If you want to change the column where the Profit Center is change

Please Login or Register  to view this content.

If you want to change where is the header row change

Please Login or Register  to view this content.

**pauldaddyadams** · 07-01-2018, 04:55 PM

Originally Posted by PCI

No enter TB or something else you want

In my example file I have 3 unique PC numbers or 3 unique PC names? What would I have to enter specifically as I’m struggling to understand?

Would I have to list all the names of each unique reference as this could take sometime in my actual data as I have 800+ and I’d be bound to make an error.

**PCI** · 07-01-2018, 04:57 PM

Would I have to list all the names of each unique reference

=> NO

Just make a run entering TB and see what's happen ... you will understand

**PCI** · 07-01-2018, 05:00 PM

It may have a confusion between "PC Name" and "Profit Centre"
=> Adjust

Please Login or Register  to view this content.

**PCI** · 07-01-2018, 05:13 PM

as I have 800+

So you may have also a long time treatment, we may see after, but this due because there is some files manipulations ...!

**xladept** · 07-01-2018, 05:25 PM

You're welcome and thanks for the rep!

The difference between those posts was that I added a string variable for the path and corrected the slash to a backslash.

It looks now though as PCI is taking good care of you.

**xladept** · 07-01-2018, 08:03 PM

Here's a version with unlimited profit centers using the dictionary like PCI has done:

Please Login or Register  to view this content.

**pauldaddyadams** · 07-02-2018, 03:42 AM

@xladept Thank you for sending the code in post #21. Can I ask please, which column it is based on, at each change in column C or D?

@PCI, In the message box I have entered "TB", "C", "PC Name", "Profit Centre" and more but each time I get error 400.

**PCI** · 07-02-2018, 04:19 AM

@PCI, In the message box I have entered "TB", "C", "PC Name", "Profit Centre" and more but each time I get error 400.

Just put TB
Is there a statement shown when there is the error 400
See attached you file with the macro

**pauldaddyadams** · 07-02-2018, 04:32 AM

Hi

The requirement for the file name would be name would be:
Profit Center Number-TrialBalance-Period (MMYYYY)”

So this for the first file would be "1012102-Trialbalance-072018"

Can we include this in the code please?

**pauldaddyadams** · 07-02-2018, 04:44 AM

Hi, When I run the code with "TB" in the message box I get an error 53 with this section highlighted in the code "Kill WkFN ' TO REMOVE the FILE"

**PCI** · 07-02-2018, 05:08 AM

Clear
New Macro

Please Login or Register  to view this content.

**pauldaddyadams** · 07-02-2018, 05:28 AM

Hi,

That works when I enter the "TB" in the message box - why do i need to do this out of interest?

Is this code working on each new instance of column C or D currently as it needs to be C please.

The file name needs tweaking e.g. currently its "TB 1060100", it would need to be "1060100-Trialbalance-072018" if possible please?

**PCI** · 07-02-2018, 05:45 AM

That works when I enter the "TB" in the message box - why do i need to do this out of interest?

Because in Thread #3 you were suggesting to do it , now we can record it in VBA, up to you to change it inside the code when needed

Is this code working on each new instance of column C or D currently as it needs to be C please.

Yes the column in reference is C see

Please Login or Register  to view this content.

The file name needs tweaking e.g. currently its "TB 1060100", it would need to be "1060100-Trialbalance-072018" if possible please?

Yes of course it is possible
Is "Trialbalance-072018" a constant or something to enter ... like the previous "TB" ...?

**PCI** · 07-02-2018, 05:54 AM

Try and comment

Please Login or Register  to view this content.

**pauldaddyadams** · 07-02-2018, 06:20 AM

Can I remove the requirement please to have the user input anything? I wouldn't know how to adjust the code.

Where in the code do I need to input the change to the file name so it would be:
Column C PC number
The word "Trial Balance"
the period "072018"

**pauldaddyadams** · 07-02-2018, 06:22 AM

Our messages crossed!! This is Perfect I believe, just what is needed!!

**PCI** · 07-02-2018, 06:33 AM

Where in the code do I need to input the change to the file name so it would be:
Column C PC number

it is done automùatically.

Where in the code do I need to input the change to the file name so it would be:
The word "Trial Balance"
the period "072018"

See code

Please Login or Register  to view this content.

**PCI** · 07-02-2018, 07:43 AM

Our messages crossed!! This is Perfect I believe, just what is needed!!

Ouf..!

So the story is done ...?
Thx for the rep... enjoy Excel
PCI

**pauldaddyadams** · 07-02-2018, 08:59 AM

Potentially - it should be done.... But. I have become aware of a requirement to delete some columns from each file.

should I post it here or as a new request?

**pauldaddyadams** · 07-02-2018, 09:27 AM

Hi,

I am reluctant to post because of the change from the original requirements but I think it can be achieved by amending the current code (and then adding the part to delete columns).

1. You will see that I no longer have header rows which need to be retained. Only the first row
2. Column B is now the column to be split (to create each new file)
3. Columns A:E need to be deleted (so I am left with just three columns as shown on the tab TB load Format)

Can you help out again please, sorry!

**PCI** · 07-02-2018, 10:20 AM

Please open a new thread and make reference to this one: Thread title is important because it is used as key to search information.
Now we don nor split anymore

**PCI** · 07-02-2018, 11:22 AM

Still the way to work ???:
Save in the same folder as the orginal file
Save with same format
File name as seen before

**pauldaddyadams** · 07-02-2018, 11:41 AM

Originally Posted by PCI

Still the way to work ???:
Save in the same folder as the orginal file
Save with same format
File name as seen before

I dont understand!?!? I opened up a new thread with the requirements.

**xladept** · 07-02-2018, 12:02 PM

Field:=4 means column D as does ws.Cells(n, 4)

Please Login or Register  to view this content.

**pauldaddyadams** · 07-02-2018, 12:32 PM

Is there a way to amend this part:

Please Login or Register  to view this content.

As the 1060100 was just an example. It would have to be based on the cell value which I think its doing at the end. e.g.

This: 1060100-Trialbalance-072018 310030.XLSX

Needs to be: 310030-TrialBalance-072018

**pauldaddyadams** · 07-02-2018, 12:34 PM

Do I now need to change this:

Please Login or Register  to view this content.

to 1?

Lastly - is there anyway to then delete columns A:D?

**xladept** · 07-02-2018, 01:26 PM

Just put 310030-TrialBalance-072018 inside those quotes or designate a field on the sheet for that value. The n is for the first row of data in that line.

To delete Columns 1-4:

Please Login or Register  to view this content.

*If you have a differently organized sample - please post it.

**pauldaddyadams** · 07-02-2018, 01:33 PM

Hi, I wouldn’t want the first part to be fixed though. The first digits need to be automatically populated based on the PC number column

**pauldaddyadams** · 07-02-2018, 01:57 PM

Hi,

I changed the code to this:

Please Login or Register  to view this content.

And its almost working perfectly. How do I change this:

Please Login or Register  to view this content.

So the PC appears ahead of the "-TrialBalance-072018"?

**xladept** · 07-02-2018, 02:04 PM

Just put BN = K(n) & " " & TB & ".XLSX"

**pauldaddyadams** · 07-02-2018, 02:19 PM

Hi, The file name is now working as it should however having checked the files the data isnt being pulled correctly.

I have another macro to compare to. When I run this I have 5 files and with the splitter (renamed to this from pauldaddyadams) it produces 6 as there is a file created for the header row which it shouldnt create.

The data in the files is also duplicated - it isn't pulling the data for that PC number only.

Any ideas why?

**xladept** · 07-02-2018, 03:19 PM

How about:

Please Login or Register  to view this content.

**pauldaddyadams** · 07-02-2018, 04:11 PM

I think we are there!!!!

I am going to test tomorrow by uploading into the system but i think we have achieved what we need to!!

Thank you so much for helping on this!

**xladept** · 07-02-2018, 04:20 PM

You're welcome!

But it shouldn't take hours

Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Re: Splitting out data to create multiple files

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions