Excel help needed

krkbkk Well-Known Member

Messages:: 1,458

Likes Received:: 29

Best Answers:: 0

Trophy Points:: 195

#1

I've about 10,000+ excel files each with about 10 rows of data - all in similar format (same number of columns)

I wanted to merge all of them in a single sheet.

Is it possible to do this quickly?

krkbkk, Apr 26, 2012 IP

Iann@LFCVPS Peon

Messages:: 28

Likes Received:: 0

Best Answers:: 0

Trophy Points:: 0

#2

I took this word for word from another forum posting but I don't have enough posts to provide the link, anyway I'm no Excel expert but it seems fairly legit:

The following macro will do the job of copying all your data from indvidual workbook and paste into a single sheet.

No error trap is provided.

Copy your all individual workbooks into a single directory "C:\Temp\"
Open a new workbook. Right click the leftmost excel ison and click viewcode then paste this code and run.

Keep backup of your orignal data.

Sub MergeSheets()
Dim SrcBook As Workbook
Dim fso As Object, f As Object, ff As Object, f1 As Object

Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.Getfolder("C:\Temp\")
Set ff = f.Files

For Each f1 In ff
Set SrcBook = Workbooks.Open(f1)
Range("A1:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
SrcBook.Close
Next
End Sub
Click to expand...

Iann@LFCVPS, Apr 27, 2012 IP

krkbkk Well-Known Member

Messages:: 1,458

Likes Received:: 29

Best Answers:: 0

Trophy Points:: 195

#3

Thanks!

krkbkk, Apr 29, 2012 IP

Log in or Sign up

Excel help needed

krkbkk Well-Known Member

Iann@LFCVPS Peon

krkbkk Well-Known Member

Useful Searches