I have 5 pipeline reports from differnt sales people and want to combine it
into one master pipeline report. i also need it to update when a sales
person updates their individual work shhet

Not enough info. What is a Pipeline Report? What are the rows and columns?
Do you want a macro or to do it manually?

See other postings that have answers befor eyou reply. If you want a macro
then look at the Programming Postings, not the general listings.

When people don't get responses it is usually because that didn't give
enough information. Sometimes it is because there isn't an answer. Your
problem does have an answer.

"bobo32" wrote:

I have 5 pipeline reports from differnt sales people and want to combine it
into one master pipeline report. i also need it to update when a sales
person updates their individual work shhet

The heading a
Customer, CustomerContact, Sales Person, Date, PipelineStage, Forecast
Close, Potential Opportunity, Weighted,Forecast, Chance of Sale,
InternalCalculation, Internal Calculation.
Rows:
Customer names.

I want each sales person to manually update their spreadsheet and when I
open the master spreadsheet I want it to be automatically updated,
I hope this is enough info if not let me knoe.
Thanks in advance,
bo

"Joel" wrote:

Not enough info. What is a Pipeline Report? What are the rows and columns?
Do you want a macro or to do it manually?

See other postings that have answers befor eyou reply. If you want a macro
then look at the Programming Postings, not the general listings.

When people don't get responses it is usually because that didn't give
enough information. Sometimes it is because there isn't an answer. Your
problem does have an answer.

"bobo32" wrote:

I have 5 pipeline reports from differnt sales people and want to combine it
into one master pipeline report. i also need it to update when a sales
person updates their individual work shhet

My assumption is that each sales person has there own customers and there
else no duplication of customers. there is a header row for the summary
sheet and fon each salesperson sheet. I also assume that each saleperson may
add new customers as well as change existing customers.

The best way of doing this is simply redo the summary sheet everytime you
want it updated. This will take only a few seconds. Is ther any order that
you want to summary sheet generated. sorted by customers or sorted by
saleperson. I also think that you should run the macro manually rather than
automatically updating.

Let me know if this is what you want. It is a very simple task that will
take a few minutes.



"bobo32" wrote:

The heading a
Customer, CustomerContact, Sales Person, Date, PipelineStage, Forecast
Close, Potential Opportunity, Weighted,Forecast, Chance of Sale,
InternalCalculation, Internal Calculation.
Rows:
Customer names.

I want each sales person to manually update their spreadsheet and when I
open the master spreadsheet I want it to be automatically updated,
I hope this is enough info if not let me knoe.
Thanks in advance,
bo

"Joel" wrote:

Not enough info. What is a Pipeline Report? What are the rows and columns?
Do you want a macro or to do it manually?

See other postings that have answers befor eyou reply. If you want a macro
then look at the Programming Postings, not the general listings.

When people don't get responses it is usually because that didn't give
enough information. Sometimes it is because there isn't an answer. Your
problem does have an answer.

"bobo32" wrote:

I have 5 pipeline reports from differnt sales people and want to combine it
into one master pipeline report. i also need it to update when a sales
person updates their individual work shhet

This is exactly what I want and I would want to sort it by salesperson.
Thanks,
Bo

"Joel" wrote:

My assumption is that each sales person has there own customers and there
else no duplication of customers. there is a header row for the summary
sheet and fon each salesperson sheet. I also assume that each saleperson may
add new customers as well as change existing customers.

The best way of doing this is simply redo the summary sheet everytime you
want it updated. This will take only a few seconds. Is ther any order that
you want to summary sheet generated. sorted by customers or sorted by
saleperson. I also think that you should run the macro manually rather than
automatically updating.

Let me know if this is what you want. It is a very simple task that will
take a few minutes.



"bobo32" wrote:

The heading a
Customer, CustomerContact, Sales Person, Date, PipelineStage, Forecast
Close, Potential Opportunity, Weighted,Forecast, Chance of Sale,
InternalCalculation, Internal Calculation.
Rows:
Customer names.

I want each sales person to manually update their spreadsheet and when I
open the master spreadsheet I want it to be automatically updated,
I hope this is enough info if not let me knoe.
Thanks in advance,
bo

"Joel" wrote:

Not enough info. What is a Pipeline Report? What are the rows and columns?
Do you want a macro or to do it manually?

See other postings that have answers befor eyou reply. If you want a macro
then look at the Programming Postings, not the general listings.

When people don't get responses it is usually because that didn't give
enough information. Sometimes it is because there isn't an answer. Your
problem does have an answer.

"bobo32" wrote:

I have 5 pipeline reports from differnt sales people and want to combine it
into one master pipeline report. i also need it to update when a sales
person updates their individual work shhet

I think sales personm column is C. If not change below as required. Also
make sure you have a worksheet called "Master Pipeline Report". change if
you are using a different name in two places in code below.

Sub test()

SalesPersonCol = "C"

'clear master sheet
Set Master = Sheets("Master Pipeline Report")
Master.Cells.ClearContents

'used to copy header row
First = True
For Each sht In ThisWorkbook.Sheets
If UCase(sht.Name) < UCase("Master Pipeline Report") Then
If First = True Then
sht.Rows(1).Copy Destination:=Master.Rows(1)
First = False
End If
ShtLastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row

sht.Rows("2:" & ShtLastRow).Copy _
Destination:=Master.Rows(MasterLastRow + 1)
End If
Next sht

MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Master.Rows("1:" & MasterLastRow)

SortRange.Sort _
Key1:=Range(SalesPersonCol & 2), _
Order1:=xlAscending, _
Header:=xlYes

End Sub


"bobo32" wrote:

This is exactly what I want and I would want to sort it by salesperson.
Thanks,
Bo

"Joel" wrote:

My assumption is that each sales person has there own customers and there
else no duplication of customers. there is a header row for the summary
sheet and fon each salesperson sheet. I also assume that each saleperson may
add new customers as well as change existing customers.

The best way of doing this is simply redo the summary sheet everytime you
want it updated. This will take only a few seconds. Is ther any order that
you want to summary sheet generated. sorted by customers or sorted by
saleperson. I also think that you should run the macro manually rather than
automatically updating.

Let me know if this is what you want. It is a very simple task that will
take a few minutes.



"bobo32" wrote:

The heading a
Customer, CustomerContact, Sales Person, Date, PipelineStage, Forecast
Close, Potential Opportunity, Weighted,Forecast, Chance of Sale,
InternalCalculation, Internal Calculation.
Rows:
Customer names.

I want each sales person to manually update their spreadsheet and when I
open the master spreadsheet I want it to be automatically updated,
I hope this is enough info if not let me knoe.
Thanks in advance,
bo

"Joel" wrote:

Not enough info. What is a Pipeline Report? What are the rows and columns?
Do you want a macro or to do it manually?

See other postings that have answers befor eyou reply. If you want a macro
then look at the Programming Postings, not the general listings.

When people don't get responses it is usually because that didn't give
enough information. Sometimes it is because there isn't an answer. Your
problem does have an answer.

"bobo32" wrote:

I have 5 pipeline reports from differnt sales people and want to combine it
into one master pipeline report. i also need it to update when a sales
person updates their individual work shhet

I am totally new to Excel, so if you could walk me through this in baby steps
I would greatly appriciate it.
thanks,
Bo

"Joel" wrote:

I think sales personm column is C. If not change below as required. Also
make sure you have a worksheet called "Master Pipeline Report". change if
you are using a different name in two places in code below.

Sub test()

SalesPersonCol = "C"

'clear master sheet
Set Master = Sheets("Master Pipeline Report")
Master.Cells.ClearContents

'used to copy header row
First = True
For Each sht In ThisWorkbook.Sheets
If UCase(sht.Name) < UCase("Master Pipeline Report") Then
If First = True Then
sht.Rows(1).Copy Destination:=Master.Rows(1)
First = False
End If
ShtLastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row

sht.Rows("2:" & ShtLastRow).Copy _
Destination:=Master.Rows(MasterLastRow + 1)
End If
Next sht

MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Master.Rows("1:" & MasterLastRow)

SortRange.Sort _
Key1:=Range(SalesPersonCol & 2), _
Order1:=xlAscending, _
Header:=xlYes

End Sub


"bobo32" wrote:

This is exactly what I want and I would want to sort it by salesperson.
Thanks,
Bo

"Joel" wrote:

My assumption is that each sales person has there own customers and there
else no duplication of customers. there is a header row for the summary
sheet and fon each salesperson sheet. I also assume that each saleperson may
add new customers as well as change existing customers.

The best way of doing this is simply redo the summary sheet everytime you
want it updated. This will take only a few seconds. Is ther any order that
you want to summary sheet generated. sorted by customers or sorted by
saleperson. I also think that you should run the macro manually rather than
automatically updating.

Let me know if this is what you want. It is a very simple task that will
take a few minutes.



"bobo32" wrote:

The heading a
Customer, CustomerContact, Sales Person, Date, PipelineStage, Forecast
Close, Potential Opportunity, Weighted,Forecast, Chance of Sale,
InternalCalculation, Internal Calculation.
Rows:
Customer names.

I want each sales person to manually update their spreadsheet and when I
open the master spreadsheet I want it to be automatically updated,
I hope this is enough info if not let me knoe.
Thanks in advance,
bo

"Joel" wrote:

Not enough info. What is a Pipeline Report? What are the rows and columns?
Do you want a macro or to do it manually?

See other postings that have answers befor eyou reply. If you want a macro
then look at the Programming Postings, not the general listings.

When people don't get responses it is usually because that didn't give
enough information. Sometimes it is because there isn't an answer. Your
problem does have an answer.

"bobo32" wrote:

I have 5 pipeline reports from differnt sales people and want to combine it
into one master pipeline report. i also need it to update when a sales
person updates their individual work shhet

To enter code into VBA

1) Copy code from posting by highlighting and copying. code starts with
"SUB" and ends with "End Sub"
2) OPen VBA window from worksheet by pressing ALT-F11
3) From VBA window menu - Insert - Module. This is Module 1 in the
Project Window on the right side of the woprksheet.
4) Paste Code into module window.
5) run code from VBA window by pressing F5 (first click on code, won't run
if cursor is outside the code. Run code from worksheet menu Tools - Macro -
Macro - Test. You can change the name of the code by changing "Sub Test" to
"sub Anything" in the first line of the VBA window in "Module 1"


"bobo32" wrote:

I am totally new to Excel, so if you could walk me through this in baby steps
I would greatly appriciate it.
thanks,
Bo

"Joel" wrote:

I think sales personm column is C. If not change below as required. Also
make sure you have a worksheet called "Master Pipeline Report". change if
you are using a different name in two places in code below.

Sub test()

SalesPersonCol = "C"

'clear master sheet
Set Master = Sheets("Master Pipeline Report")
Master.Cells.ClearContents

'used to copy header row
First = True
For Each sht In ThisWorkbook.Sheets
If UCase(sht.Name) < UCase("Master Pipeline Report") Then
If First = True Then
sht.Rows(1).Copy Destination:=Master.Rows(1)
First = False
End If
ShtLastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row

sht.Rows("2:" & ShtLastRow).Copy _
Destination:=Master.Rows(MasterLastRow + 1)
End If
Next sht

MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Master.Rows("1:" & MasterLastRow)

SortRange.Sort _
Key1:=Range(SalesPersonCol & 2), _
Order1:=xlAscending, _
Header:=xlYes

End Sub


"bobo32" wrote:

This is exactly what I want and I would want to sort it by salesperson.
Thanks,
Bo

"Joel" wrote:

My assumption is that each sales person has there own customers and there
else no duplication of customers. there is a header row for the summary
sheet and fon each salesperson sheet. I also assume that each saleperson may
add new customers as well as change existing customers.

The best way of doing this is simply redo the summary sheet everytime you
want it updated. This will take only a few seconds. Is ther any order that
you want to summary sheet generated. sorted by customers or sorted by
saleperson. I also think that you should run the macro manually rather than
automatically updating.

Let me know if this is what you want. It is a very simple task that will
take a few minutes.



"bobo32" wrote:

The heading a
Customer, CustomerContact, Sales Person, Date, PipelineStage, Forecast
Close, Potential Opportunity, Weighted,Forecast, Chance of Sale,
InternalCalculation, Internal Calculation.
Rows:
Customer names.

I want each sales person to manually update their spreadsheet and when I
open the master spreadsheet I want it to be automatically updated,
I hope this is enough info if not let me knoe.
Thanks in advance,
bo

"Joel" wrote:

Not enough info. What is a Pipeline Report? What are the rows and columns?
Do you want a macro or to do it manually?

See other postings that have answers befor eyou reply. If you want a macro
then look at the Programming Postings, not the general listings.

When people don't get responses it is usually because that didn't give
enough information. Sometimes it is because there isn't an answer. Your
problem does have an answer.

"bobo32" wrote:

I have 5 pipeline reports from differnt sales people and want to combine it
into one master pipeline report. i also need it to update when a sales
person updates their individual work shhet

What worksheet should i work from/

"Joel" wrote:

To enter code into VBA

1) Copy code from posting by highlighting and copying. code starts with
"SUB" and ends with "End Sub"
2) OPen VBA window from worksheet by pressing ALT-F11
3) From VBA window menu - Insert - Module. This is Module 1 in the
Project Window on the right side of the woprksheet.
4) Paste Code into module window.
5) run code from VBA window by pressing F5 (first click on code, won't run
if cursor is outside the code. Run code from worksheet menu Tools - Macro -
Macro - Test. You can change the name of the code by changing "Sub Test" to
"sub Anything" in the first line of the VBA window in "Module 1"


"bobo32" wrote:

I am totally new to Excel, so if you could walk me through this in baby steps
I would greatly appriciate it.
thanks,
Bo

"Joel" wrote:

I think sales personm column is C. If not change below as required. Also
make sure you have a worksheet called "Master Pipeline Report". change if
you are using a different name in two places in code below.

Sub test()

SalesPersonCol = "C"

'clear master sheet
Set Master = Sheets("Master Pipeline Report")
Master.Cells.ClearContents

'used to copy header row
First = True
For Each sht In ThisWorkbook.Sheets
If UCase(sht.Name) < UCase("Master Pipeline Report") Then
If First = True Then
sht.Rows(1).Copy Destination:=Master.Rows(1)
First = False
End If
ShtLastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row

sht.Rows("2:" & ShtLastRow).Copy _
Destination:=Master.Rows(MasterLastRow + 1)
End If
Next sht

MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Master.Rows("1:" & MasterLastRow)

SortRange.Sort _
Key1:=Range(SalesPersonCol & 2), _
Order1:=xlAscending, _
Header:=xlYes

End Sub


"bobo32" wrote:

This is exactly what I want and I would want to sort it by salesperson.
Thanks,
Bo

"Joel" wrote:

My assumption is that each sales person has there own customers and there
else no duplication of customers. there is a header row for the summary
sheet and fon each salesperson sheet. I also assume that each saleperson may
add new customers as well as change existing customers.

The best way of doing this is simply redo the summary sheet everytime you
want it updated. This will take only a few seconds. Is ther any order that
you want to summary sheet generated. sorted by customers or sorted by
saleperson. I also think that you should run the macro manually rather than
automatically updating.

Let me know if this is what you want. It is a very simple task that will
take a few minutes.



"bobo32" wrote:

The heading a
Customer, CustomerContact, Sales Person, Date, PipelineStage, Forecast
Close, Potential Opportunity, Weighted,Forecast, Chance of Sale,
InternalCalculation, Internal Calculation.
Rows:
Customer names.

I want each sales person to manually update their spreadsheet and when I
open the master spreadsheet I want it to be automatically updated,
I hope this is enough info if not let me knoe.
Thanks in advance,
bo

"Joel" wrote:

Not enough info. What is a Pipeline Report? What are the rows and columns?
Do you want a macro or to do it manually?

See other postings that have answers befor eyou reply. If you want a macro
then look at the Programming Postings, not the general listings.

When people don't get responses it is usually because that didn't give
enough information. Sometimes it is because there isn't an answer. Your
problem does have an answer.

"bobo32" wrote:

I have 5 pipeline reports from differnt sales people and want to combine it
into one master pipeline report. i also need it to update when a sales
person updates their individual work shhet

When you refer to worksheet(1 below) it can mean either the spreadsheet or
the macro code window (2 below) in VBA.

1) It doesn't matter which worksheet because the code call out each
worksheet by name.
2) In the VBA window you need to add a module like in my instructions and
add the code to the module you added.


"bobo32" wrote:

What worksheet should i work from/

"Joel" wrote:

To enter code into VBA

1) Copy code from posting by highlighting and copying. code starts with
"SUB" and ends with "End Sub"
2) OPen VBA window from worksheet by pressing ALT-F11
3) From VBA window menu - Insert - Module. This is Module 1 in the
Project Window on the right side of the woprksheet.
4) Paste Code into module window.
5) run code from VBA window by pressing F5 (first click on code, won't run
if cursor is outside the code. Run code from worksheet menu Tools - Macro -
Macro - Test. You can change the name of the code by changing "Sub Test" to
"sub Anything" in the first line of the VBA window in "Module 1"


"bobo32" wrote:

I am totally new to Excel, so if you could walk me through this in baby steps
I would greatly appriciate it.
thanks,
Bo

"Joel" wrote:

I think sales personm column is C. If not change below as required. Also
make sure you have a worksheet called "Master Pipeline Report". change if
you are using a different name in two places in code below.

Sub test()

SalesPersonCol = "C"

'clear master sheet
Set Master = Sheets("Master Pipeline Report")
Master.Cells.ClearContents

'used to copy header row
First = True
For Each sht In ThisWorkbook.Sheets
If UCase(sht.Name) < UCase("Master Pipeline Report") Then
If First = True Then
sht.Rows(1).Copy Destination:=Master.Rows(1)
First = False
End If
ShtLastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row

sht.Rows("2:" & ShtLastRow).Copy _
Destination:=Master.Rows(MasterLastRow + 1)
End If
Next sht

MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Master.Rows("1:" & MasterLastRow)

SortRange.Sort _
Key1:=Range(SalesPersonCol & 2), _
Order1:=xlAscending, _
Header:=xlYes

End Sub


"bobo32" wrote:

This is exactly what I want and I would want to sort it by salesperson.
Thanks,
Bo

"Joel" wrote:

My assumption is that each sales person has there own customers and there
else no duplication of customers. there is a header row for the summary
sheet and fon each salesperson sheet. I also assume that each saleperson may
add new customers as well as change existing customers.

The best way of doing this is simply redo the summary sheet everytime you
want it updated. This will take only a few seconds. Is ther any order that
you want to summary sheet generated. sorted by customers or sorted by
saleperson. I also think that you should run the macro manually rather than
automatically updating.

Let me know if this is what you want. It is a very simple task that will
take a few minutes.



"bobo32" wrote:

The heading a
Customer, CustomerContact, Sales Person, Date, PipelineStage, Forecast
Close, Potential Opportunity, Weighted,Forecast, Chance of Sale,
InternalCalculation, Internal Calculation.
Rows:
Customer names.

I want each sales person to manually update their spreadsheet and when I
open the master spreadsheet I want it to be automatically updated,
I hope this is enough info if not let me knoe.
Thanks in advance,
bo

"Joel" wrote:

Not enough info. What is a Pipeline Report? What are the rows and columns?
Do you want a macro or to do it manually?

See other postings that have answers befor eyou reply. If you want a macro
then look at the Programming Postings, not the general listings.

When people don't get responses it is usually because that didn't give
enough information. Sometimes it is because there isn't an answer. Your
problem does have an answer.

"bobo32" wrote:

I have 5 pipeline reports from differnt sales people and want to combine it
into one master pipeline report. i also need it to update when a sales
person updates their individual work shhet

I'm sorry I am so ignorant. If I have spreadsheets from my diffen't sales
people, how does this get all their info into the master?

"Joel" wrote:

When you refer to worksheet(1 below) it can mean either the spreadsheet or
the macro code window (2 below) in VBA.

1) It doesn't matter which worksheet because the code call out each
worksheet by name.
2) In the VBA window you need to add a module like in my instructions and
add the code to the module you added.


"bobo32" wrote:

What worksheet should i work from/

"Joel" wrote:

To enter code into VBA

1) Copy code from posting by highlighting and copying. code starts with
"SUB" and ends with "End Sub"
2) OPen VBA window from worksheet by pressing ALT-F11
3) From VBA window menu - Insert - Module. This is Module 1 in the
Project Window on the right side of the woprksheet.
4) Paste Code into module window.
5) run code from VBA window by pressing F5 (first click on code, won't run
if cursor is outside the code. Run code from worksheet menu Tools - Macro -
Macro - Test. You can change the name of the code by changing "Sub Test" to
"sub Anything" in the first line of the VBA window in "Module 1"


"bobo32" wrote:

I am totally new to Excel, so if you could walk me through this in baby steps
I would greatly appriciate it.
thanks,
Bo

"Joel" wrote:

I think sales personm column is C. If not change below as required. Also
make sure you have a worksheet called "Master Pipeline Report". change if
you are using a different name in two places in code below.

Sub test()

SalesPersonCol = "C"

'clear master sheet
Set Master = Sheets("Master Pipeline Report")
Master.Cells.ClearContents

'used to copy header row
First = True
For Each sht In ThisWorkbook.Sheets
If UCase(sht.Name) < UCase("Master Pipeline Report") Then
If First = True Then
sht.Rows(1).Copy Destination:=Master.Rows(1)
First = False
End If
ShtLastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row

sht.Rows("2:" & ShtLastRow).Copy _
Destination:=Master.Rows(MasterLastRow + 1)
End If
Next sht

MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Master.Rows("1:" & MasterLastRow)

SortRange.Sort _
Key1:=Range(SalesPersonCol & 2), _
Order1:=xlAscending, _
Header:=xlYes

End Sub


"bobo32" wrote:

This is exactly what I want and I would want to sort it by salesperson.
Thanks,
Bo

"Joel" wrote:

My assumption is that each sales person has there own customers and there
else no duplication of customers. there is a header row for the summary
sheet and fon each salesperson sheet. I also assume that each saleperson may
add new customers as well as change existing customers.

The best way of doing this is simply redo the summary sheet everytime you
want it updated. This will take only a few seconds. Is ther any order that
you want to summary sheet generated. sorted by customers or sorted by
saleperson. I also think that you should run the macro manually rather than
automatically updating.

Let me know if this is what you want. It is a very simple task that will
take a few minutes.



"bobo32" wrote:

The heading a
Customer, CustomerContact, Sales Person, Date, PipelineStage, Forecast
Close, Potential Opportunity, Weighted,Forecast, Chance of Sale,
InternalCalculation, Internal Calculation.
Rows:
Customer names.

I want each sales person to manually update their spreadsheet and when I
open the master spreadsheet I want it to be automatically updated,
I hope this is enough info if not let me knoe.
Thanks in advance,
bo

"Joel" wrote:

Not enough info. What is a Pipeline Report? What are the rows and columns?
Do you want a macro or to do it manually?

See other postings that have answers befor eyou reply. If you want a macro
then look at the Programming Postings, not the general listings.

When people don't get responses it is usually because that didn't give
enough information. Sometimes it is because there isn't an answer. Your
problem does have an answer.

"bobo32" wrote:

I have 5 pipeline reports from differnt sales people and want to combine it
into one master pipeline report. i also need it to update when a sales
person updates their individual work shhet

Make sure you have a worksheet called "Master Pipeline Report"

The following code checks every sheet in the workbook (skiping the Master
report)

For Each sht In ThisWorkbook.Sheets
If UCase(sht.Name) < UCase("Master Pipeline Report") Then


The following line copies each sheet to the master

sht.Rows("2:" & ShtLastRow).Copy _
Destination:=Master.Rows(MasterLastRow + 1)


sht is set to each worksheet one at a time. The instruction copies from row
2 (skips Header Row) to the Last row of each of the sheets and puts it after
the Last Row on the Master worksheet.

"bobo32" wrote:

I'm sorry I am so ignorant. If I have spreadsheets from my diffen't sales
people, how does this get all their info into the master?

"Joel" wrote:

When you refer to worksheet(1 below) it can mean either the spreadsheet or
the macro code window (2 below) in VBA.

1) It doesn't matter which worksheet because the code call out each
worksheet by name.
2) In the VBA window you need to add a module like in my instructions and
add the code to the module you added.


"bobo32" wrote:

What worksheet should i work from/

"Joel" wrote:

To enter code into VBA

1) Copy code from posting by highlighting and copying. code starts with
"SUB" and ends with "End Sub"
2) OPen VBA window from worksheet by pressing ALT-F11
3) From VBA window menu - Insert - Module. This is Module 1 in the
Project Window on the right side of the woprksheet.
4) Paste Code into module window.
5) run code from VBA window by pressing F5 (first click on code, won't run
if cursor is outside the code. Run code from worksheet menu Tools - Macro -
Macro - Test. You can change the name of the code by changing "Sub Test" to
"sub Anything" in the first line of the VBA window in "Module 1"


"bobo32" wrote:

I am totally new to Excel, so if you could walk me through this in baby steps
I would greatly appriciate it.
thanks,
Bo

"Joel" wrote:

I think sales personm column is C. If not change below as required. Also
make sure you have a worksheet called "Master Pipeline Report". change if
you are using a different name in two places in code below.

Sub test()

SalesPersonCol = "C"

'clear master sheet
Set Master = Sheets("Master Pipeline Report")
Master.Cells.ClearContents

'used to copy header row
First = True
For Each sht In ThisWorkbook.Sheets
If UCase(sht.Name) < UCase("Master Pipeline Report") Then
If First = True Then
sht.Rows(1).Copy Destination:=Master.Rows(1)
First = False
End If
ShtLastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row

sht.Rows("2:" & ShtLastRow).Copy _
Destination:=Master.Rows(MasterLastRow + 1)
End If
Next sht

MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Master.Rows("1:" & MasterLastRow)

SortRange.Sort _
Key1:=Range(SalesPersonCol & 2), _
Order1:=xlAscending, _
Header:=xlYes

End Sub


"bobo32" wrote:

This is exactly what I want and I would want to sort it by salesperson.
Thanks,
Bo

"Joel" wrote:

My assumption is that each sales person has there own customers and there
else no duplication of customers. there is a header row for the summary
sheet and fon each salesperson sheet. I also assume that each saleperson may
add new customers as well as change existing customers.

The best way of doing this is simply redo the summary sheet everytime you
want it updated. This will take only a few seconds. Is ther any order that
you want to summary sheet generated. sorted by customers or sorted by
saleperson. I also think that you should run the macro manually rather than
automatically updating.

Let me know if this is what you want. It is a very simple task that will
take a few minutes.



"bobo32" wrote:

The heading a
Customer, CustomerContact, Sales Person, Date, PipelineStage, Forecast
Close, Potential Opportunity, Weighted,Forecast, Chance of Sale,
InternalCalculation, Internal Calculation.
Rows:
Customer names.

I want each sales person to manually update their spreadsheet and when I
open the master spreadsheet I want it to be automatically updated,
I hope this is enough info if not let me knoe.
Thanks in advance,
bo

"Joel" wrote:

Not enough info. What is a Pipeline Report? What are the rows and columns?
Do you want a macro or to do it manually?

See other postings that have answers befor eyou reply. If you want a macro
then look at the Programming Postings, not the general listings.

When people don't get responses it is usually because that didn't give
enough information. Sometimes it is because there isn't an answer. Your
problem does have an answer.

"bobo32" wrote:

I have 5 pipeline reports from differnt sales people and want to combine it
into one master pipeline report. i also need it to update when a sales
person updates their individual work shhet

It gives me a run time error '9' Subscript out of range

"Joel" wrote:

Make sure you have a worksheet called "Master Pipeline Report"

The following code checks every sheet in the workbook (skiping the Master
report)

For Each sht In ThisWorkbook.Sheets
If UCase(sht.Name) < UCase("Master Pipeline Report") Then


The following line copies each sheet to the master

sht.Rows("2:" & ShtLastRow).Copy _
Destination:=Master.Rows(MasterLastRow + 1)


sht is set to each worksheet one at a time. The instruction copies from row
2 (skips Header Row) to the Last row of each of the sheets and puts it after
the Last Row on the Master worksheet.

"bobo32" wrote:

I'm sorry I am so ignorant. If I have spreadsheets from my diffen't sales
people, how does this get all their info into the master?

"Joel" wrote:

When you refer to worksheet(1 below) it can mean either the spreadsheet or
the macro code window (2 below) in VBA.

1) It doesn't matter which worksheet because the code call out each
worksheet by name.
2) In the VBA window you need to add a module like in my instructions and
add the code to the module you added.


"bobo32" wrote:

What worksheet should i work from/

"Joel" wrote:

To enter code into VBA

1) Copy code from posting by highlighting and copying. code starts with
"SUB" and ends with "End Sub"
2) OPen VBA window from worksheet by pressing ALT-F11
3) From VBA window menu - Insert - Module. This is Module 1 in the
Project Window on the right side of the woprksheet.
4) Paste Code into module window.
5) run code from VBA window by pressing F5 (first click on code, won't run
if cursor is outside the code. Run code from worksheet menu Tools - Macro -
Macro - Test. You can change the name of the code by changing "Sub Test" to
"sub Anything" in the first line of the VBA window in "Module 1"


"bobo32" wrote:

I am totally new to Excel, so if you could walk me through this in baby steps
I would greatly appriciate it.
thanks,
Bo

"Joel" wrote:

I think sales personm column is C. If not change below as required. Also
make sure you have a worksheet called "Master Pipeline Report". change if
you are using a different name in two places in code below.

Sub test()

SalesPersonCol = "C"

'clear master sheet
Set Master = Sheets("Master Pipeline Report")
Master.Cells.ClearContents

'used to copy header row
First = True
For Each sht In ThisWorkbook.Sheets
If UCase(sht.Name) < UCase("Master Pipeline Report") Then
If First = True Then
sht.Rows(1).Copy Destination:=Master.Rows(1)
First = False
End If
ShtLastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row

sht.Rows("2:" & ShtLastRow).Copy _
Destination:=Master.Rows(MasterLastRow + 1)
End If
Next sht

MasterLastRow = Master.Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Master.Rows("1:" & MasterLastRow)

SortRange.Sort _
Key1:=Range(SalesPersonCol & 2), _
Order1:=xlAscending, _
Header:=xlYes

End Sub


"bobo32" wrote:

This is exactly what I want and I would want to sort it by salesperson.
Thanks,
Bo

"Joel" wrote:

My assumption is that each sales person has there own customers and there
else no duplication of customers. there is a header row for the summary
sheet and fon each salesperson sheet. I also assume that each saleperson may
add new customers as well as change existing customers.

The best way of doing this is simply redo the summary sheet everytime you
want it updated. This will take only a few seconds. Is ther any order that
you want to summary sheet generated. sorted by customers or sorted by
saleperson. I also think that you should run the macro manually rather than
automatically updating.

Let me know if this is what you want. It is a very simple task that will
take a few minutes.



"bobo32" wrote:

The heading a
Customer, CustomerContact, Sales Person, Date, PipelineStage, Forecast
Close, Potential Opportunity, Weighted,Forecast, Chance of Sale,
InternalCalculation, Internal Calculation.
Rows:
Customer names.

I want each sales person to manually update their spreadsheet and when I
open the master spreadsheet I want it to be automatically updated,
I hope this is enough info if not let me knoe.
Thanks in advance,
bo

"Joel" wrote:

Not enough info. What is a Pipeline Report? What are the rows and columns?
Do you want a macro or to do it manually?

See other postings that have answers befor eyou reply. If you want a macro
then look at the Programming Postings, not the general listings.

When people don't get responses it is usually because that didn't give
enough information. Sometimes it is because there isn't an answer. Your
problem does have an answer.

"bobo32" wrote:

I have 5 pipeline reports from differnt sales people and want to combine it
into one master pipeline report. i also need it to update when a sales
person updates their individual work shhet