Hi Satish,
I had the chance to look at this further this morning. The following
code seems to do the trick of pasting one workbook's data under
another.
All the best,
Chris
www.Technicana.com
Sub CombineFiles()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
vMaster = ActiveWorkbook.Name
vSheet = ActiveSheet.Name
Application.FileDialog(msoFileDialogFolderPicker).Show
vDirectory = Application.FileDialog
(msoFileDialogFolderPicker).SelectedItems(1)
vFile = Dir(vDirectory & "\*.xls")
Do Until vFile = ""
Workbooks.Open (vDirectory & "\" & vFile)
vWB = ActiveWorkbook.Name
vRows = Cells(Rows.Count, "A").End(xlUp).Row - 1
Debug.Print Workbooks(vMaster).Sheets(vSheet).Range("A65536").End
(xlUp).Row
If vRows > 65536 - Workbooks(vMaster).Sheets(vSheet).Range
("A65536").End(xlUp).Row Then
Workbooks(vMaster).Sheets.Add
vSheet = Workbooks(vMaster).ActiveSheet.Name
Workbooks(vMaster).Sheets(vSheet).Move after:=Workbooks
(vMaster).Sheets(Workbooks(vMaster).Sheets.Count)
Windows(vFile).Activate
End If
If IsEmpty(Workbooks(vMaster).Sheets(vSheet).UsedRange) Then
Range("A1:Z" & Cells(Rows.Count, "B").End(xlUp).Row).Copy
Else
Range("A1:Z" & Cells(Rows.Count, "B").End(xlUp).Row).Copy
End If
Windows(vMaster).Activate
vStart = Range("AB65536").End(xlUp).Offset(1, 0).Row
If vStart = 1 Then
Range("A1").Select
Else
Range("A" & vStart).Select
End If
ActiveSheet.Paste Range("AA" & vStart & ":" & "AA" & Cells
(Rows.Count, "AB").End(xlUp).Row)
Workbooks(vWB).Close
vFile = Dir
Loop
Application.ScreenUpdating = True
MsgBox ("DONE")
End Sub
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
