+ Reply to Thread
Results 1 to 9 of 9

Appending multiple excel sheets into one

  1. #1
    Registered User
    Join Date
    05-01-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    39

    Appending multiple excel sheets into one

    Need to make a macro which can append all the excel files ( names may vary) that are located in a particular folder. The number of rows in the excel sheets can vary. For example:

    folder name is : storage
    I have 3 excel files placed in storage folder, i.e. home.csv, HereIam.csv and There.csv


    I need the data from all three to be in a single excel sheet.

    So the data of the first excel will remain at the top of the sheet, the data of the second excel will be pasted below the data of the first excel and then the data of the third excel will be pasted below the data of the second excel, until there is no more excels in the specified folder ( i.e. storage folder) from where I need to collect data.

    Please help

  2. #2
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: Appending multiple excel sheets into one

    Try this code
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose Tools | Macro | Macros
    Select a macro in the list, and click the Run button
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    05-01-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Appending multiple excel sheets into one

    Awesome Arlu!!
    Thanks a ton!

    Could you also clarify some queries regarding the code:

    Application.ScreenUpdating = False -> What does this do?
    Application.DisplayAlerts = False -> What does this do?


    CurrentFileName = Dir(myPath & "\*.csv") - > does this code ensure that xls, xlsx and csv are searched for? or is it just csv?

    Master.Worksheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) - > what does offset(1,0) do?

  4. #4
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: Appending multiple excel sheets into one

    Sorry for the delay in replying to you. I was sick for a week and couldnt get back to this thread.
    Quote Originally Posted by rexer231 View Post
    Application.ScreenUpdating = False -> What does this do? - This stops the screen from flickering during the code execution. Once the code is complete, it will go back to the regular screen flickering as required.

    Application.DisplayAlerts = False -> What does this do? This stops popups from showing up during code execution. Once the code is complete, it reverts to its original state.

    CurrentFileName = Dir(myPath & "\*.csv") - > does this code ensure that xls, xlsx and csv are searched for? or is it just csv? Only csv since the line states *.csv.

    Master.Worksheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) - > what does offset(1,0) do? It asks the code to paste the data one row below the existing data. So offset (1,0) positions the pointer at the next available row.

  5. #5
    Registered User
    Join Date
    06-18-2013
    Location
    Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Appending multiple excel sheets into one

    Dear Arlu1201 what a usefull code.., Thank you.
    I will try to find for additional function such as:
    - I can open folder that I want by click "Browse"
    - can append from many excel file that contain more than 1 sheet name

  6. #6
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: Appending multiple excel sheets into one

    putut,

    If you have questions regarding those 2 functions, you can create a thread of your own and post it there. Someone, if not me, will surely help you.

  7. #7
    Registered User
    Join Date
    05-01-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Appending multiple excel sheets into one

    Hi Arlu,

    With the same requirement as above, i.e. to select data from different sheets located in different folder, I need that only certain columns be copied to the mastersheet. I.e. would only want to copy the contents of column A, C, E
    how would I be able to modify the above code to only select certain columns entirely???

  8. #8
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: Appending multiple excel sheets into one

    Instead of
    Please Login or Register  to view this content.
    use this
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    05-01-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Appending multiple excel sheets into one

    Hi Arlu

    is there a way to have some code in place which highlights or skips if a csv file is not present in a folder? Because right now it causes an error if a csv file is not present.


    Also, is there a way to traverse all the folders inside of a main folder?

    id I have a folder called class

    in class I have - maths, science and English.

    want to traverse all these folders without naming

+ 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. Appending multiple sheets into one excluding header cells
    By knevil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-23-2014, 11:37 AM
  2. Filter data from a master to multiple sheets + appending lists
    By nomwich in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 11-15-2013, 12:42 PM
  3. Replies: 0
    Last Post: 04-19-2013, 05:50 PM
  4. Appending Multiple Excel Sheets
    By krizbomb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-02-2013, 04:35 AM
  5. Appending student data from multiple sheets in chronological order on another sheet
    By StudentTeacher in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-22-2010, 10:53 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