Hi Group...
I am trying to copy data from 100 *similar *excel files and copying in one
excel sheet. I tried below mentioned macro, but one problem has raised. In
each excel file there are about 98 rows but in the merged file only first 92
rows are copied and pasted. Can anyone find out the mistake in the below
mentioned macro and guide me... Urgent requirement.
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
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, "A").End(xlUp).Row).Copy
Else
Range("A1:Z" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
End If
Windows(vMaster).Activate
vStart = Range("A65536").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,
"A").End(xlUp).Row).Value = vFile
Workbooks(vWB).Close
vFile = Dir
Loop
Application.ScreenUpdating = True
MsgBox ("DONE")
End Sub
--
Satish P N
Asst. Manager
South Indian Bank
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
