Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Separating a List onto multiple worksheets in the same workbook based off 1 criteria
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Separating a List onto multiple worksheets in the same workbook based off 1 criteria
I have a template that I'm trying to develop for daily use in my
office. The template is a tool to help my supervisors check the work
for the next day. I work for a service organization that covers a 3
state area.....I have routers that plan the day for the techs but I
need a fast tool for my supervisors to be able to be the safety net if
you will for them. I can have them import the routes into map point
but the problem is now it is very manual and time consuming.....I
would to create a code that would take the list and separate each
route into its own worksheet in the workbook.....I just have no idea
where to start. Any ideas???

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,355
Default Separating a List onto multiple worksheets in the same workbook ba
Let's say you have a worksheet with the following information on it.

Col A Col B
Route # Stop #

And your data is stored in rows 2 - N

Try something like this:

Sub SPlitRoutes()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim newWS As Worksheet
Dim i As Integer
Dim lrow As Integer

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet

For i = 2 To aWS.Cells(Rows.Count, 1).End(xlUp).Row
Set newWS = Nothing
On Error Resume Next
Set newWS = aWB.Worksheets(aWS.Cells(i, 1))
On Error GoTo 0
If newWS Is Nothing Then
Debug.Print aWS.Cells(i, 1).Value
Set newWS = Worksheets.Add(After:=Worksheets(aWB.Worksheets.Co unt))
newWS.Name = aWS.Cells(i, 1).Value
newWS.Cells(1, 1).Value = aWS.Cells(1, 1).Value
newWS.Cells(1, 2).Value = aWS.Cells(1, 2).Value
newWS.Cells(2, 1).Value = aWS.Cells(i, 1).Value
newWS.Cells(2, 2).Value = aWS.Cells(i, 2).Value
Else
lrow = newWS.Cells(Rows.Count, 1).End(xlUp).Row
newWS.Cells(lrow + 1, 1) = aWS.Cells(i, 1)
newWS.Cells(lrow + 1, 2) = aWS.Cells(i, 2)
End If

Next i
End Sub



"DMRbaxter" wrote:

I have a template that I'm trying to develop for daily use in my
office. The template is a tool to help my supervisors check the work
for the next day. I work for a service organization that covers a 3
state area.....I have routers that plan the day for the techs but I
need a fast tool for my supervisors to be able to be the safety net if
you will for them. I can have them import the routes into map point
but the problem is now it is very manual and time consuming.....I
would to create a code that would take the list and separate each
route into its own worksheet in the workbook.....I just have no idea
where to start. Any ideas???


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Separating a List onto multiple worksheets in the same workbook ba
On Apr 17, 8:06 pm, Barb Reinhardt
wrote:
Let's say you have a worksheet with the following information on it.

Col A Col B
Route # Stop #

And your data is stored in rows 2 - N

Try something like this:

Sub SPlitRoutes()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim newWS As Worksheet
Dim i As Integer
Dim lrow As Integer

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet

For i = 2 To aWS.Cells(Rows.Count, 1).End(xlUp).Row
Set newWS = Nothing
On Error Resume Next
Set newWS = aWB.Worksheets(aWS.Cells(i, 1))
On Error GoTo 0
If newWS Is Nothing Then
Debug.Print aWS.Cells(i, 1).Value
Set newWS = Worksheets.Add(After:=Worksheets(aWB.Worksheets.Co unt))
newWS.Name = aWS.Cells(i, 1).Value
newWS.Cells(1, 1).Value = aWS.Cells(1, 1).Value
newWS.Cells(1, 2).Value = aWS.Cells(1, 2).Value
newWS.Cells(2, 1).Value = aWS.Cells(i, 1).Value
newWS.Cells(2, 2).Value = aWS.Cells(i, 2).Value
Else
lrow = newWS.Cells(Rows.Count, 1).End(xlUp).Row
newWS.Cells(lrow + 1, 1) = aWS.Cells(i, 1)
newWS.Cells(lrow + 1, 2) = aWS.Cells(i, 2)
End If

Next i
End Sub

"DMRbaxter" wrote:
I have a template that I'm trying to develop for daily use in my
office. The template is a tool to help my supervisors check the work
for the next day. I work for a service organization that covers a 3
state area.....I have routers that plan the day for the techs but I
need a fast tool for my supervisors to be able to be the safety net if
you will for them. I can have them import the routes into map point
but the problem is now it is very manual and time consuming.....I
would to create a code that would take the list and separate each
route into its own worksheet in the workbook.....I just have no idea
where to start. Any ideas???

So here are the correct columns:

AQ AR
IND-1234-FSS1 1
IND-1234-FSS1 2
IND-1234-FSS1 3
IND-1234-FSS1 4
IND-4321-FSS1 1
IND-4321-FSS1 2
IND-4321-FSS1 3
FTW-4321-FSS1 1
FTW-4321-FSS1 2
FTW-4321-FSS1 3
FTW-4321-FSS1 4
FTW-4321-FSS1 5

How would the formula change, you have to excuse me I'm new at this?


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Separating a List onto multiple worksheets in the same workbook ba
On Apr 17, 8:28 pm, DMRbaxter wrote:
On Apr 17, 8:06 pm, Barb Reinhardt



wrote:
Let's say you have a worksheet with the following information on it.

Col A Col B
Route # Stop #

And your data is stored in rows 2 - N

Try something like this:

Sub SPlitRoutes()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim newWS As Worksheet
Dim i As Integer
Dim lrow As Integer

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet

For i = 2 To aWS.Cells(Rows.Count, 1).End(xlUp).Row
Set newWS = Nothing
On Error Resume Next
Set newWS = aWB.Worksheets(aWS.Cells(i, 1))
On Error GoTo 0
If newWS Is Nothing Then
Debug.Print aWS.Cells(i, 1).Value
Set newWS = Worksheets.Add(After:=Worksheets(aWB.Worksheets.Co unt))
newWS.Name = aWS.Cells(i, 1).Value
newWS.Cells(1, 1).Value = aWS.Cells(1, 1).Value
newWS.Cells(1, 2).Value = aWS.Cells(1, 2).Value
newWS.Cells(2, 1).Value = aWS.Cells(i, 1).Value
newWS.Cells(2, 2).Value = aWS.Cells(i, 2).Value
Else
lrow = newWS.Cells(Rows.Count, 1).End(xlUp).Row
newWS.Cells(lrow + 1, 1) = aWS.Cells(i, 1)
newWS.Cells(lrow + 1, 2) = aWS.Cells(i, 2)
End If

Next i
End Sub

"DMRbaxter" wrote:
I have a template that I'm trying to develop for daily use in my
office. The template is a tool to help my supervisors check the work
for the next day. I work for a service organization that covers a 3
state area.....I have routers that plan the day for the techs but I
need a fast tool for my supervisors to be able to be the safety net if
you will for them. I can have them import the routes into map point
but the problem is now it is very manual and time consuming.....I
would to create a code that would take the list and separate each
route into its own worksheet in the workbook.....I just have no idea
where to start. Any ideas???

So here are the correct columns:

AQ AR
IND-1234-FSS1 1
IND-1234-FSS1 2
IND-1234-FSS1 3
IND-1234-FSS1 4
IND-4321-FSS1 1
IND-4321-FSS1 2
IND-4321-FSS1 3
FTW-4321-FSS1 1
FTW-4321-FSS1 2
FTW-4321-FSS1 3
FTW-4321-FSS1 4
FTW-4321-FSS1 5

How would the formula change, you have to excuse me I'm new at this?

I also need it to copy the entire row into the new worksheets not just
the 2 cells......also thanks for your help so far

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,355
Default Separating a List onto multiple worksheets in the same workboo
Any line that shows
aws.cells(something,1) should change to aws.cells(something,"AQ")
aws.cells(something,2) should change to aws.cells(something,"AR")

"DMRbaxter" wrote:

On Apr 17, 8:06 pm, Barb Reinhardt
wrote:
Let's say you have a worksheet with the following information on it.

Col A Col B
Route # Stop #

And your data is stored in rows 2 - N

Try something like this:

Sub SPlitRoutes()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim newWS As Worksheet
Dim i As Integer
Dim lrow As Integer

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet

For i = 2 To aWS.Cells(Rows.Count, 1).End(xlUp).Row
Set newWS = Nothing
On Error Resume Next
Set newWS = aWB.Worksheets(aWS.Cells(i, 1))
On Error GoTo 0
If newWS Is Nothing Then
Debug.Print aWS.Cells(i, 1).Value
Set newWS = Worksheets.Add(After:=Worksheets(aWB.Worksheets.Co unt))
newWS.Name = aWS.Cells(i, 1).Value
newWS.Cells(1, 1).Value = aWS.Cells(1, 1).Value
newWS.Cells(1, 2).Value = aWS.Cells(1, 2).Value
newWS.Cells(2, 1).Value = aWS.Cells(i, 1).Value
newWS.Cells(2, 2).Value = aWS.Cells(i, 2).Value
Else
lrow = newWS.Cells(Rows.Count, 1).End(xlUp).Row
newWS.Cells(lrow + 1, 1) = aWS.Cells(i, 1)
newWS.Cells(lrow + 1, 2) = aWS.Cells(i, 2)
End If

Next i
End Sub

"DMRbaxter" wrote:
I have a template that I'm trying to develop for daily use in my
office. The template is a tool to help my supervisors check the work
for the next day. I work for a service organization that covers a 3
state area.....I have routers that plan the day for the techs but I
need a fast tool for my supervisors to be able to be the safety net if
you will for them. I can have them import the routes into map point
but the problem is now it is very manual and time consuming.....I
would to create a code that would take the list and separate each
route into its own worksheet in the workbook.....I just have no idea
where to start. Any ideas???

So here are the correct columns:

AQ AR
IND-1234-FSS1 1
IND-1234-FSS1 2
IND-1234-FSS1 3
IND-1234-FSS1 4
IND-4321-FSS1 1
IND-4321-FSS1 2
IND-4321-FSS1 3
FTW-4321-FSS1 1
FTW-4321-FSS1 2
FTW-4321-FSS1 3
FTW-4321-FSS1 4
FTW-4321-FSS1 5

How would the formula change, you have to excuse me I'm new at this?



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Separating a List onto multiple worksheets in the same workboo
On Apr 18, 8:08 am, Barb Reinhardt
wrote:
Any line that shows
aws.cells(something,1) should change to aws.cells(something,"AQ")
aws.cells(something,2) should change to aws.cells(something,"AR")

"DMRbaxter" wrote:
On Apr 17, 8:06 pm, Barb Reinhardt
wrote:
Let's say you have a worksheet with the following information on it.

Col A Col B
Route # Stop #

And your data is stored in rows 2 - N

Try something like this:

Sub SPlitRoutes()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim newWS As Worksheet
Dim i As Integer
Dim lrow As Integer

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet

For i = 2 To aWS.Cells(Rows.Count, 1).End(xlUp).Row
Set newWS = Nothing
On Error Resume Next
Set newWS = aWB.Worksheets(aWS.Cells(i, 1))
On Error GoTo 0
If newWS Is Nothing Then
Debug.Print aWS.Cells(i, 1).Value
Set newWS = Worksheets.Add(After:=Worksheets(aWB.Worksheets.Co unt))
newWS.Name = aWS.Cells(i, 1).Value
newWS.Cells(1, 1).Value = aWS.Cells(1, 1).Value
newWS.Cells(1, 2).Value = aWS.Cells(1, 2).Value
newWS.Cells(2, 1).Value = aWS.Cells(i, 1).Value
newWS.Cells(2, 2).Value = aWS.Cells(i, 2).Value
Else
lrow = newWS.Cells(Rows.Count, 1).End(xlUp).Row
newWS.Cells(lrow + 1, 1) = aWS.Cells(i, 1)
newWS.Cells(lrow + 1, 2) = aWS.Cells(i, 2)
End If

Next i
End Sub

"DMRbaxter" wrote:
I have a template that I'm trying to develop for daily use in my
office. The template is a tool to help my supervisors check the work
for the next day. I work for a service organization that covers a 3
state area.....I have routers that plan the day for the techs but I
need a fast tool for my supervisors to be able to be the safety net if
you will for them. I can have them import the routes into map point
but the problem is now it is very manual and time consuming.....I
would to create a code that would take the list and separate each
route into its own worksheet in the workbook.....I just have no idea
where to start. Any ideas???

So here are the correct columns:

AQ AR
IND-1234-FSS1 1
IND-1234-FSS1 2
IND-1234-FSS1 3
IND-1234-FSS1 4
IND-4321-FSS1 1
IND-4321-FSS1 2
IND-4321-FSS1 3
FTW-4321-FSS1 1
FTW-4321-FSS1 2
FTW-4321-FSS1 3
FTW-4321-FSS1 4
FTW-4321-FSS1 5

How would the formula change, you have to excuse me I'm new at this?

So is there a way to make it copy all the rows that match column AQ so
I would need it to copy A-BR that into the new sheet

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,355
Default Separating a List onto multiple worksheets in the same workboo
Change the whole if/then as follows:

If newWS Is Nothing Then
Set newWS = Worksheets.Add(After:=Worksheets(aWB.Worksheets.Co unt))
newWS.Name = aWS.Cells(i, "AQ").Value
newWS.rows(1).Value = aWS.rows(1).Value
newWS.rows(2).Value = aWS.rows(i).Value
Else
lrow = newWS.Cells(Rows.Count, 1).End(xlUp).Row
newWS.rows(lrow + 1).value = aWS.rows(i).value
End If


"DMRbaxter" wrote:

On Apr 18, 8:08 am, Barb Reinhardt
wrote:
Any line that shows
aws.cells(something,1) should change to aws.cells(something,"AQ")
aws.cells(something,2) should change to aws.cells(something,"AR")

"DMRbaxter" wrote:
On Apr 17, 8:06 pm, Barb Reinhardt
wrote:
Let's say you have a worksheet with the following information on it.

Col A Col B
Route # Stop #

And your data is stored in rows 2 - N

Try something like this:

Sub SPlitRoutes()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim newWS As Worksheet
Dim i As Integer
Dim lrow As Integer

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet

For i = 2 To aWS.Cells(Rows.Count, 1).End(xlUp).Row
Set newWS = Nothing
On Error Resume Next
Set newWS = aWB.Worksheets(aWS.Cells(i, 1))
On Error GoTo 0
If newWS Is Nothing Then
Debug.Print aWS.Cells(i, 1).Value
Set newWS = Worksheets.Add(After:=Worksheets(aWB.Worksheets.Co unt))
newWS.Name = aWS.Cells(i, 1).Value
newWS.Cells(1, 1).Value = aWS.Cells(1, 1).Value
newWS.Cells(1, 2).Value = aWS.Cells(1, 2).Value
newWS.Cells(2, 1).Value = aWS.Cells(i, 1).Value
newWS.Cells(2, 2).Value = aWS.Cells(i, 2).Value
Else
lrow = newWS.Cells(Rows.Count, 1).End(xlUp).Row
newWS.Cells(lrow + 1, 1) = aWS.Cells(i, 1)
newWS.Cells(lrow + 1, 2) = aWS.Cells(i, 2)
End If

Next i
End Sub

"DMRbaxter" wrote:
I have a template that I'm trying to develop for daily use in my
office. The template is a tool to help my supervisors check the work
for the next day. I work for a service organization that covers a 3
state area.....I have routers that plan the day for the techs but I
need a fast tool for my supervisors to be able to be the safety net if
you will for them. I can have them import the routes into map point
but the problem is now it is very manual and time consuming.....I
would to create a code that would take the list and separate each
route into its own worksheet in the workbook.....I just have no idea
where to start. Any ideas???

So here are the correct columns:

AQ AR
IND-1234-FSS1 1
IND-1234-FSS1 2
IND-1234-FSS1 3
IND-1234-FSS1 4
IND-4321-FSS1 1
IND-4321-FSS1 2
IND-4321-FSS1 3
FTW-4321-FSS1 1
FTW-4321-FSS1 2
FTW-4321-FSS1 3
FTW-4321-FSS1 4
FTW-4321-FSS1 5

How would the formula change, you have to excuse me I'm new at this?

So is there a way to make it copy all the rows that match column AQ so
I would need it to copy A-BR that into the new sheet


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
importing and updating list based on criteria BlackDN Excel Discussion (Misc queries) 0 March 7th 07 09:34 PM
Return value from list based on criteria [email protected] Excel Worksheet Functions 2 October 3rd 06 10:44 AM
sum based on multiple criteria Todd Excel Worksheet Functions 3 May 5th 06 10:06 PM
add to a cell on a list based on two criteria gabrielinlompoc New Users to Excel 0 February 9th 06 10:07 PM
Multiple Sum, based on criteria Andy the yeti Excel Worksheet Functions 4 December 22nd 05 02:32 PM


All times are GMT +1. The time now is 10:28 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement