how to copy a.xls and b.xls to c.xls .

honey26 · November 16th, 2011, 01:56 PM

hi ,

i have two sheets a.xls and b.xls i want to copy it to c.xls . It should be like first all contents of a.xls is copied then contents of b.xls are copied . in sequence .

Shasur · November 16th, 2011, 10:15 PM

There are lot of ways to do this

Copy the usedrange of each excel in to the new one

Merge the excel sheets (http://video.about.com/spreadsheets/...s-in-Excel.htm)

Have a look at this and select the best one

Cheers
Shasur

honey26 · November 17th, 2011, 03:39 AM

thanks ....................

Shasur · November 17th, 2011, 08:29 AM

Hi

Here is a hint

Code:

Sub ad1()
Dim i1 As Long
Dim oWBC As Workbook
Dim oWBA As Workbook
Dim oWBB As Workbook
Set oWBC = Workbooks.Add
Set oWBA = Workbooks.Open("c:\1.xls")
oWBA.Sheets(1).UsedRange.Copy oWBC.Sheets(1).Range("A1")
oWBA.Close (False)
i1 = oWBC.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Set oWBB = Workbooks.Open("c:\2.xls")
oWBB.Sheets(1).UsedRange.Copy oWBC.Sheets(1).Range("A" & i1)
oWBB.Close (False)
oWBC.SaveAs "c:\3.xls"
oWBC.Close (False)
 
End Sub

If you want to schedule every week, it would be better if you can develop the same in Vb6 / .NET and create an Exe. You can then add this Exe in Windows Scheduler

Cheers
Shasur

honey26 · November 17th, 2011, 09:28 AM

Thanks .........

Shasur · November 17th, 2011, 12:34 PM

Then have a for loop

Code:

Sub ad1()
Dim i1 As Long
Dim oWBC As Workbook
Dim oWBA As Workbook
Dim oWBB As Workbook
Set oWBC = Workbooks.Add
For i = 1 to 10
i1 = oWBC.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Set oWBB = Workbooks.Open("c:\" & i & ".xls")
oWBB.Sheets(1).UsedRange.Copy oWBC.Sheets(1).Range("A" & i1)
oWBB.Close (False)
Next i
oWBC.SaveAs "c:\Master.xls"
oWBC.Close (False)
 
End Sub

Cheers
Shasur