Appending multiple excel sheets into one

[rexer231]

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

[arlu1201]

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

[rexer231]

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?

[arlu1201]

Sorry for the delay in replying to you. I was sick for a week and couldnt get back to this thread.

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.

[putut]

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

[arlu1201]

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.

[rexer231]

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???

[arlu1201]

Instead of

Please Login or Register  to view this content.

use this

Please Login or Register  to view this content.

[rexer231]

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