Please help me sort then extract the data to new workbooks

[tahrah]

I have a spreadsheet of quotes, samples, and orders. Each row includes
a "representative" field. I want to sort by "representative", then
move that representatives data to a new workbook so I can send to them
for follow up. There are about 40 columns and EVERY column (including
formating) needs to go to the new workbook. Basically I'm just
separating the workbook into separate workbooks so each rep sees only
the information for their territory.

I looked at the pivot table and didn't see how that can help me.

Right now I'm sorting, then cutting and pasting the rep's individual
rows to the new spreadsheet/workbook. Then saving it with their name.

There's got to be a better way and I sure would appreciate anyone's
help.

Thank You,
Tahrah

 

[Ron de Bruin]

Hi

There is a code example here for workbooks
http://www.rondebruin.nl/copy5.htm

Or Install EasyFilter
http://www.rondebruin.nl/easyfilter.htm

 

[tahrah]

Ron - Thank you for the response. I'm a real newbie at this. Would
you mind telling me how I get that code to work with my specific
worksheet? I tried copying it to a macro but that didn't work. Sorry
for the trouble.

Regards,
Tahrah

 

[Ron de Bruin]

Hi

The macro you copy in a normal module in your workbook
Change the folder name to where you want the files and the sheet name to where the data is

I use
Set rng = ws1.Range("A1").CurrentRegion '<<< Change

That mean that the data start in A1 and that row 1 have headers
My example filter on Column A
rng.Columns(1).AdvancedFilter _

If your filter column = C then change the 1 to 3

Post back if you need more help
I am going away for a few days, sunday I am home.



Sub Copy_With_AdvancedFilter_To_Workbooks()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim WBNew As Workbook
Dim rng As Range
Dim cell As Range
Dim Lrow As Long
Dim FileFolder As String

FileFolder = "C:\Data\" '<<< Change
Set ws1 = ThisWorkbook.Sheets("Sheet1") '<<< Change
'Tip : You can also use a Dynamic range name, http://www.contextures.com/xlNames01.html#Dynamic
'or a fixed range like Range("A1:H1200")
Set rng = ws1.Range("A1").CurrentRegion '<<< Change

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ws1
'This example filter on the first column in the range (change this if needed)
'You see that the last two columns of the worksheet are used to make a Unique list
'and add the CriteriaRange.(you can't use this macro if you use the columns)
rng.Columns(1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("IV1"), Unique:=True

Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row
.Range("IU1").Value = .Range("IV1").Value

For Each cell In .Range("IV2:IV" & Lrow)
.Range("IU2").Value = cell.Value
Set WBNew = Workbooks.Add
On Error Resume Next
On Error GoTo 0
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("IU1:IU2"), _
CopyToRange:=WBNew.Sheets(1).Range("A1"), _
Unique:=False
WBNew.Sheets(1).Columns.AutoFit
WBNew.SaveAs FileFolder & Format(Now, "yyyy-mmm-dd hh-mm-ss") & " Value = " & cell.Value
WBNew.Close False
Next
.Columns("IU:IV").Clear
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[tahrah]

Hi Ron, Thank you again for all your help.

Now this is wierd. I copied everything over. Made the changes where
you marked "change". Ran the macro. It worked.

THEN, I copied over more data into the spreadsheet from another
identical spreadsheet. Now, it won't work anymore. Columns are all
exactly the same and the columns I'm sorting is column #11 which is
still what it says in the macro. But now, I'm getting a big red X and
the number 400 for the error message? Any ideas? I've tried rebooting
and everything thinking it had something to do with the computer and
not the macro.

Also, there is a 2nd sheet on this workbook. Is there any way to get
this same macro to also sort and copy over the same reps data from the
2nd sheet as well? Or should I create a 2nd macro for the 2nd sheet
and just have it create all new spreadsheets as well?

I appreciate your help. You are WONDERFUL!!!

Regards,
Tahrah

 

[Ron de Bruin]

Hi tahrah

Send me the file and I look at it tomorrow after work

 

[tahrah]

Ron, After some tinkering I figured it out. The script copies some
data to the last two columns. If I delete that data before running the
script, the script works GREAT. I am sooooooooooo happy! You are
wonderful.

Over the weekend I created a new macro that deletes the data in the
last two columns, runs the script, goes to the 2nd sheet and deletes
the data in the last two columns, then runs the script.

This task that used to take me ALL DAY, now takes about 5 minutes.

I truly am grateful for your help.

Best Regards,
Tahrah

 

[Ron de Bruin]

Hi tahrah

There is a note in the macro about this

'You see that the last two columns of the worksheet are used to make a Unique list
'and add the CriteriaRange.(you can't use this macro if you use the columns)


When the macro is ready it delete the data in the last two columns

.Columns("IU:IV").Clear

You can do that also before you run the code