User input to split worksheet into X parts, but ensure it includes all entries with same ID in column C

stratplayer

New Member
Joined
Jun 11, 2015
Messages
9
Hi everyone I have done lots of work to take a 20,000 line file and cleanse it into the key elements required to split amongst our team to work on, I did it using VBA and it was only the 2nd attempt at doing such a thing. I am sure if I posted it then lots of people would improve my work, but before I do that I want to do the following if possible and am looking for some guidance in how to do so.

Working on a worksheet named "Working Data"

Prompt user for how many worksheets/files to create for data

Split worksheet into X seperate worksheets. So if there were 10,000 lines (top being a header) and the user selected 4 files then it would seperate it into 4 x 2500 (although last would be 2499). However, in Column C there is a unique ID that ideally I want to keep together in one file. So can the selection keep selecting until the next entry in the file in Column C is unique, then create the worksheet. The remaining data "Working Data" is then split 3 ways, again using same technique... until there is nothing left after a total of 4 passes.

Then saving each worksheet one as 20180715_username where the end user is prompted for the name to save

I appreciate this is a big ask, well in my opinion, any help would be massively appreciated.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort of linked to the above, I am trying to copy a selection of data from one worksheet called "Working Data" into another worksheet

When I try it manually I can't get the header with filter settings to copy across (tried all the options on Paste As).

I am looking to do this in VBA so I can automate it. Can anyone help point me in the right direction?
 
Upvote 0
Just found this bit of code, I think this will do what I am looking for, unless there is a better way to do it as part of the copy?

Sub TurnAutoFilterOn()
'check for filter, turn on if none exists
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top