Having a problem with Macro to merge data help needed

[chris priyesh]

Hi All,
I have been using the macro mentioned in the link given below to merge data from various workbooks.
Excel Macro VBA - Combine Multiple Workbooks into One - Free Excel Macros VBA - TeachExcel.com

[COLOR=#000000][FONT=monospace]Private Declare Function SetCurrentDirectoryA Lib _[/FONT][/COLOR]
[COLOR=#000000][FONT=monospace]    "kernel32" (ByVal lpPathName As String) As Long[/FONT][/COLOR]
[COLOR=#000000][FONT=monospace]Sub ChDirNet(szPath As String)
    SetCurrentDirectoryA szPath
End Sub[/FONT][/COLOR]
[COLOR=#000000][FONT=monospace]Sub Combine_Workbooks_Select_Files()
    Dim MyPath As String
    Dim SourceRcount As Long, Fnum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim rnum As Long, CalcMode As Long
    Dim SaveDriveDir As String
    Dim FName As Variant[/FONT][/COLOR]
[COLOR=#000000][FONT=monospace]    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With[/FONT][/COLOR]
[COLOR=#000000][FONT=monospace]    SaveDriveDir = CurDir
    ChDirNet "C:\"[/FONT][/COLOR]
[COLOR=#000000][FONT=monospace]    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
                                        MultiSelect:=True)
    If IsArray(FName) Then
        Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
        rnum = 1
        For Fnum = LBound(FName) To UBound(FName)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(FName(Fnum))
            On Error GoTo 0
            If Not mybook Is Nothing Then
                On Error Resume Next
                With mybook.Worksheets(1)
                    Set sourceRange = .Range("A:I")
                End With
                If Err.Number > 0 Then
                    Err.Clear
                    Set sourceRange = Nothing
                Else
        If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
                        Set sourceRange = Nothing
                    End If
                End If
                On Error GoTo 0[/FONT][/COLOR]
[COLOR=#000000][FONT=monospace]                If Not sourceRange Is Nothing Then[/FONT][/COLOR]
[COLOR=#000000][FONT=monospace]                    SourceRcount = sourceRange.Rows.Count[/FONT][/COLOR]
[COLOR=#000000][FONT=monospace]                    If rnum + SourceRcount >= BaseWks.Rows.Count Then
                        MsgBox "Not enough rows in the sheet. "
                        BaseWks.Columns.AutoFit
                        mybook.Close savechanges:=False
                        GoTo ExitTheSub
                    Else
                        Set destrange = BaseWks.Range("A" & rnum)
                        With sourceRange
                            Set destrange = destrange. _
                                            Resize(.Rows.Count, .Columns.Count)
                        End With
                        destrange.Value = sourceRange.Value[/FONT][/COLOR]
[COLOR=#000000][FONT=monospace]                        rnum = rnum + SourceRcount
                    End If
                End If
                mybook.Close savechanges:=False
            End If
        Next Fnum
        BaseWks.Columns.AutoFit
    End If
ExitTheSub: 
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
    ChDirNet SaveDriveDir
[/FONT][/COLOR]
[COLOR=#000000][FONT=monospace]End Sub[/FONT][/COLOR]

However i have 2 issues with the same:
1. It copies only the data from 1 sheet. If the file has multiple sheets is it anyway possible that the data would be merged from multiple sheets as well?? (like the Sheet 1 of master file contains the merged data of sheet 1 of all the files, sheet 2 of master file contains the merged data of sheet 2 of all files etc). If not can you suggest any other Macro that does the same?

2. i have been facing with "Not enough rows in the sheet" error. Can you guide me on how to over come that? Usually my data would be in columns A:I or A:V the length would differ each time hence can't give a fixed range.

Hope you guys would help me or point me in the right direction.
All help is deeply thanked and Greatly appreciated.