consolidating sales sheets

I am responsible for 6 sales people on the east coast.  I have a sheet for each salesperson (within the same shared network based workbook) to track their sales with sections (column & row headings) for forecasted sales, pipeline and beyond.  All they need to do is insert new rows and enter the cell contents.  Is there a convienent way for me to combine these three sheets each month to see how the whole area is performing.

0
anonymous (74722)
2/22/2004 5:16:05 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
309 Views

Similar Articles

[PageSpeed] 37

sorry a typo: it's consolidating the 6 sheets into a combined sheet within the same workbook.
0
anonymous (74722)
2/22/2004 5:31:06 AM
If the workbook is shared, then that combined sheet has to already exist--you
can't add a worksheet to a shared workbook.

So I'm gonna use Combined as the name of the sheet that gets all the data.  I'm
also guessing that row 1 the only row that contains headers and that header row
is already on Combined.

Option Explicit
Sub combineWks()

    Dim CombWks As Worksheet
    Dim wks As Worksheet
    Dim dummyRng As Range
    Dim rngToCopy As Range
    Dim DestCell As Range
    
    Set CombWks = Worksheets("combined")
    
    With CombWks
        'clean up existing data, but keep headers
        .Range("a2", .Cells(.Rows.Count, .Columns.Count)).ClearContents
        Set DestCell = .Range("a2")
    End With
    
    For Each wks In Worksheets
        If wks.Name = CombWks.Name Then
            'do nothing
        Else
            With wks
                'try to reset lastused cell
                Set dummyRng = .UsedRange
                Set rngToCopy _
                     = .Range("a2", .Cells.SpecialCells(xlCellTypeLastCell))
            End With
            With rngToCopy
                DestCell.Resize(.Rows.Count, .Columns.Count).Value _
                    = rngToCopy.Value
                Set DestCell = DestCell.Offset(.Rows.Count)
            End With
        End If
    Next wks
    
End Sub

gino from florida wrote:
> 
> sorry a typo: it's consolidating the 6 sheets into a combined sheet within the same workbook.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
2/22/2004 3:33:44 PM
Dave
Your assumption is correct.  The combined sheet already exists and it is the one that gets all the data combined.  You code looks very interesting, but I don't know what to do with it or where to put it.  please advise
Thanks very much
Gino.
0
anonymous (74722)
2/22/2004 4:26:06 PM
Since you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like:   VBAProject (yourfilename.xls)  

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.  But change the cell addresses to save and the names of
the worksheets.

Now go back to excel.
click on Tools|macro|macros...
click on the macro name (combineWks)
and then click run.

========

The workbook must not be shared for you to add the macro.  If you can't unshare
it, then you can put the macro in a separate workbook and then with your
"combination" workbook active, do the tools|macro stuff.

gino from florida wrote:
> 
> Dave,
> Your assumption is correct.  The combined sheet already exists and it is the one that gets all the data combined.  You code looks very interesting, but I don't know what to do with it or where to put it.  please advise.
> Thanks very much.
> Gino.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
2/23/2004 12:07:52 AM
Reply:

Similar Artilces:

Sales Order Transfer to Invoice
When attempting to transfer sales order to invoice, I receive the following error message: A valid document number could not be found. Any ideas as to what might be causing this or how to fix? I've dealt with a few transfer problems, but this error does not sound familiar. Do you have anything customized? Can other orders be transferred normally or is this a system-wide problem? "Scott" wrote: > When attempting to transfer sales order to invoice, I receive the > following error message: > > A valid document number could not be found. > > Any ideas as t...

change stage of sales process
Hi All, We have defined sales process in MSCRM, and we have a requirements that only manager can change the sales process stage from current stage to the next stage, how can we do that in MSCRM ? Because if we try to complete all the activities in the current stage, it will automatically change the stage to the next stage. We want to do it manually. Your advices, helps or guidances on this matter is really appreciated. Thanks for your help before. Kind regards, William maybe you should have a task that says contact manager for approval. This signals the user to call the manager and s...

Golf Tee sheet.
Hi guys, Im trying to create a golf tee sheet with some usefull functions. Im having trouble working out how to do a couple of things though. Firstly I would like to change a cell colour when a checkbox is clicked (The checkbox will be clicked when the tee time has been paid for) - that will then turn another 3 cells Green. The other thing I know im going to have trouble with is... I have 2 coloumns (one is for 9 hole rounds the other is for 18 hole) basicly if anything is typed into the 18hole cell for that time slot I would like the time slot exactly 2 hours ahead to be booked with the...

Consolidating Personal Folders
I have somehow opened up 4 personal folders. How do I consolidate all of them into one personal folder and merge all the information. Next, how do I delete the other personal folders? I've read through a couple of books but I can't find the information. Any help would be very much appreciated Thanks MikeSellers Move the contents of the folders to the folder that you want to keep. You can disconnect from the other pst-files by rightclicking the root folder and choose Close. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook I...

Sale List based on a Schedule
We have a lot of items that are always on sale (loss leaders). I have them put on sale according to a schedule (created so that they would be on sale 24/7). The reason I did it this way instead of just putting the lowered price in, is that I wanted to customers to see what they are saving. Anyway, is there a way to print a list of what I have on sale according to this "schedule"? Lance - I just worked out a report that I think might be what you are looking for. What is your email - I will shoot it to you. -- Jocelyn "Lance" wrote: > We have a lot of items th...

Important information on 2 sheets, combine?
I have 2 worksheets from my supplier. One has ALL of the produc information that I need, excluding the cost of the items. The one WIT the cost has little info on it, but I NEED the cost column. I know can copy and paste it to the first sheet, BUT I cannot trust the par numbers to match. The second sheet is updated every 15 minutes whil the first on updates maybe monthly. So info on the sheet one is older but the info on sheet two is limited. I hope this makes sense cause am wasting serious man hours trying to combine the right info for me t upload and to make sure all is correct is ta...

Counting sales
I have two different datasets. Each with a common identifier. I need a formula that will reference the identifiers in both datasets and count the number of sales a particular agent made. Example. look up this identifier in the other dataset and then give me the value that is 2 columns over from the identifier in the second dataset. See if this helps: http://contextures.com/xlFunctions02.html -- Biff Microsoft Excel MVP "Dave" <Dave@discussions.microsoft.com> wrote in message news:E7245B37-033F-400E-9CEE-9822BE7CB0F5@microsoft.com... >I have two...

Ability to re-open sales orders
It would be nice to have the same functionality on a Sales Order as there currently is with Purchase Orders. By this I mean the ability to re-open a sales order after it has been moved to History. Currently this functionality exists on a purchase order by changing the status to "Change Order" or something comparable. -- Thanks. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this ...

consolidate queries
I have many too many queries, which I use to gather data for a report. I hope someone can suggest how to consolidate them. For example, 8 of my queries are of the exact same form, except the value of one field is one of 8 possibilities. Here is the SQL for the qCUS query: SELECT q1Main.DReferred, q1Main.CaseType FROM q1Main WHERE (((q1Main.DReferred)>=[Forms]![fgetStats]![tbxBegDate] And (q1Main.DReferred)<=[Forms]![fgetStats]![tbxEndDate]) AND ((q1Main.CaseType)="CUS")); The only difference between the 8 queries is that in the qCUS query, CaseType = "...

making changes to existing sheets
I'vve have recieved from a friend falconer a sheet to hold the date of my birds like dayly weight, training results, food given, prey taken ect. Difference in weight between days. My problem is that I use metric values for weight and the table is set up for imperial weight. I have no experience with exel what concern formulas. the table I recieved is with choice window for the weight. If you point to it you can make a choise between predefined (imperial weights like 1, 1 1/4; .. . .). I am able to delete the formulas but not to see the formulas. I also want to know with formula is u...

Z Report to not print departments with zero sales
Hello All! How do I amend a z report to not print departments if the sales are zero. We have 192 departments but only a the sales seem to fall in a small portion... Thanks. And thanks for all the help I've received...your suggestions have made this much more bearable for me! This is a multi-part message in MIME format. ------=_NextPart_000_0621_01C7AECC.0F55A160 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: quoted-printable ZCSF, Download one from here, all the way at the bottom = http://tinyurl.com/4gdbu Must have access to CustomerSource --=20 J...

Consolidation Worksheet Data Sequence
I'm consolidating 3 worksheets into one by using Excel 2007 data consolidation. The worksheets have a product number in column A and sales figures in columns B, C & D for Jan, Feb and Mar. The worksheets are in product number sequence. Not all product numbers appear on each sheet, so I consolidate by category using "labels in left column" and "create links to source data" to create a consolidation sheet in outline form. The problem is the consolidated worksheet is not in product number sequence as I need it to be. I have books on Excel 2007 and Excel 2003 and their ...

Copying a range to another sheet
I have a central data sheet. It has a cell with =TODAY() in it, so it changes with the months, and shifts lit cells around according to dates and workdays. Is there a way to not only save this dynamic data sheet at the end of each day in its current state, but to archive this data to another master sheet as well, so that data is not lost when the month changes? For instance, I only need to save data in column A, C, and G to a range designated for January on a master sheet in the same workbook. When =TODAY() switches to February, I'd like to automatically archive a new month of da...

Sales license and Cases question
Hi there! I have a User with a single Sales license. It seems that he can see the Cases under an Account and he can receive activities regarding Cases, but cannot Complete them. Does it makes sense? How "far" a Sales user can go with Cases? Thanks a lot, Elena Verify the roles >-----Original Message----- >Hi there! >I have a User with a single Sales license. It seems that he can see the >Cases under an Account and he can receive activities regarding Cases, but >cannot Complete them. Does it makes sense? How "far" a Sales user can go >with Cases? ...

Updating Sales Order total
Greetings All: I'm integrating UPS shipping system with GP 9.0. I have developed an application that writes the freight and tracking data back to an order. I write the freight total to SOP10100.FRTAMNT. I'm quite green with GP 9.0 and discovered that indeed the freight on the order is updated but the order total doesn't get updated. What's the best way to get the order's bottom line updated? Is there some stored procedure or something that handles this logic? Thanks in advance, -Rusty Hi, I have tested you scenario as stated by you, the choice is yours if you ...

Calculation Sales tax
I want the sales tax to calculate and show on my form. This is my formula, but nothing appears on the form, not even an error message. Any help would be appreciated. Thanks, =IIf([Customers_Taxable]=True,[Subtotal]*[Sales Tax Rate]) -- Taylor Taylor Where are you putting this formula? Regards Jeff Boyce Microsoft Office/Access MVP "TaylorLeigh" <TaylorLeigh@discussions.microsoft.com> wrote in message news:42878E09-FA72-42DF-B371-0533563347D6@microsoft.com... >I want the sales tax to calculate and show on my form. This is my formula, > but nothing appears on t...

Creating a copy of a sheet and naming it
Being a newbie to VBA could I please have some advice . What I am trying to do is write a macro to select the active sheet and create a copy of it but then I would like an input message to ask me what to name the sheet so that I am presented with an exact copy (including macro buttons) but with a name of my choice. I have tried a myriad of combinations but do not seem able to get it to work This macro must be able to work with any selected sheet in the workbook Any help would be appreciated Brian Scotland Brian, SheetName = InputBox("Enter desired name for copy of active sheet&quo...

Re: Restricted Stock Unit (RSU) Disqualifying Sale and Taxes
I posted about this almost exactly three years ago. Here is an updated version of that post... I believe that on the day your RSUs are vested (deposited to your account) a taxable event occurs. Your employer witholds income tax immediately. The cost basis of the vested shares is the market value on the day of vesting. And, you should receive paperwork documenting these numbers. I modeled the above assumptions in MS Money as follows: 1. Do nothing until RSUs vest. Wait for documentation of vesting to arrive in the mail. When the documentation arrives, record the following in the M...

TextToColumn when destination to other sheet
God day Group, Is it at all possible to destinate the the split columns to another worksheet when using the TextToColumn wizard? Can it be made by code? When trying in the wizard I get the error message about wrong destination reference. Brgds CG Rosen Only the current sheet is possible -- Gary''s Student - gsnu200908 "CG Rosen" wrote: > God day Group, > > Is it at all possible to destinate the the split columns to another > worksheet when using the TextToColumn wizard? Can it be made > by code? When trying in the wizard I get t...

Does ino in deleted sheets persist?
I just delivered an MS Access application that creates .XLS files - each containing four sheets of employee/budget information. Two of the sheets contain only hourly data and two contain dollar amounts - from which one could deduce the hourly rate of an employee. Now the client has decided that they want the dollar sheets in separate .XLS files. My first thought is not to touch the process of creating a 4-sheet .XLS and just tack come code on to the end that saves the 4-sheet file twice - once named as an "Hours" document and once named as a "Dollars" document. Then I...

Summary sheet for a list of sheets
Hi all, I have a workbook with multiple sheets that have similar datasets on them. I'm trying to create a summary sheet that adds up the value of the cells across the sheets and lists them on one sheet. I have created a list of all the sheets I want to include (and have created a dynamic range to represent the list). I'm having trouble trying to write a formula that would take care of this by using the my list of sheets and summing all the values that have the same ID numbers. I'm not sure if this makes any sense. If you need more information or would like to take a look at the f...

Point of Sale
How can we have Point of Sale default to quote instead of sale? For our particular business, we need to start with a quote, then check out inventory, and then it would become a sale. This is a multi-part message in MIME format. ------=_NextPart_000_009E_01C79893.C6F025B0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: quoted-printable Kenny, MSPOS doesn't have a Quote feature. RMS does, but you can't default to it. Try creating a macro or a custom = button on your keyboard to go into Quote mode. --=20 Jeff=20 Check Point Software =3D=3D=3D=3D...

Printing non-adjacent cells on a single sheet
I'm no Excel expert so I'm hoping someone here can help me. I need to print non-adjacent cells / ranges in an MS Excel worksheet onto a single sheet. It is possible to select rages and set the print area to include those ranges; the problem being though is that each range is printed on a separate sheet. The only way I've found to get the various ranges to print on a single sheet is to hide the columns separating the ranges. I want to avoid having to hide unwanted cells each time we want to print a separate report. It's a spreadsheet that serves many uses and I&#...

Data Consolidation
Biff: Thanks so much for your input on the automation. After a small bit of tweaking, your formula worked like a charm. Now that I have the correlation multi-year table comparison automated, I would like to consolidate each commodities multi-year correlation into an overall correlation set. What I have now is five sets of correlation rows/commodity (representing the five historical data sets) which look like the following: Australian Dollar (AD) Correlations ============================== 1yr: C S PN SI SM 5yr: BO BP CD DX EU GC S SF SM 10yr: DX EU GC MP SF SI W 15yr: DX TU SP MP 20yr: CC G...

customer vendor consolidation #3
I would like to be able to transfer mulitple documents between payables and receivalbles all at once and have the option of having the documents transfer individually or as one lump sum. Currently if mulitple documents are transfered at the same time, a lump sum is transerferred instead of individiuals documents, which mean one credit memo is created instead of one per document being transferred. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in t...