0

I am trying to copy a specific worksheet (called "Edit") from a folder full of workbooks (190). I have the following code to do this: 

Option Explicit
Sub CombineSheets()
    Dim sPath As String
    Dim sFname As String
    Dim wBk As Workbook
    Dim wSht As Variant

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    sPath = InputBox("M:\Employee Information\Peter Young\Msc Project\1 - 181028 - Office First Floor\MacroCopy")
    ChDir sPath
    sFname = InputBox("*")
    sFname = Dir(sPath & "\" & sFname & ".xl*", vbNormal)
    wSht = InputBox("Edit")
    Do Until sFname = ""
        Set wBk = Workbooks.Open(sFname)
        Windows(sFname).Activate
        Sheets(wSht).Copy Before:=ThisWorkbook.Sheets(1)
        wBk.Close False
        sFname = Dir()
    Loop
    ActiveWorkbook.Save
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

When I run the Macro, I get the dialog box asking to name the sheet to copy from all matching workbooks. I enter Edit, and get the message

Run-time error '76': Path not found

Can anyone tell me what needs to be changed in the code to make this work.?

Mesut Akcan
  • 899
  • 7
  • 19
Peter Young
  • 49
  • 7
  • Welcome to SO. Please, specify which line raises the error. Also, when you execute `Workbooks.Open(sFname)` , can you tell what is the value of `sFname`? – Foxfire And Burns And Burns Nov 23 '18 at 09:51
  • I think the error is in ChDir sPath, when I debug - run to cursor this is the line that is highlighted. I'm not quite sure what you mean about the second point, could you explain further. Is this something that I should define? – Peter Young Nov 23 '18 at 10:18
  • What is the value stored in `sFname` when you execute the code? Try debugging your code. Execute it with F8 (Step by step) and the code will go line by line. By holding the cursor steady on a variable, you can see the value of the variable :) . Please, check this link to learn how to debug code https://www.excel-easy.com/vba/examples/debugging.html – Foxfire And Burns And Burns Nov 23 '18 at 10:49
  • Ok, when I execute line by line it seems to get to ChDir sPath then comes up with the run time error above. The value of Workbooks.Open(sFname) = "" – Peter Young Nov 23 '18 at 11:57
  • Then `sFname` is not getting the value you need. You must hanlde it in the previous lines. Does `sFname = Dir(sPath & "\" & sFname & ".xl*", vbNormal)` work properly with that `*`? Also, if you want to loop trought files of a specific folder, check [Loop through files in a folder using VBA?](https://stackoverflow.com/a/45749626/9199828) – Foxfire And Burns And Burns Nov 23 '18 at 12:49
  • Also, line `Sheets(wSht).Copy Before:=ThisWorkbook.Sheets(1)` should be `wBk.Worsheets("Edit").Copy Before:=ThisWorkbook.Sheets(1)` When you are handling different workbooks at same time, make sure you reference the right workbook. If you just invoke something like `Sheets(wSht)`, it will interact with the activeworkbook. It's easier adding which workbook you want to invoke. – Foxfire And Burns And Burns Nov 23 '18 at 12:52
  • From what I read the * was to denote all workbooks in the folder. Does this seems right? I can I tell if those two lines aren't working together. I don't need them to process in a specific order, just all workbooks in the folder. I should probably say I don't really know anything about VBA. – Peter Young Nov 23 '18 at 13:02
  • You must loop trough every file in the folder, check if it's an Excel File before open it, and then, if it's an Excel file, open it and get the worksheet Edit you want. About Stack Overflow, we do not code for free. We help answering specific coding problems. So I kindly suggest you to code something for yourself, (you can use the code I linked in previous comment) and then adapt it to your needs. – Foxfire And Burns And Burns Nov 23 '18 at 13:41

0 Answers0