Can you send your file without data / with dummy data ?
On Wed, Jun 10, 2009 at 10:42 AM, satish <satishpag...@gmail.com> wrote:
> 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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
