Merging sheets within a single workbook
Merging sheets within a single workbook
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Merging sheets within a single workbook

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    State? Insanity! :)
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a document composed of 17 identical sheets (same exact fields, just different data in each sheet).
    I want ONE worksheet, I want to merge ALL the sheets into one ( very long!) sheet.

    HOW can I do this?

    ACTUALLY.. I ALSO have all 17 sheets, saved separately, if there is a way to merge all of them from separate files, into ONE file, that would be even better!
    Either way, same result.

    Thanks in advance for the help!

    [ This Message was edited by: vampcatt on 2002-02-23 00:51 ]

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Merge? Do you mean you want to SUM all of the data in all of the 17 cells, for instance B2, of all of the worksheets?

    If that's the case:

    Click on B2 of the *summary* sheet, which MUST be the first or last sheet of the workbook. Type =sum(
    Then, click on the sheet tab of the first worksheet you're adding from, hold your shift key, click on sheet tab of last worksheet you're adding from, now click on cell B2. Type a ) and hit enter. It will look something like:

    =sum(sheet1!:sheet5!B2)
    ~Anne Troy

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    State? Insanity! :)
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Actually. I want all documents to retain all the original data, but I want it all in ONE page.
    Maybe I should give an example, lets say they were pages with sample fields like :

    Name Age Location Number

    Lets say that EACH page is set up EXACTLY the same, no variances, except the text.
    Basically.. instead of having 1-200 entries downthe page EACH..
    I'd rather have 1 - 2800 entries in one page.

    (If there were only 4 fields, this wouldnt be that hard to cut and paste, but the fields are from A - AR) so.. its quite large.)

    I am probably wording this funky, not conveying exactly what I am trying to say..
    BUT.. instead of separate files, I want them all in one huge file.

    Help.. I know there has to be a way to do this, other than the manual "cut and paste" that would take forever.

    Thanks!

  4. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Actually, perhaps you're not aware of certain shortcuts. Try this:

    Open one of your worksheets.
    Click on cell B1.
    Hit Shift + Ctrl + Right arrow. This should select all of your first record.
    Now, hit Shift + Ctrl + Down arrow. This should select all of your records.
    Copy.
    Go to main workbook, click on A1. Hit Ctrl-Down arrow. Hit Down arrow once more. Hit Paste.
    If this does not work, perhaps your worksheets are not set up in an efficient manner? (No offense intended!)
    ~Anne Troy

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    State? Insanity! :)
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That seems to work just as hitting the corner square ( highlighting ALL cells) and cutting and pasting it .

    I DID combine all 17 files into one workbook, as separate sheets, BUT.. this is going to be a progressing task.

    I will do this once a week probably, and if I can avoid cutting and pasting 17 + entire documents into one file, that would be great.
    If there isnt a way to just merge different documents together, then I will just ahve to do manual cut and pastes..

    thanks!

  6. #6
    New Member
    Join Date
    Feb 2002
    Location
    State? Insanity! :)
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think that will work well enough, thank you for the new shortcuts.

    I have another question
    I don't know if I should start a new thread, or just ask in here, since you've been pretty good about replying

    Is there a way ( other than "find/replace" ) to change decimals to letters? )

    What I have is 7 columns of numbers.
    and 0.1 0.2 0.3 = P
    0.4 0.5 0.6 0.7= BA
    Is there a way to tell excel that certain ranges of numbers equals a certain letter value?

    Thank you so much !

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    You could use VBA, but if the list can be ordered, or sorted you can use VLOOKUP or HLOOKUP or INDEX to find strings or numbers. With and ordered list if will return predictable results even without an exact match. I use this to look up prices all the time. The price table is based on quantity. For example 9, 99, 999, 9999 If you do a lookup for 300 you will get the price associated with 999 witch of course this the 100 to 999 price.

    HTH

    Rocky...

    [ This Message was edited by: Rocky E on 2002-02-23 21:39 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com