combine excel sheets into one.....

P

prav.sarada

New Member
Joined
Jun 5, 2007
Messages
29
Sir,
I have 1200 excel sheets and i want to combine these into one sheet...copy paste is too difficult and time consuming....plz help!!!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
HI
How much of data you have in each book? (how many rows and columns?). How do you propose to collate the entire lot into one sheet? With 65000+ rows per sheet you can have about 50 rows per excel workbook.
Ravi
 
Upvote 0
One possible answer

One thing you could is the following if they are named the same with a date extension.



Code: 
GetDate:
Inputdate = InputBox("Please enter the reporting date in the following format: 070305.", "Date Input")
If Len(Inputdate) = 6 Then
    dtEnddate = Left(Inputdate, 2) & "/" & Mid(Inputdate, 3, 2) & "/" & Right(Inputdate, 2)
    DateInput1 = dtEnddate
    DateInput1 = DateAdd("d", -1, DateInput1)
    datelit = Format(DateInput1, "mmddyy")
    Workbooks.Open ("\\fileserve\Timetndr\Service Level\Gas South Reports\GS Data Template.xls")
    Workbooks.Open ("\\fileserve\Timetndr\Service Level\Gas South Reports\Data Files\Daily\gs." & datelit & ".xls")
    Windows("gs." + datelit + ".xls").Activate
    GoTo GetData
Else
End If
MsgBox "Date must be exactly 6 digits and you entered " & Inputdate
GoTo GetDate

GetData:
If ActiveSheet.Cells(x, "a") = "" Then
    GoTo GotFile
Else
End If
x = x + 1
GoTo GetData

GotFile:
x = x - 1
Worksheets("Gas South Script").Range(Cells(2, "a"), Cells(x, "o")).Copy
Windows("GS Data Template.xls").Activate
If sw1 = 0 Then
    m = x
    sw1 = 1
Else
    m = m + x - 1
End If
ActiveSheet.Paste Destination:=Worksheets("Gas South Script").Range(Cells(n, "a"), Cells(m, "o"))
Application.CutCopyMode = False
Windows("gs." + datelit + ".xls").Activate
ActiveSheet.Cells(x + 1, "a").Select
Workbooks("gs." + datelit + ".xls").Close
Windows("GS Data Template.xls").Activate
x = 2
n = m + 1
Bkup = Bkup + 1
If Bkup = 6 Then
    x = 2
    Fname = ("\\fileserve\Timetndr\Service Level\Gas South Reports\Data Files\Weekly\gs." + Inputdate + ".xls")
    ActiveWorkbook.SaveAs Filename:=Fname
    Workbooks("gs." + Inputdate + ".xls").Close
    GoTo OpenFiles
Else
End If
DateInput1 = DateAdd("d", -1, DateInput1)
datelit = Format(DateInput1, "mmddyy")
Workbooks.Open ("\\fileserve\Timetndr\Service Level\Gas South Reports\Data Files\Daily\gs." & datelit & ".xls")
Windows("gs." + datelit + ".xls").Activate
GoTo GetData
 
Upvote 0
its like ....
i have 200 rows in a workbook....
wb#1
albert
..
..
..
james

wb#2
david
....
....
....
brad









i want...them to be combined like this...
albert
...
...
...
james
david
...
..
..
brad



plz help me out....
 
Upvote 0
Here you go

OK. In my situation I have a file that is named as follows: gs.mmddyy.xls where mmddyy is the date it was created.
I have to roll either a month or a week of days together.

First I ask for the weekending date and open the first spreadsheet.
Code: 
GetDate: 
Inputdate = InputBox("Please enter the reporting date in the following format: 070305.", "Date Input") 
If Len(Inputdate) = 6 Then 
    dtEnddate = Left(Inputdate, 2) & "/" & Mid(Inputdate, 3, 2) & "/" & Right(Inputdate, 2) 
    DateInput1 = dtEnddate 
    DateInput1 = DateAdd("d", -1, DateInput1) 
    datelit = Format(DateInput1, "mmddyy") 
    Workbooks.Open ("\\fileserve\Timetndr\Service Level\Gas South Reports\GS Data Template.xls") 
    Workbooks.Open ("\\fileserve\Timetndr\Service Level\Gas South Reports\Data Files\Daily\gs." & datelit & ".xls") 
    Windows("gs." + datelit + ".xls").Activate 
    GoTo GetData 
Else 
End If 
MsgBox "Date must be exactly 6 digits and you entered " & Inputdate 
GoTo GetDate

Next I cycle through the spreadsheet on a cell that I kknow has data to find the bottom.
Code: 
GetData: 
If ActiveSheet.Cells(x, "a") = "" Then 
    GoTo GotFile 
Else 
End If 
x = x + 1 
GoTo GetData

Now I subtract one from my counter (it is an empty cell and not needed)
Code: 
GotFile: 
x = x - 1

Select all cells 
Worksheets("Gas South Script").Range(Cells(2, "a"), Cells(x, "o")).Copy 
Windows("GS Data Template.xls").Activate

Set starting point for paste in destination spreadsheet
Code: 
If sw1 = 0 Then 
    m = x 
    sw1 = 1 
Else 
    m = m + x - 1 
End If

Paste the data in the destination spreadsheet
Code: 
ActiveSheet.Paste Destination:=Worksheets("Gas South Script").Range(Cells(n, "a"), Cells(m, "o")) 
Application.CutCopyMode = False 
Windows("gs." + datelit + ".xls").Activate 
ActiveSheet.Cells(x + 1, "a").Select 
Workbooks("gs." + datelit + ".xls").Close 
Windows("GS Data Template.xls").Activate 
x = 2 
n = m + 1

Add one to my counter so I know when to stop
Code: 
Bkup = Bkup + 1 
If Bkup = 6 Then 
    x = 2 
    Fname = ("\\fileserve\Timetndr\Service Level\Gas South Reports\Data Files\Weekly\gs." + Inputdate + ".xls") 
    ActiveWorkbook.SaveAs Filename:=Fname 
    Workbooks("gs." + Inputdate + ".xls").Close 
    GoTo OpenFiles 
Else 
End If

Subtract 1 from my date and open the next file and start over again
Code: 
DateInput1 = DateAdd("d", -1, DateInput1) 
datelit = Format(DateInput1, "mmddyy") 
Workbooks.Open ("\\fileserve\Timetndr\Service Level\Gas South Reports\Data Files\Daily\gs." & datelit & ".xls") 
Windows("gs." + datelit + ".xls").Activate 
GoTo GetData
 
Upvote 0
If you want help with this you really need to give us far more information.

How is the data structured?

Where is it located?

Where are the workbooks with the data located?

How exactly do you want to combine it?

etc

Also, as has been pointed out by ravi, unless you are using Excel 2007, it won't be possible to combine 1200 worksheets/workbooks with that many rows into 1 worksheet.

Unless of course you spread the data across multiple columns, but even then you might hit some problems.
 
Upvote 0
Hi
I have uploaded a file called merge1200files.xls. you can download it from Download Link: http://www.filesend.net/download.php?f=a87025819d8cecadad294ec87d1f6706
All you need to do is put file path in cell A1 ( Ex: D:\my documents\money \rupee\) and press the button to run the macro. It will list all files in the folder in col A and col B and lists data of col A of each file as long as it is not blank. Try it.
Ravi
 
Upvote 0
You must log in or register to reply here.

Similar threads

Chris_010101
  • Solved
Combine Multiple Sheets
Replies
5
Views
594
Fluff
Fluff
B
  • Question
Combining csv sheets to one - code error
2
Replies
10
Views
838
Alex Blakenburg
A
C
  • Question
VBA Code Combine Multiple financial sheets
Replies
1
Views
248
mumps
mumps
V
  • Question
Copying data with header Name
Replies
0
Views
277
vmjan02
V
N
  • Question
Convert Data from multiple Rows to one Row with corresponding Value in Columns
Replies
9
Views
419
Kerryx
K

Forum statistics

Threads
1,214,979
Messages
6,122,557
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top