• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error in getting values from a sheet with macro

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: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?

72748

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:
ferocious12
Hint: Based Your picture there are some challenges to test nor verify Your challenge with ... an Excel-file.


You are right. Can you help me to to add a line to change the above code after following line (that copies the "Data Sheet" worksheet from source file to current workbook) to copy from source worksheet range ("AH8:AK") to the copied "Data Sheet" worksheet in the destination file? Thanks

>>> use code - tags <<<
Code:
Sheets(wSht).Copy Before:=ThisWorkbook.Sheets(1)
 
Back
Top