+ Reply to Thread
Results 1 to 14 of 14

Collate data from all tabs to a tab

  1. 03-14-2014, 07:25 AM #1
    Sri.n
    Sri.n is offline
    Forum Contributor
    Join Date
    06-25-2013
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    114

    Collate data from all tabs to a tab

    Hello All,

    I always copy data from all tabs to a single tab. I am looking for a macro which does this and saves time. Criteria is Data starts from Col B to Q not further in all tabs, but the rows will vary in all tabs.

    Looking for a macro which helps on this.

    Regards,
    Sri
    Reply With QuoteRegister To Reply
  2. 03-14-2014, 07:31 AM #2
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Collate data from all tabs to a tab

    Maybe:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 03-14-2014, 07:38 AM #3
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,885

    Re: Collate data from all tabs to a tab

    Try this:

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  4. 03-14-2014, 07:55 AM #4
    Sri.n
    Sri.n is offline
    Forum Contributor
    Join Date
    06-25-2013
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    114

    Re: Collate data from all tabs to a tab

    Thanks for the code but seems like its not copying all the info which is the tabs, I have attached a sample format for your reference.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-14-2014, 08:55 AM #5
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,885

    Re: Collate data from all tabs to a tab

    Whose code are you referring to? John's or mine or both?

    I tried to open your file and excel indicated that it was corrupted. Unable to examine your file. Since you saved as an xlsx file, I am assuming that it does not contain either code. How do you expect us to diagnose it if you do not include the code you used. You will have to amended the code for sheet names at a minimum at least.

    Want to try again?
    Last edited by alansidman; 03-14-2014 at 08:59 AM.
    Reply With QuoteRegister To Reply
  6. 03-14-2014, 09:06 AM #6
    Sri.n
    Sri.n is offline
    Forum Contributor
    Join Date
    06-25-2013
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    114

    Re: Collate data from all tabs to a tab

    John code is overlapping the data and your code is stopping me. I have attached the file again as Xls. please let me know if you are able to view the format
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 03-14-2014, 09:08 AM #7
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Collate data from all tabs to a tab

    The file is still corrupted.
    Reply With QuoteRegister To Reply
  8. 03-14-2014, 09:35 AM #8
    Sri.n
    Sri.n is offline
    Forum Contributor
    Join Date
    06-25-2013
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    114

    Re: Collate data from all tabs to a tab

    Hi John,

    not sure whats happening. I have created newly and attached now. Please try.

    Thank you
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 03-14-2014, 09:44 AM #9
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Collate data from all tabs to a tab

    Quote Originally Posted by Sri.n View Post
    Hi John,

    not sure whats happening. I have created newly and attached now. Please try.

    Thank you
    It gives an error which says "Too Many Different Cell Formats"
    Reply With QuoteRegister To Reply
  10. 03-14-2014, 01:29 PM #10
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,885

    Re: Collate data from all tabs to a tab

    It gives an error which says "Too Many Different Cell Formats"
    I had same error message this morning. Opened latest file and it is absolutely empty. No data at all. Just empty worksheets with some formatting. No VBA code to analyze either.
    Reply With QuoteRegister To Reply
  11. 03-17-2014, 04:06 AM #11
    Sri.n
    Sri.n is offline
    Forum Contributor
    Join Date
    06-25-2013
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    114

    Re: Collate data from all tabs to a tab

    Morning All.

    I have managed to attached another file now. Please can you let me know if you are able to open the file.

    Thanks
    Sri
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 03-17-2014, 05:25 AM #12
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Collate data from all tabs to a tab

    You do not say if you want to include the total in each sheet.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 03-18-2014, 07:58 AM #13
    Sri.n
    Sri.n is offline
    Forum Contributor
    Join Date
    06-25-2013
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    114

    Re: Collate data from all tabs to a tab

    Hello AB33, that's great it works well and please can I ask you to ignore the Totals and add single total line at the bottom which should be the sum of all the above lines with out any blanks.

    Thanks
    Reply With QuoteRegister To Reply
  14. 03-18-2014, 12:30 PM #14
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Collate data from all tabs to a tab

    Try the attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Collate data monthly from different Tabs
    By skoushik in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-08-2013, 07:53 AM
  2. [SOLVED] Collate all tabs in a single file
    By fatalcore in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2012, 07:22 AM
  3. Link to collate the data from different tabs
    By raxs in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-16-2012, 11:52 AM
  4. Link to collate the data from different tabs
    By raxs in forum Excel General
    Replies: 2
    Last Post: 03-14-2012, 08:44 AM
  5. Collate Data
    By Chatbot in forum Excel General
    Replies: 20
    Last Post: 09-08-2011, 07:05 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1