Free Microsoft Excel 2013 Quick Reference

Compile files into one

Hi, all.

I have several workbooks. One for each day of the month. Now I want to compile them in to one and save as one workbook for that month, taking necessary 11 columns from them.

So e.g. there are 30 workbooks in december, dec 1,dec 2 .......... I want to compile them and name it as DECEMBER, But I don't want all the columns I need some specific columns in the new file DECEMBER. Is it possible through vba ? I have attached one example here.

Post your answer or comment

comments powered by Disqus
hi there,

I have a number of sheets with the same structure but different data. I need the information to sit in the different files for multiple users to be able to update it. Can someone please help me with a solution for compiling/pulling the data ranges in the different files into one file automatically. The ranges may vary although I could set a buffer in the ranges.

Please help.


I'm trying to develop a way to gather information from several excel files into one that will analyse all the data. I want to collect, for ex, named ranges A, B and C from files 1, 2 and 3 and then display that data in file 4, where I will display that data in form of a chart (i'm guessing pivot tables here are essential).

What's the best way to accomplish this? Writing VBA code to copy data from those 1, 2 and 3 files to file nº4?

I'm sorry if it's confusing..

i am tring to merge severalexcel files into one single workbook

Hi there, i'm new to vba and trying to get a hint about some work i need to do.
to make things short, i need to copy data from multiple file into one worksheet.

the detail is:
i have multiple .csv file with first (& only) worksheet named exactly like the file name (ex: if file name = azure.csv --> 1st worksheet name = azure).
these csv file has similar column (name & format) but may have different total of data rows.
from these data i want to make userform that enable user to choose which data file they want to copy via checkbutton. so if there is 3 file & user checks only two file name, only those two file data (with range(cell(x,y),cell(a,b))) will be copied (append) into one worksheet.

to make things more challenging:
data file to be copied doesn't need to get opened. i won't mind if it is opened in the background though.
and also, i put time frame in the userform so user can get which data they want to copy based on date&time value they input (already add comboboxes with from/to date & time).

Hopefully i'm not asking too much, but please help me...

Hi there,
First of all I want to thank you for that wonderfull site here.
it makes me happy that there is always someone who can help you in trouble.
I am new for the VBA macros but i like Excel and i am suprised from its power

Here is my problem:
Every month I have *.xls files containing one sheet with different data.
I tried to make a macro whose job was to copy all data from different files into one sheet but i failed .
Please tell me is there some kind of macros which will copy all data sheet from all files into one sheet and to detect every next empty row to paste the information there ?
Please help me !!
if you want more information my e-mail is

Does anyone know if there's an easy way of importing hundreds of Excel files into one file?

I have several files saved under the same folder (which each has one worksheet only), I would like to single click of a button to combine all these files into one (by entering the location of the folder where all these files are saved), so that I can have just 1 file that contains different worksheets. thanks

is it also possible to extract the name from a single cell from each file (same location on all files), and use that to rename the tab in the combined file?

hi all,

Could someone help me to combine multiple text files into one file using excel VB code.

eg: I have three files below in C:temp

test.txt contains:
today is beautiful
but tomorrow will not be

test1.txt contains:
last year was good
but this year is better

test2.txt contains:
this week is the first week of the month
next week is the second week of the month

combined file contains:
today is beautiful
but tomorrow will not be
last year was good
but this year is better
this week is the first week of the month
next week is the second week of the month

thanks in advance for your help.


Hi all,
I'm trying to come up with a way to import multiple date named csv files into one excel worksheet. So basically something that takes some values from a user (start / end date), changes the format slightly, and then imports the correct files into a specific sheet.

Basically some custom software in my factory saves these comma delimited text files as follows:

The first part of the name (B1) can either be B1 or B2 dependant upon the plant. The user should be able to choose the plant from the "import" spreadsheet. The last 6 values are the dates of the files (it saves one per plant per 24 hours). Ideally, the user should be able to choose whether to import one, or both plants, and one or more dates as chosen.

It's very very difficult for me - please help if possible!

I regularly have to combine anywhere from 10 to 50 files into one file and I would like a macro that would allow me to combine the data from multiple files into 1 file and in one sheet. I have one that will combine it into one file but puts every sheet into seperate workbooks. That wont work.

I need this to be able to work with both .csv and .xls as well.

Thanks in advance.

I get a dump of about 70 text files into a shared folder each week...lets call the folder "Data Dump". All of the files in the folder are named with a txt extension such as 5474.txt, tudfhd.txt ,or dysh.txt...etc. I need a way to combine all of these files into one and get it into Excel so I can start creating a Macro for it. Anyone know how to do that....Thanks in advance.

Desire to copy and paste 38 individual files into one file as 38 individual
worksheets. Have attempted one file individualy and am loosing formulas with
all cells of each file copy and pasted. I think I have the loss of formula
data resolved now but does anyone have a five, six, seven stroke step
allowing the copying then pasting of all 38 files at once?

Id like to add but in my own opinion...........Since 1995 as a Windows user
Microsoft has at last created Vista and the 2007 series Office products of
which I can now say.........."about time Microsoft, products/software worth
their price" Thank you.

Yvez TT Constance

I have 20 excel files and each excel file contains 3 worksheets. How can I
combine these files into one by only a few clicks?

In the past, I did it by right click 'move or copy' and one by one.


I have tried a few macro codes for merging several similar excel files
into one but all of them (the ones that actually worked) did not read
the 'correct' worksheet withing the XLS.

Can anyone give me a hint?

Thank you so much!!!


hello, I am facing difficuties to combine 2 files into one excel file
These 2 file consist about 80,000 rows.

Need your help urgently and please advice.

Thank you.


I need to combine two .xlsx files into one .xlsx file. Here is a picture, so it would be much easier to understand what's the goal

I need those two files to match each other by the product_id, as you can see from the picture.

Thanks in advance for your help.


Can u help any body in merging the multiple *.xls files into one single *.xls file but each *.xls file com in separate worksheet.

Say i have 30 xls files in datewise i.e., 01.10.10, 02.10.10, 03.10.10 so on....

I want to merge all the above 30 xls files in single file master workbook - in that master workbook file the above 30 xls should come in separate work sheets.

Thank you

Dear Madam, Sir,

if i want to import serveral csv files into one worksheet in Excel how will
the macro look like?

can you help me on that.

thanks in advance,

Rick Portier

Hi all,

I'm trying to import multiple text files into one worksheet and have the following code.

It runs fine for the 1st file but throws error while refreshing the second one. The line where I'm getting the error is in bold. I'm getting the following error message "Run-time error '1004': Application-defined or object-defined error". Really appreciate all your help.
Dim CntnNm As Integer

Do While strfile <> ""
MsgBox strfile

StartRange = ActiveSheet.Range("GZ1").Value
MsgBox ("A" & StartRange)

ActiveSheet.Range("A" & StartRange).Select

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:Documents and Settingsckar1DesktopAsher" & strfile, Destination:= _
        ActiveSheet.Range("A" & StartRange))
        .Name = CntnNm
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "{"
        .TextFileColumnDataTypes = Array(2, 5, 2, 1, 2, 2, 1, 1, 2, 5, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, _
        1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        'On Error Resume Next
        .Refresh BackgroundQuery:=False
        'On Error GoTo 0
    End With

'MsgBox ActiveSheet.QueryTables.Count

'x = ActiveSheet.QueryTables.Count

'Do While ActiveSheet.QueryTables.Count >= 1

'MsgBox ActiveSheet.QueryTables.Count


CntnNm = CntnNm + 1

strfile = Dir

RowNo = ActiveSheet.Range("GZ1").Value
MsgBox RowNo


'RowNo = ActiveSheet.Range("GZ1").Value
'MsgBox RowNo

Could someone please help me with an excel macro to copy data/information
from multiple Excel files (each info is in the same cell and same column
label in each file) into one Excel file , each source file info going to one
row in the destination file.

I travel 3rd Class because there is no 4th Class- Gandhi.

My co-worker have multiple Excel files (1 worksheet per file); all worksheets have the same column headers and structure; she needs to combine all these worksheets from the multiple files into one worksheet with all the data stacked one below the other.

Please help if you know of an easy way to do this.

If would be great if you could provide a macro code and instruction on how to use the macro.

Hi everyone -

I am analyzing some data and at this step I need to get about 400 text files into one excel worksheet. The most advanced thing I have done in Excel up until now is make a pivot table, so I am going to need a little help.

Right now I have these data files in text format, in a single column with 17 rows. (See end of post for example)

I want to import each file either into its own row or its own column (doesn't matter).

One extra kink: the file name is also important, so I want to append that at the beginning or end of each file.

Here, as promised, is an example:


I'm leaning towards doing this in a UNIX shell, but I am hoping that there is a better way to do it directly in Excel - does anyone have any suggestions?

Thanks very much!

i tried to find a macro to consolidate multiple excel files into one file, in the forum but could not find anything which suits my requirements....
can someone pls help me with this....

i have approximately 30 excel files in a folder, which i should consolidate into one sheet...
the path of the folder is not changes from day to day...
for instance the files which we use today will be saved in today's dated folder i.e., "Day shift 12/19/2010"...
i should consolidated all the files in the above folder at the end of the day...which i should repeat daily...
the excel file contains data only in sheet1...
and rows and columns are not fixed...
each files contains header... and the header should include only ones in master sheet.

your help is greatly appreciated...

I have several excel files (not worksheets within one file) that I want to
combine into one excel file. In other words, I want to combine file1.xls,
file2.xls, file3.xls, etc and put all of the data from all three of those
files into one excel file. The data in each individual file is of the same
information, so this should be done without any problems. How do I combine
the individual files into one excel file?


I have 60 excel files with single sheet and I want to merge all 60 files into one excel file with 60 tabs. Is there any way to do it with macros ??

Appreciate your help !!!


No luck finding an answer? You could always try Google.