ferocious12
Member
Hi All
I am using following macro to extract specific worksheet "Data Sheet" from all workbooks saved in particular location. Last 3 columns in extracted worksheets is giving me value error (these columns have sumif formula =SUMIF('[PC location of file] Summary'!C:C,E14,'[PC location of file] Summary'!P).
I have similar formula within same worksheet referring to file saved on same location of my PC but it is not giving me "Value Error" while this particular column does. There is no error in the first extracted worksheet but from 2nd worksheet onwards.
Any idea why is it the case?
I am using following macro to extract specific worksheet "Data Sheet" from all workbooks saved in particular location. Last 3 columns in extracted worksheets is giving me value error (these columns have sumif formula =SUMIF('[PC location of file] Summary'!C:C,E14,'[PC location of file] Summary'!P).
I have similar formula within same worksheet referring to file saved on same location of my PC but it is not giving me "Value Error" while this particular column does. There is no error in the first extracted worksheet but from 2nd worksheet onwards.
Any idea why is it the case?
Code:
Sub CombineSheets()
Dim sPath As String
Dim sFname As String
Dim wBk As Workbook
Dim wSht As Variant
For Each wSht In Worksheets
If wSht.Name = "Master" Then
Application.DisplayAlerts = False
Sheets("Master").Delete
Application.DisplayAlerts = True
End
End If
Next
Sheets.Add(Before:=Worksheets(1)).Name = "Master"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
sPath = InputBox("Enter a full path to workbooks")
ChDir sPath
sFname = InputBox("*")
sFname = Dir(sPath & "\" & sFname & ".xl*", vbNormal)
wSht = InputBox("Data Sheet")
Do Until sFname = ""
Set wBk = Workbooks.Open(sFname)
Windows(sFname).Activate
Sheets(wSht).Copy Before:=ThisWorkbook.Sheets(1)
wBk.Close False
sFname = Dir()
ActiveSheet.Range("AH8:AK").Copy
ActiveSheet.Range("AH8:AK").PasteSpecial xlPasteValues
ActiveSheet.Range("AH8:AK").PasteSpecial xlPasteFormats
Loop
ActiveWorkbook.Save
Application.EnableEvents = True
Application.ScreenUpdating = True
Call Remove_Filter_Allwks
End Sub
Public Sub Remove_Filter_Allwks()
Dim sht As Worksheet
For Each sht In Worksheets
If sht.AutoFilterMode = True Then
Debug.Print sht.Name
sht.AutoFilterMode = False
End If
Next
End Sub
Last edited by a moderator: