+ Reply to Thread
Results 1 to 17 of 17

Combine data of multiple sheets to one with a code

  1. #1
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,147

    Combine data of multiple sheets to one with a code

    Hello code experts
    I have here 25 sheets all with the name of E_Invoices + State Code with 1000's of rows of data. I am trying to combine all the data in one sheet in a new sheet named E_Invoices. The data of the first sheet to be copied to a new sheet and the rest of the sheets the data of which to be pasted below the last empty row in the new sheet. For your reference I have shared only 3 sheets so that I can upload it without any problem. The 25 sheets data starts from the 5th row and the new sheet data must begin at the 2nd row. The headings and columns count are same in all the sheets.
    I have been able to copy the data of first sheet to the new sheet but to combine the data of the other sheets to the last empty row of the E_Invoices sheet, it is my first. I need your expertise to do this with the help of a code which I can in my already existing code.
    Please note that I need to combine 25 sheets and not only 3 as in the sample data. Using a loop function or something I think it will be possible to check all sheets with the name E_Invoices + code.
    Thank you in advance.
    Attached Files Attached Files
    Last edited by RAJESH SHAH; 03-24-2023 at 06:44 AM. Reason: #Solved by Marc L

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hello, try this !


    Move first the expected worksheet to the left of E_Invoices worksheet or remove it …
    According to your attachment a VBA demonstration as a beginner starter :

    PHP Code: 
    Sub Demo1()
        
    Dim S&
            
    Sheet20.UsedRange.Offset(1).Clear
            Application
    .ScreenUpdating False
        
    For Sheet20.Index 1 To Sheets.Count
            With Sheets
    (S)
                .
    UsedRange.Rows("5:" & .Cells(.Rows.Count1).End(xlUp).Row).Copy Sheet20.Cells(Rows.Count1).End(xlUp)(2)
            
    End With
        Next
            Application
    .ScreenUpdating True
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  3. #3
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,147

    Re: Combine data of multiple sheets to one with a code

    Marc L Attachment 822905I tested the code twice but I am getting this message and is closing the excel application.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Combine data of multiple sheets to one with a code


    As no issue on my side with your attachment but yes I forgot to warn : works only with good enough readers …

  5. #5
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,147

    Re: Combine data of multiple sheets to one with a code

    Once the sheet E_Invoices is deleted and recreated by the code the sheet number may change. I will try to edit that part and try once again.

  6. #6
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,147

    Re: Combine data of multiple sheets to one with a code

    Got it right now. Thanks Marc L.
    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Combine data of multiple sheets to one with a code


    You badly modified my state-of-the-art code ‼ Your original code is totally useless !
    Just open your initial post attachment, remove the expected worksheet then use my demonstration as it is …
    Anyway, thanks for the rep' !

  8. #8
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,147

    Re: Combine data of multiple sheets to one with a code

    There is no E_Invoices sheet in the workbook when I run the code. The code creates one and then combines the data of all sheets to E_Invoices sheet.
    Now i get it what you are trying to say. The UP code sheet is printed twice. My bad.
    I will try and edit the code again and get only the headings first after I create the EInvoice sheet and then use your code to combine the sheets data in one. Thanks once gain Marc

  9. #9
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,147

    Re: Combine data of multiple sheets to one with a code

    Corrected now. But I am not getting the headers in color but it is not a problem. Marc I am still learning and your patience really helps. Thanks
    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Combine data of multiple sheets to one with a code


    Quote Originally Posted by RAJESH SHAH View Post
    There is no E_Invoices sheet in the workbook when I run the code.
    As I wrote « According to your attachment » so where E_Invoices worksheet already exists !
    The initial post needs a better explanation with a better reflective workbook attachment …

  11. #11
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,147

    Re: Combine data of multiple sheets to one with a code

    I meant to say that the EInvoices sheet is not in the workbook in which the data is to be combined. It is created first and then the data from the E_invoices + code sheets is combined to E_Invoices. There are 25 E_Invoices sheets in the workbook with code before I run the code. I hope you understood now Marc.

  12. #12
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,147

    Re: Combine data of multiple sheets to one with a code

    Marc L. I need your help to edit this one code line. There are other sheets too in the workbook other than the E_Invoices sheets with code names. When I run the code, it is combining the data of not only the E_INvoices sheets but also the other sheets in the workbook. Can you please edit this line to select only sheets with E_invoices + code name.? Please mention E_Invoices + Code beacuse there are other sheets with different names but with the same codes.
    For s = Sheets("E_invoices").Index + 1 To Sheets.Count

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Combine data of multiple sheets to one with a code


    As this does not match again your initial post so attach at least a 'before' workbook as it is just before to launch the VBA procedure
    and accordingly its exact expected 'result' workbook as it must be once the procedure ends …

  14. #14
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,147

    Re: Combine data of multiple sheets to one with a code

    I concur. It is working fine in the sample workbook. I avoided inserting all the sheets as there were more than 70 sheets in the workbook. So, I shared only the sample sheets which were connected to the code. I need your help to edit that one line and combine the sheets which contain E_Invoices A, B and so on.

  15. #15
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,147

    Re: Combine data of multiple sheets to one with a code

    I am sharing the new sample workbook. There are other sheets in the workbook other than E invoices. When I run the macro, the data from all the sheets is combined to the E invoices sheet. I want the code to select only the E invoices sheets and combine the data of those sheets only. I think this line needs editing. Can someone please edit this line and get me the correct result.?
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Try this ...


    According to your last attachment my VBA demonstration revamped to a very beginner level starter :

    PHP Code: 
    Sub Demo1r()
               Const 
    "E_invoices"
                 
    Dim Ws As Worksheet
        With Sheets
    (S)
               .
    UsedRange.Offset(1).Clear
                Application
    .ScreenUpdating False
            
    For Each Ws In Worksheets
                  
    If Ws.Name Like S " *" Then _
                     Ws
    .UsedRange.Rows("5:" Ws.Cells(Ws.Rows.Count1).End(xlUp).Row).Copy .Cells(.Rows.Count1).End(xlUp)(2)
            
    Next
        End With
                Application
    .ScreenUpdating True
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  17. #17
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    2,147

    Re: Combine data of multiple sheets to one with a code

    Finally got it right now after I inserted your code. Thank you very much Marc L.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Code to combine data of 2 sheets with condition
    By RAJESH SHAH in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-04-2023, 02:49 PM
  2. [SOLVED] Need to combine a code that loops through multiple sheets
    By g1terra in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-31-2019, 02:07 PM
  3. Help make my code work: Loop through folder of workbooks with multiple sheets and combine
    By carlito2002wgn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-06-2019, 08:19 PM
  4. How to combine code from multiple sheets to one sheet
    By Braden8898 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-12-2018, 10:42 AM
  5. [SOLVED] How to combine data from multiple sheets into one?
    By kb9omaaj in forum Excel Formulas & Functions
    Replies: 33
    Last Post: 02-08-2018, 04:11 PM
  6. Combine Data from multiple sheets
    By samfarrugia in forum Excel General
    Replies: 7
    Last Post: 10-02-2009, 11:41 AM
  7. [SOLVED] combine data from multiple sheets
    By Roxypup in forum Excel General
    Replies: 2
    Last Post: 04-07-2006, 02:45 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1