Stay with me, not a very good techical writer, will try to be clear.

I get a daily Excel spreadsheet with multiple columns.
Would like to set up a new workbook with the master sheet where I just add
the dailys into, plus have 5 sheets where the rows will be exported depending
on the names in column A, only 5 names.

So, I would like a workbook with 6 sheets, one sheet is The Master (Which is
updated daily) & 5 where the rows will be exported to from the master
depending on the name in column A.

If you need me to be more clear, let me know I will do my best. Also, the
clearer your answer the better, I sort of got thrown into this task & I have
no clue how to use Excel, learning as I go along.

Thanks.

First question(s): Do the other 5 sheets already have names on them that
appear (or will appear) on the master sheet? Or are you supposed to move new
names that appear on the master sheet (but don't exist on the others) onto
those sheets?

I am thinking that since you said only 5 names, and you have 5 other sheets,
that there is 1 name on each of the other sheets, and that all entries on the
Master sheet will have one of those 5 names associated with it.

Next question: when you get a match on a name and want to move the entry
from the master sheet to the appropriate sheet, does the data get placed in a
new row at the end of existing information on those sheets?

This is going to take a bit of VBA coding (a macro) and it will help if you
have a column on the Master sheet that the code can use to put a simple
marker, like an "X" into it to indicate that the information on that row has
already been moved or processed, unless there is some other way we can
clearly tell whether a should be copied without having to compare every row
of information on every sheet each time the process is run.


"Andrew C" wrote:

Stay with me, not a very good techical writer, will try to be clear.

I get a daily Excel spreadsheet with multiple columns.
Would like to set up a new workbook with the master sheet where I just add
the dailys into, plus have 5 sheets where the rows will be exported depending
on the names in column A, only 5 names.

So, I would like a workbook with 6 sheets, one sheet is The Master (Which is
updated daily) & 5 where the rows will be exported to from the master
depending on the name in column A.

If you need me to be more clear, let me know I will do my best. Also, the
clearer your answer the better, I sort of got thrown into this task & I have
no clue how to use Excel, learning as I go along.

Thanks.

Yes, there will only be a possibility of 5 names in colomn A, with each of
the other 5 sheets assigned one of the 5 names.

And yes, i would like the data to be placed at the end of the previously
entry on each of the sheets.

thanks,
Drew

"JLatham" wrote:

First question(s): Do the other 5 sheets already have names on them that
appear (or will appear) on the master sheet? Or are you supposed to move new
names that appear on the master sheet (but don't exist on the others) onto
those sheets?

I am thinking that since you said only 5 names, and you have 5 other sheets,
that there is 1 name on each of the other sheets, and that all entries on the
Master sheet will have one of those 5 names associated with it.

Next question: when you get a match on a name and want to move the entry
from the master sheet to the appropriate sheet, does the data get placed in a
new row at the end of existing information on those sheets?

This is going to take a bit of VBA coding (a macro) and it will help if you
have a column on the Master sheet that the code can use to put a simple
marker, like an "X" into it to indicate that the information on that row has
already been moved or processed, unless there is some other way we can
clearly tell whether a should be copied without having to compare every row
of information on every sheet each time the process is run.


"Andrew C" wrote:

Stay with me, not a very good techical writer, will try to be clear.

I get a daily Excel spreadsheet with multiple columns.
Would like to set up a new workbook with the master sheet where I just add
the dailys into, plus have 5 sheets where the rows will be exported depending
on the names in column A, only 5 names.

So, I would like a workbook with 6 sheets, one sheet is The Master (Which is
updated daily) & 5 where the rows will be exported to from the master
depending on the name in column A.

If you need me to be more clear, let me know I will do my best. Also, the
clearer your answer the better, I sort of got thrown into this task & I have
no clue how to use Excel, learning as I go along.

Thanks.

This solution depends on some assumptions. Biggest one is that the names are
always spelled the same way. Second one is that they names are in column A
on all sheets (that can be changed in the code).

Third assumption is that the master sheet has a header row, and that the
data you need to possibly move to other sheets starts on row 2. That can
also be changed in the code.

Rather than use a helper column to mark the entries that have been
processed, I add the "~" (tilde) character to the beginning of names on the
Master sheet as they are processed. We could use a helper column if that
would be better for you, I'd just need to know a column ID (as A, B, Z, AB,
etc) that is available to be used for that.

How to get the code into your workook and test it all:
Make a copy of your workbook and use it for the testing initially. Since
the code will think none of the data has been processed, go to each of the 5
sheets and delete all information you now have on them that came from the
Master sheet.

To put the code into the workbook (the copy, remember?): Open that workbook
and press [Alt]+[F11] to open the VB Editor. In it, choose Insert | Module
from its menu and copy the code below and paste it into the empty module
presented to you. Make any edits to the Const values that you need to.
Close the VB Editor.

Save the workbook to save this 'empty' copy with the code in it. To use the
code, select Tools | Macro | Macros from the Excel menu bar and highlight the
CopyNewMasterData entry and click the [Run] button. Should work fairly
quickly.

Here's the code:

Sub CopyNewMasterData()
Const masterSheetName = "Master" ' change as needed
Const nameColumn = "A" ' change if needed
Const firstUsedRow = 2 'first row on Master sheet with data
Const markDoneCharacter = "~"

Dim masterSheet As Worksheet
Dim masterNameRange As Range
Dim anyMasterName As Range
Dim anySheet As Worksheet

Set masterSheet = ThisWorkbook.Worksheets(masterSheetName)
Set masterNameRange = masterSheet.Range(nameColumn & firstUsedRow _
& ":" & masterSheet.Range(nameColumn & Rows.Count).End(xlUp).Address)
'now test each name in the list on the Master sheet
'to see if it:
' 1) starts with ~ (if it does, ignore it, has been processed)
' 2) if #1 fails, find proper sheet based on name being last
' used cell in column A on any of those sheets
For Each anyMasterName In masterNameRange
If Not IsEmpty(anyMasterName) Then
If Left(anyMasterName, 1) < markDoneCharacter Then
'new data, add to a sheet
For Each anySheet In ThisWorkbook.Worksheets
'don't look on the Master sheet itself
If anySheet.Name < masterSheetName Then
If UCase(Trim(anySheet.Range(nameColumn & _
Rows.Count).End(xlUp))) = _
UCase(Trim(anyMasterName)) Then
'it needs to be put on this sheet at bottom of the list
anyMasterName.EntireRow.Copy
anySheet.Range(nameColumn & Rows.Count).End(xlUp). _
Offset(1, 0).PasteSpecial xlPasteAll
Application.CutCopyMode = False
'mark name on Master sheet as having been processed
anyMasterName = markDoneCharacter & anyMasterName
' exit anySheet loop to look at next name on master sheet
Exit For
End If
End If
Next ' end of anySheet loop
End If
End If ' end of test for empty cell on Master sheet
Next
'house cleaning
Set masterNameRange = Nothing
Set masterSheet = Nothing
End Sub


"Andrew C" wrote:

Yes, there will only be a possibility of 5 names in colomn A, with each of
the other 5 sheets assigned one of the 5 names.

And yes, i would like the data to be placed at the end of the previously
entry on each of the sheets.

thanks,
Drew

"JLatham" wrote:

First question(s): Do the other 5 sheets already have names on them that
appear (or will appear) on the master sheet? Or are you supposed to move new
names that appear on the master sheet (but don't exist on the others) onto
those sheets?

I am thinking that since you said only 5 names, and you have 5 other sheets,
that there is 1 name on each of the other sheets, and that all entries on the
Master sheet will have one of those 5 names associated with it.

Next question: when you get a match on a name and want to move the entry
from the master sheet to the appropriate sheet, does the data get placed in a
new row at the end of existing information on those sheets?

This is going to take a bit of VBA coding (a macro) and it will help if you
have a column on the Master sheet that the code can use to put a simple
marker, like an "X" into it to indicate that the information on that row has
already been moved or processed, unless there is some other way we can
clearly tell whether a should be copied without having to compare every row
of information on every sheet each time the process is run.


"Andrew C" wrote:

Stay with me, not a very good techical writer, will try to be clear.

I get a daily Excel spreadsheet with multiple columns.
Would like to set up a new workbook with the master sheet where I just add
the dailys into, plus have 5 sheets where the rows will be exported depending
on the names in column A, only 5 names.

So, I would like a workbook with 6 sheets, one sheet is The Master (Which is
updated daily) & 5 where the rows will be exported to from the master
depending on the name in column A.

If you need me to be more clear, let me know I will do my best. Also, the
clearer your answer the better, I sort of got thrown into this task & I have
no clue how to use Excel, learning as I go along.

Thanks.

Your assumptions were correct, thank you so much. Currently not working, but
I'm going to try and figure it out before asking for more help.

Give a man a fish.... Blah blah blah

"JLatham" wrote:

This solution depends on some assumptions. Biggest one is that the names are
always spelled the same way. Second one is that they names are in column A
on all sheets (that can be changed in the code).

Third assumption is that the master sheet has a header row, and that the
data you need to possibly move to other sheets starts on row 2. That can
also be changed in the code.

Rather than use a helper column to mark the entries that have been
processed, I add the "~" (tilde) character to the beginning of names on the
Master sheet as they are processed. We could use a helper column if that
would be better for you, I'd just need to know a column ID (as A, B, Z, AB,
etc) that is available to be used for that.

How to get the code into your workook and test it all:
Make a copy of your workbook and use it for the testing initially. Since
the code will think none of the data has been processed, go to each of the 5
sheets and delete all information you now have on them that came from the
Master sheet.

To put the code into the workbook (the copy, remember?): Open that workbook
and press [Alt]+[F11] to open the VB Editor. In it, choose Insert | Module
from its menu and copy the code below and paste it into the empty module
presented to you. Make any edits to the Const values that you need to.
Close the VB Editor.

Save the workbook to save this 'empty' copy with the code in it. To use the
code, select Tools | Macro | Macros from the Excel menu bar and highlight the
CopyNewMasterData entry and click the [Run] button. Should work fairly
quickly.

Here's the code:

Sub CopyNewMasterData()
Const masterSheetName = "Master" ' change as needed
Const nameColumn = "A" ' change if needed
Const firstUsedRow = 2 'first row on Master sheet with data
Const markDoneCharacter = "~"

Dim masterSheet As Worksheet
Dim masterNameRange As Range
Dim anyMasterName As Range
Dim anySheet As Worksheet

Set masterSheet = ThisWorkbook.Worksheets(masterSheetName)
Set masterNameRange = masterSheet.Range(nameColumn & firstUsedRow _
& ":" & masterSheet.Range(nameColumn & Rows.Count).End(xlUp).Address)
'now test each name in the list on the Master sheet
'to see if it:
' 1) starts with ~ (if it does, ignore it, has been processed)
' 2) if #1 fails, find proper sheet based on name being last
' used cell in column A on any of those sheets
For Each anyMasterName In masterNameRange
If Not IsEmpty(anyMasterName) Then
If Left(anyMasterName, 1) < markDoneCharacter Then
'new data, add to a sheet
For Each anySheet In ThisWorkbook.Worksheets
'don't look on the Master sheet itself
If anySheet.Name < masterSheetName Then
If UCase(Trim(anySheet.Range(nameColumn & _
Rows.Count).End(xlUp))) = _
UCase(Trim(anyMasterName)) Then
'it needs to be put on this sheet at bottom of the list
anyMasterName.EntireRow.Copy
anySheet.Range(nameColumn & Rows.Count).End(xlUp). _
Offset(1, 0).PasteSpecial xlPasteAll
Application.CutCopyMode = False
'mark name on Master sheet as having been processed
anyMasterName = markDoneCharacter & anyMasterName
' exit anySheet loop to look at next name on master sheet
Exit For
End If
End If
Next ' end of anySheet loop
End If
End If ' end of test for empty cell on Master sheet
Next
'house cleaning
Set masterNameRange = Nothing
Set masterSheet = Nothing
End Sub


"Andrew C" wrote:

Yes, there will only be a possibility of 5 names in colomn A, with each of
the other 5 sheets assigned one of the 5 names.

And yes, i would like the data to be placed at the end of the previously
entry on each of the sheets.

thanks,
Drew

"JLatham" wrote:

First question(s): Do the other 5 sheets already have names on them that
appear (or will appear) on the master sheet? Or are you supposed to move new
names that appear on the master sheet (but don't exist on the others) onto
those sheets?

I am thinking that since you said only 5 names, and you have 5 other sheets,
that there is 1 name on each of the other sheets, and that all entries on the
Master sheet will have one of those 5 names associated with it.

Next question: when you get a match on a name and want to move the entry
from the master sheet to the appropriate sheet, does the data get placed in a
new row at the end of existing information on those sheets?

This is going to take a bit of VBA coding (a macro) and it will help if you
have a column on the Master sheet that the code can use to put a simple
marker, like an "X" into it to indicate that the information on that row has
already been moved or processed, unless there is some other way we can
clearly tell whether a should be copied without having to compare every row
of information on every sheet each time the process is run.


"Andrew C" wrote:

Stay with me, not a very good techical writer, will try to be clear.

I get a daily Excel spreadsheet with multiple columns.
Would like to set up a new workbook with the master sheet where I just add
the dailys into, plus have 5 sheets where the rows will be exported depending
on the names in column A, only 5 names.

So, I would like a workbook with 6 sheets, one sheet is The Master (Which is
updated daily) & 5 where the rows will be exported to from the master
depending on the name in column A.

If you need me to be more clear, let me know I will do my best. Also, the
clearer your answer the better, I sort of got thrown into this task & I have
no clue how to use Excel, learning as I go along.

Thanks.

Feel free to get in touch with me directly, remove spaces:
Help From @JLathamsite .com
will get emails and files to me.

"Andrew C" wrote:

Your assumptions were correct, thank you so much. Currently not working, but
I'm going to try and figure it out before asking for more help.

Give a man a fish.... Blah blah blah

"JLatham" wrote:

This solution depends on some assumptions. Biggest one is that the names are
always spelled the same way. Second one is that they names are in column A
on all sheets (that can be changed in the code).

Third assumption is that the master sheet has a header row, and that the
data you need to possibly move to other sheets starts on row 2. That can
also be changed in the code.

Rather than use a helper column to mark the entries that have been
processed, I add the "~" (tilde) character to the beginning of names on the
Master sheet as they are processed. We could use a helper column if that
would be better for you, I'd just need to know a column ID (as A, B, Z, AB,
etc) that is available to be used for that.

How to get the code into your workook and test it all:
Make a copy of your workbook and use it for the testing initially. Since
the code will think none of the data has been processed, go to each of the 5
sheets and delete all information you now have on them that came from the
Master sheet.

To put the code into the workbook (the copy, remember?): Open that workbook
and press [Alt]+[F11] to open the VB Editor. In it, choose Insert | Module
from its menu and copy the code below and paste it into the empty module
presented to you. Make any edits to the Const values that you need to.
Close the VB Editor.

Save the workbook to save this 'empty' copy with the code in it. To use the
code, select Tools | Macro | Macros from the Excel menu bar and highlight the
CopyNewMasterData entry and click the [Run] button. Should work fairly
quickly.

Here's the code:

Sub CopyNewMasterData()
Const masterSheetName = "Master" ' change as needed
Const nameColumn = "A" ' change if needed
Const firstUsedRow = 2 'first row on Master sheet with data
Const markDoneCharacter = "~"

Dim masterSheet As Worksheet
Dim masterNameRange As Range
Dim anyMasterName As Range
Dim anySheet As Worksheet

Set masterSheet = ThisWorkbook.Worksheets(masterSheetName)
Set masterNameRange = masterSheet.Range(nameColumn & firstUsedRow _
& ":" & masterSheet.Range(nameColumn & Rows.Count).End(xlUp).Address)
'now test each name in the list on the Master sheet
'to see if it:
' 1) starts with ~ (if it does, ignore it, has been processed)
' 2) if #1 fails, find proper sheet based on name being last
' used cell in column A on any of those sheets
For Each anyMasterName In masterNameRange
If Not IsEmpty(anyMasterName) Then
If Left(anyMasterName, 1) < markDoneCharacter Then
'new data, add to a sheet
For Each anySheet In ThisWorkbook.Worksheets
'don't look on the Master sheet itself
If anySheet.Name < masterSheetName Then
If UCase(Trim(anySheet.Range(nameColumn & _
Rows.Count).End(xlUp))) = _
UCase(Trim(anyMasterName)) Then
'it needs to be put on this sheet at bottom of the list
anyMasterName.EntireRow.Copy
anySheet.Range(nameColumn & Rows.Count).End(xlUp). _
Offset(1, 0).PasteSpecial xlPasteAll
Application.CutCopyMode = False
'mark name on Master sheet as having been processed
anyMasterName = markDoneCharacter & anyMasterName
' exit anySheet loop to look at next name on master sheet
Exit For
End If
End If
Next ' end of anySheet loop
End If
End If ' end of test for empty cell on Master sheet
Next
'house cleaning
Set masterNameRange = Nothing
Set masterSheet = Nothing
End Sub


"Andrew C" wrote:

Yes, there will only be a possibility of 5 names in colomn A, with each of
the other 5 sheets assigned one of the 5 names.

And yes, i would like the data to be placed at the end of the previously
entry on each of the sheets.

thanks,
Drew

"JLatham" wrote:

First question(s): Do the other 5 sheets already have names on them that
appear (or will appear) on the master sheet? Or are you supposed to move new
names that appear on the master sheet (but don't exist on the others) onto
those sheets?

I am thinking that since you said only 5 names, and you have 5 other sheets,
that there is 1 name on each of the other sheets, and that all entries on the
Master sheet will have one of those 5 names associated with it.

Next question: when you get a match on a name and want to move the entry
from the master sheet to the appropriate sheet, does the data get placed in a
new row at the end of existing information on those sheets?

This is going to take a bit of VBA coding (a macro) and it will help if you
have a column on the Master sheet that the code can use to put a simple
marker, like an "X" into it to indicate that the information on that row has
already been moved or processed, unless there is some other way we can
clearly tell whether a should be copied without having to compare every row
of information on every sheet each time the process is run.


"Andrew C" wrote:

Stay with me, not a very good techical writer, will try to be clear.

I get a daily Excel spreadsheet with multiple columns.
Would like to set up a new workbook with the master sheet where I just add
the dailys into, plus have 5 sheets where the rows will be exported depending
on the names in column A, only 5 names.

So, I would like a workbook with 6 sheets, one sheet is The Master (Which is
updated daily) & 5 where the rows will be exported to from the master
depending on the name in column A.

If you need me to be more clear, let me know I will do my best. Also, the
clearer your answer the better, I sort of got thrown into this task & I have
no clue how to use Excel, learning as I go along.

Thanks.

My instructions for testing in new workbook may be the reason it's not
working for you. Try putting the person's name in the first empty cell in
column A of each of the other 5 sheets. It needs a "seed" name there to get
started. With me telling you to delete existing data below any headers, we
removed that 'seed'.

"Andrew C" wrote:

Your assumptions were correct, thank you so much. Currently not working, but
I'm going to try and figure it out before asking for more help.

Give a man a fish.... Blah blah blah

"JLatham" wrote:

This solution depends on some assumptions. Biggest one is that the names are
always spelled the same way. Second one is that they names are in column A
on all sheets (that can be changed in the code).

Third assumption is that the master sheet has a header row, and that the
data you need to possibly move to other sheets starts on row 2. That can
also be changed in the code.

Rather than use a helper column to mark the entries that have been
processed, I add the "~" (tilde) character to the beginning of names on the
Master sheet as they are processed. We could use a helper column if that
would be better for you, I'd just need to know a column ID (as A, B, Z, AB,
etc) that is available to be used for that.

How to get the code into your workook and test it all:
Make a copy of your workbook and use it for the testing initially. Since
the code will think none of the data has been processed, go to each of the 5
sheets and delete all information you now have on them that came from the
Master sheet.

To put the code into the workbook (the copy, remember?): Open that workbook
and press [Alt]+[F11] to open the VB Editor. In it, choose Insert | Module
from its menu and copy the code below and paste it into the empty module
presented to you. Make any edits to the Const values that you need to.
Close the VB Editor.

Save the workbook to save this 'empty' copy with the code in it. To use the
code, select Tools | Macro | Macros from the Excel menu bar and highlight the
CopyNewMasterData entry and click the [Run] button. Should work fairly
quickly.

Here's the code:

Sub CopyNewMasterData()
Const masterSheetName = "Master" ' change as needed
Const nameColumn = "A" ' change if needed
Const firstUsedRow = 2 'first row on Master sheet with data
Const markDoneCharacter = "~"

Dim masterSheet As Worksheet
Dim masterNameRange As Range
Dim anyMasterName As Range
Dim anySheet As Worksheet

Set masterSheet = ThisWorkbook.Worksheets(masterSheetName)
Set masterNameRange = masterSheet.Range(nameColumn & firstUsedRow _
& ":" & masterSheet.Range(nameColumn & Rows.Count).End(xlUp).Address)
'now test each name in the list on the Master sheet
'to see if it:
' 1) starts with ~ (if it does, ignore it, has been processed)
' 2) if #1 fails, find proper sheet based on name being last
' used cell in column A on any of those sheets
For Each anyMasterName In masterNameRange
If Not IsEmpty(anyMasterName) Then
If Left(anyMasterName, 1) < markDoneCharacter Then
'new data, add to a sheet
For Each anySheet In ThisWorkbook.Worksheets
'don't look on the Master sheet itself
If anySheet.Name < masterSheetName Then
If UCase(Trim(anySheet.Range(nameColumn & _
Rows.Count).End(xlUp))) = _
UCase(Trim(anyMasterName)) Then
'it needs to be put on this sheet at bottom of the list
anyMasterName.EntireRow.Copy
anySheet.Range(nameColumn & Rows.Count).End(xlUp). _
Offset(1, 0).PasteSpecial xlPasteAll
Application.CutCopyMode = False
'mark name on Master sheet as having been processed
anyMasterName = markDoneCharacter & anyMasterName
' exit anySheet loop to look at next name on master sheet
Exit For
End If
End If
Next ' end of anySheet loop
End If
End If ' end of test for empty cell on Master sheet
Next
'house cleaning
Set masterNameRange = Nothing
Set masterSheet = Nothing
End Sub


"Andrew C" wrote:

Yes, there will only be a possibility of 5 names in colomn A, with each of
the other 5 sheets assigned one of the 5 names.

And yes, i would like the data to be placed at the end of the previously
entry on each of the sheets.

thanks,
Drew

"JLatham" wrote:

First question(s): Do the other 5 sheets already have names on them that
appear (or will appear) on the master sheet? Or are you supposed to move new
names that appear on the master sheet (but don't exist on the others) onto
those sheets?

I am thinking that since you said only 5 names, and you have 5 other sheets,
that there is 1 name on each of the other sheets, and that all entries on the
Master sheet will have one of those 5 names associated with it.

Next question: when you get a match on a name and want to move the entry
from the master sheet to the appropriate sheet, does the data get placed in a
new row at the end of existing information on those sheets?

This is going to take a bit of VBA coding (a macro) and it will help if you
have a column on the Master sheet that the code can use to put a simple
marker, like an "X" into it to indicate that the information on that row has
already been moved or processed, unless there is some other way we can
clearly tell whether a should be copied without having to compare every row
of information on every sheet each time the process is run.


"Andrew C" wrote:

Stay with me, not a very good techical writer, will try to be clear.

I get a daily Excel spreadsheet with multiple columns.
Would like to set up a new workbook with the master sheet where I just add
the dailys into, plus have 5 sheets where the rows will be exported depending
on the names in column A, only 5 names.

So, I would like a workbook with 6 sheets, one sheet is The Master (Which is
updated daily) & 5 where the rows will be exported to from the master
depending on the name in column A.

If you need me to be more clear, let me know I will do my best. Also, the
clearer your answer the better, I sort of got thrown into this task & I have
no clue how to use Excel, learning as I go along.

Thanks.