Hi Friends I need help to rectify my code error.
my query is I have 4(.xls) files in folder and macro file, my code is open xls file one by one and copy the data in my active macro file also rename the tab according the open xls file name, but is not working Please help ..
Option Explicit
'-------------------------------------------------------------------------------
Sub Collect()
'-------------------------------------------------------------------------------
'Collect all "Input" sheets from all workbooks in folder.
Dim DataName As String
Dim DataWB As Workbook
Dim File As String
Dim MasterWB As Workbook
Dim MScnt As Integer
Dim Snr As Integer
'Find out number of sheets in Master
Set MasterWB = ActiveWorkbook
MScnt = MasterWB.Sheets.Count
'Switch to folder containing data workbooks
'Use path from master for now
ChDir MasterWB.Path
'Find al xlsx workbooks in folder
File = Dir("*.xls")
While File <> ""
Debug.Print "Processing file " & File
'Do not process yourself
If InStr(File, MasterWB.Name) = 0 Then
'Open data workbook
Set DataWB = Workbooks.Open(File, xlUpdateLinksNever, True)
DataWB.Activate
'Catch missing input sheet
On Error Resume Next
Snr = 1
'Snr = Sheets("input").Index
On Error GoTo 0
If Snr > 0 Then
Sheets(Snr).Copy After:=MasterWB.Sheets(MScnt)
MasterWB.Activate
'Rename added sheet; use data wb name for now
MScnt = MScnt + 1
Sheets(MScnt).Name = DataWB.Name
End If
MasterWB.Activate
DataWB.Close False
End If
'Next file
File = Dir()
Wend
end sub
Bookmarks