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

Hi

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

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"tahrah" wrote in message oups.com...
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 - 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 wrote:
Hi

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

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"tahrah" wrote in message oups.com...
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

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"tahrah" wrote in message oups.com...
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 wrote:
Hi

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

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"tahrah" wrote in message oups.com...
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

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 wrote:
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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"tahrah" wrote in message oups.com...
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 wrote:
Hi

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

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"tahrah" wrote in message oups.com...
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

Hi tahrah

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"tahrah" wrote in message oups.com...
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 wrote:
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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"tahrah" wrote in message oups.com...
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 wrote:
Hi

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

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"tahrah" wrote in message oups.com...
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