Question: I have a code below which copies a specific worksheets form all active or open workbooks. 

But how to copy the same Worksheet without opening the workbooks, like if we can provide the path in the code , it should be able to pick the given worksheet from all the workbooks form that path. 

Below is the code that am currently using.

```
Sub CopySheets1()
    Dim wkb As Workbook
    Dim sWksName As String

    sWksName = "SHEET NAME"

    For Each wkb In Workbooks
        If wkb.Name <> ThisWorkbook.Name Then
            wkb.Worksheets(sWksName).Copy _
            Before:=ThisWorkbook.Sheets(1)
        End If
    Next

    Set wkb = Nothing
 End Sub

```

Answer: 
*Work with **[Workbooks.Open Method](https://msdn.microsoft.com/en-us/library/office/ff193498.aspx)** to open it in in the background, and hide any alerts with Application / ScreenUpdating / EnableEvents / DisplayAlerts* 

---

> 
>  [Application.ScreenUpdating Property (Excel)](https://msdn.microsoft.com/en-us/library/office/ff193498.aspx) Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster.
> 
> 
> 

---

*Example*

```
Sub CopySheets1()
    Dim wkb As Workbook
    Dim sWksName As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With

    wkb Workbooks.Open("C:\temp\bookname.xls")

    sWksName = "SHEET NAME"

    For Each wkb In Workbooks
        wkb.Worksheets(sWksName).Copy _
        Before:=ThisWorkbook.Sheets(1)
    Next

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With

    Set wkb = Nothing
 End Sub

```

---

*Assuming your folder name is `C:\Temp\` then Loop until folder returns an empty*

---

*Example* 

```
    Dim FileName As String
    ' Modify this folder path as needed
    FolderPath = "C:\Temp\"
    ' Call Dir the first time to all Excel files in path.
    FileName = Dir(FolderPath & "*.xl*")

    ' Loop until Dir returns an empty .
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set wkb = Workbooks.Open(FolderPath & FileName)

        '--->> Do your copy here

        ' Close the source workbook without saving changes.
        wkb.Close savechanges:=False

        ' next file name.
        FileName = Dir()
    Loop

```

> 
> Use a [Do...Loop](https://msdn.microsoft.com/en-us/library/eked04a7.aspx) structure when you want to repeat a set of statements an indefinite number of times, until a condition is satisfied. If you want to repeat the statements a set number of times, the [For...Next Statement](https://msdn.microsoft.com/en-us/library/5z06z1kb.aspx) is usually a better choice.
> 
> 
>
It seems then that you have to manually open the workbooks. You can automate this process in VBA as follows;

```
Sub CopySheets1()
Dim wkb As Workbook
Dim dirPath As String ' Path to the directory with workbooks
dim wkbName as String

dirPath="C:\folder\"

sWksName = "SHEET NAME"

wkbName=Dir(dirPath & "*.xlsx") 

```

For example:
dirPath = "C:\folder\"
So that the input to the Dir function be like "C:\folder\*.xlsx"

```
Application.DisplayAlerts = False
do while wkbName <> ""
    Set wkb=Application.Workbooks.Open(dirPath & wkbName)
            wkb.Worksheets(sWksName).Copy _
            Before:=ThisWorkbook.Sheets(1)
    wk.Close False
    wkbName = Dir
loop
Application.DisplayAlerts = True

End Sub

```