1004 error while running the macro in VBA

[Jagdev]

Hi Experts,

The attached macro is used to extract data from various sheets placed in a folder into on single new sheet.

The attached macro is working fine, but throwing an error on few instances. Please find the macro and error message attached with the thread.

Also, it is extracting data from one tab in case where the sheet consists of more than one tab in it. Can we amend the macro to extract all the tabs in such cases

Regards,
JD

[Aussiebear]

I don't know about others, but I hate having to download a file to find the section of code that is causing an error. In future please try to present the section of code so that others can be a little more internet safe.



Function Get_File_Names(MyPath As String, Subfolders As Boolean, _
ExtStr As String, myReturnedFiles As Variant) As Long

Dim Fso_Obj As Object, RootFolder As Object
Dim SubFolderInRoot As Object, file As Object
'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If
'Create FileSystemObject object
Set Fso_Obj = CreateObject("Scripting.FileSystemObject") '<=== errors out on this line
Erase myFiles()
Fnum = 0
'Test if the folder exist and set RootFolder
If Fso_Obj.FolderExists(MyPath) = False Then
Exit Function
End If
Set RootFolder = Fso_Obj.GetFolder(MyPath)
'Fill the array(myFiles)with the list of Excel files in the folder(s)
'Loop through the files in the RootFolder
For Each file In RootFolder.Files
If LCase(file.Name) Like LCase(ExtStr) Then
Fnum = Fnum + 1
ReDim Preserve myFiles(1 To Fnum)
myFiles(Fnum) = MyPath & file.Name
End If
Next file


'Loop through the files in the Sub Folders if SubFolders = True
If Subfolders Then
Call ListFilesInSubfolders(OfFolder:=RootFolder, FileExt:=ExtStr)
End If


myReturnedFiles = myFiles
Get_File_Names = Fnum
End Function

[Jagdev]

Hi Aussiebear

Sorry for the trouble.

I am facing the above error in the code highlighted below.


Sub Get_Sheet(PasteAsValues As Boolean, SourceShName As String, _
SourceShIndex As Integer, myReturnedFiles As Variant)
Dim mybook As Workbook, BaseWks As Worksheet
Dim CalcMode As Long
Dim SourceSh As Variant
Dim sh As Worksheet
Dim I As Long
'Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
On Error GoTo ExitTheSub
'Add a new workbook with one sheet
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

'Check if we use a named sheet or the index
If SourceShName = "" Then
SourceSh = SourceShIndex
Else
SourceSh = SourceShName
End If
'Loop through all files in the array(myFiles)
For I = LBound(myReturnedFiles) To UBound(myReturnedFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(myReturnedFiles(I))
On Error GoTo 0
If Not mybook Is Nothing Then
'Set sh and check if it is a valid
On Error Resume Next
Set sh = mybook.Sheets(SourceSh)
If Err.Number > 0 Then
Err.Clear
Set sh = Nothing
End If
On Error GoTo 0
If Not sh Is Nothing Then
sh.Copy after:=BaseWks.Parent.Sheets(BaseWks.Parent.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0
If PasteAsValues = True Then
With ActiveSheet.UsedRange
.Value = .Value
End With
End If
End If
'Close the workbook without saving
mybook.Close savechanges:=False
End If
'Open the next workbook
Next I
' delete the first sheet in the workbook
Application.DisplayAlerts = False
On Error Resume Next
BaseWks.Delete
On Error GoTo 0
Application.DisplayAlerts = True
ExitTheSub:
'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub

[Jagdev]

Hi Experts

I am able to fix the above code by removing the error code. Is is working fine now.

.value = .value

Could someone please help me with the other query. Currently the macro is useful to extract the data from different sheets that to the first tab only. Is it possible to tweak the code to ensure that the macro extract all the tabs data from the sheets.

Regards,
JD

[Jagdev]

Hi Experts,

I think the code which needs to tweak to extract the data from all the tabs laise here


Sub Get_Sheet(PasteAsValues As Boolean, SourceShName As String, _
SourceShIndex As Integer, myReturnedFiles As Variant)
Dim mybook As Workbook, BaseWks As Worksheet
Dim CalcMode As Long
Dim SourceSh As Variant
Dim sh As Worksheet
Dim I As Long
'Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
On Error GoTo ExitTheSub
'Add a new workbook with one sheet
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

'Check if we use a named sheet or the index
If SourceShName = "" Then
SourceSh = SourceShIndex
Else
SourceSh = SourceShName
End If
'Loop through all files in the array(myFiles)
For I = LBound(myReturnedFiles) To UBound(myReturnedFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(myReturnedFiles(I))
On Error GoTo 0
If Not mybook Is Nothing Then
'Set sh and check if it is a valid
On Error Resume Next
Set sh = mybook.Sheets(SourceSh)
If Err.Number > 0 Then
Err.Clear
Set sh = Nothing
End If
On Error GoTo 0
If Not sh Is Nothing Then
sh.Copy after:=BaseWks.Parent.Sheets(BaseWks.Parent.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0
If PasteAsValues = True Then
With ActiveSheet.UsedRange
.Value = .Value
End With
End If
End If
'Close the workbook without saving
mybook.Close savechanges:=False
End If
'Open the next workbook
Next I
' delete the first sheet in the workbook
Application.DisplayAlerts = False
On Error Resume Next
BaseWks.Delete
On Error GoTo 0
Application.DisplayAlerts = True
ExitTheSub:
'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub

[Jagdev]

Hi Experts

Please help me to amend the above code to exact data from all the sheets and tab instead of exacting the first tab. The current code is extracting the first tab only from the sheet.

Regards
JD