separate large file into separate worksheets (by state)

**[email protected]** · 01-31-2013, 03:47 PM

i have a workbook with over 40,000 records. i need to separate the original into 50 separate workbooks by state. HELP...

**Alf** · 01-31-2013, 04:01 PM

Upload a small sample of your workbook to show how the data is orgazied.

Alf

**[email protected]** · 01-31-2013, 04:12 PM

Here you go. Thank you for being willing to help. Totally lost and on a very tight deadline.

**Alf** · 02-01-2013, 02:20 AM

Ok here is a macro that should do what you wish. I've also added some comments.

The sheet where the macro reads the data must be called "Sheet1". Macro uses autofilter to split information and makes a temporary storage to a sheet. This sheet is then saved as a workbook in a folder on the C drive so this folder "All States" on the C drive must exist or the macro will stop.

You can of course change folder name and drive to whatever you like (change string "C:\All States" to suit your needs) and you can also change sheet name "Sheet1" to whatever you wish as long as you replace all instances of "Sheet1" in the macro.

The result will be saved as a workbook named "XX.xls" where XX is the two letter code for the state name and the temporary sheet deleted.

Option Explicit

Sub SplitData()
Dim sh As Worksheet
Dim cell As Range
Dim i As Integer
Application.ScreenUpdating = False
'This setting will make it possible to delete sheets without Excel asking for 'confimation
Application.DisplayAlerts = False

' Copy all state names names
Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row).Copy
'Add sheet "Analyse" and past all names from "Sheet1" to this sheet
Set sh = Worksheets.Add(After:=Sheets("Sheet1"))
sh.Name = "Analyse"
Range("A2").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
'Sort state names and name range "Raw_data"
ActiveSheet.UsedRange.Sort Key1:=Range("A2"), Order1:=xlAscending
ActiveSheet.UsedRange.Name = "Raw_data"
i = 0
'Checking each value in "Raw_data" to find unique names and write those names to C2, C3 and so forth
For Each cell In Range("Raw_data")
       cell.Value = Trim(cell.Value)
            If Application.CountIf(Columns(3), cell) = 0 Then
                Range("C2").Offset(i, 0) = cell.Value
               i = i + 1
            End If
     Next cell
'Naming the rang "unique" with help of the the i value
Range("C2:C" & i + 1).Name = "unique"
'Looping through all vaues in range "unique", activating "Sheet1" sheet and setting an autofilter
'using unique value as criteria and copy the filtered values.
For Each cell In Range("unique")
Sheets("Sheet1").Activate
With ActiveSheet
    .AutoFilterMode = False
    .Range("A1:N1").AutoFilter
End With
Selection.AutoFilter Field:=5, Criteria1:=cell.Value, Operator:=xlAnd
ActiveSheet.AutoFilter.Range.Copy
'Adding a new sheet at the end using unique value as the sheet name
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = cell.Value
'and copy filtered range to cell A1.
Range("A1").PasteSpecial Paste:=xlPasteValues
Columns("A:N").Columns.AutoFit
Application.CutCopyMode = False
ActiveSheet.Copy
' Save sheet as workbook to folder "C:\All States\" as an xls file
ActiveWorkbook.SaveAs Filename:="C:\All States\" & cell & ".xls"
' Close the workbook and delete sheet
ActiveWorkbook.Close
ActiveSheet.Delete

Next cell

'Cleaning up, delete sheet "Analyse" activate "Sheet1" and remove autofilter
Sheets("Analyse").Delete
Sheets("Sheet1").Activate
Range("A1:N1").AutoFilter

'DisplayAlerts and ScreenUpdating set to default i.e. "True"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Alf

**[email protected]** · 02-01-2013, 11:25 AM

Ok. So, I have my sheet named properly and I have the "All States" Folder on the C drive. How do I actually get it to work? Do I record a new Macro and just paste the code into a workbook in A1?

**Alf** · 02-01-2013, 12:09 PM

Do I record a new Macro and just paste the code into a workbook in A1?

No you need to enter the code in a macro window.

Click on “Developers” tab (Macro1.png)

Then top, left click on “Visual Basic” (Macro1.png)

In the new windows that opens click “Insert” and then click “Module” (Macro2.png)

Copy the code I wrote and paste it in the “Module” window. (Macro3.png)

Close window (click white x in red square)

Click “Macro” to bring up macro window and click “Run” (Macro4.pgn)

Alf

**[email protected]** · 02-01-2013, 12:21 PM

Oh my goodness. I am so appreciative I can't express it!

Thank you so very much.

**[email protected]** · 02-01-2013, 12:15 PM

I got all the way to the "Insert Module" part previously, but "Module" was grayed out...

Let me try again. And thank you for the screen captures.

**Alf** · 02-01-2013, 01:16 PM

Oh my goodness. I am so appreciative I can't express it!

Welcome to the wonderful world of Excel macros!

I assume from this that it worked the way it was supposed to do. Fine, could you then please mark you post "Solved" as per forum rules and if you like to give my answer a rating click on the small star (bottom left) in my post and give my answer a rating.

Alf

Ps I'm a bit curious as I've never had masses of data like that could you do a guessimate and tell me how long it took from when you clicked "Run" until the job was done?

Ps Ps If you do a rerun you better empty the target folder first as Excel will be nagging you about "Target file exists, do you want to over write?" even if I set
Application.DisplayAlerts = False for the macro run.

**[email protected]** · 02-01-2013, 01:19 PM

Alf,

It was so lightening fast--maybe 10 seconds. And I actually had to run multiple files. I renamed the first folder and created a new one with the name you provided. And yes, I will follow the protocol. Thank you for making me aware of it.

Perri

**[email protected]** · 02-01-2013, 01:16 PM

Would it also be a Macro that would allow you to take the individual Excel files (by state) and convert them to a specific Avery mailing label?

**Alf** · 02-01-2013, 01:40 PM

Would it also be a Macro that would allow you to take the individual Excel files (by state) and convert them to a specific Avery mailing label?

Yes that would certainly be possible I think. But you must supply more information. I would recommend starting a new thread with title "Converting Excel files to Avery mailing label" par example.

If you look at the macro before the Next statement there is a part where the workbook is created, saved and closed. The next line deletes the worksheet that has all the information for a particular state. If it's easier to mail a worksheet you just put an apostrophe in front of the line

'ActiveSheet.Delete

then you keep all the 52 worksheets.

You would also need a bit of code in the beginning to delete all extra sheets unless you prefer to do it manually but that assumes you do this quite often and I got the impression this was a one time job.

Alf

**[email protected]** · 02-01-2013, 01:41 PM

I will. Thank you. I read that in the rules as well. Again, I am so appreciative of your assistance.

separate large file into separate worksheets (by state)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

separate large file into separate worksheets (by state)

Re: separate large file into separate worksheets (by state)

Re: separate large file into separate worksheets (by state)

Re: separate large file into separate worksheets (by state)

Re: separate large file into separate worksheets (by state)

Re: separate large file into separate worksheets (by state)

Re: separate large file into separate worksheets (by state)

Re: separate large file into separate worksheets (by state)

Re: separate large file into separate worksheets (by state)

Re: separate large file into separate worksheets (by state)

Re: separate large file into separate worksheets (by state)

Re: separate large file into separate worksheets (by state)

Re: separate large file into separate worksheets (by state)

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions