CODEWITHSUNDEEP
vbaexcel
AayushmanR
AayushmanR

Reputation: 63

Copy a specific worksheet from multiple workbooks without openeing the workbook

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

Upvotes: 2

Views: 1251

Answers (3)

gr8tech
gr8tech

Reputation: 174

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

Upvotes: 0

Ulysses
Ulysses

Reputation: 6015

I am assuming that you don't want to display the opened workbook to the user so the operation is not visible on screen.

If that's the case, you can use the following line before your code 

  Application.ScreenUpdating = False

  'open the new/target excel workbook and put all the sheets in there

And following after:

  Application.ScreenUpdating = True

Upvotes: 0

0m3r
0m3r

Reputation: 12499

Work with Workbooks.Open Method to open it in in the background, and hide any alerts with Application / ScreenUpdating / EnableEvents / DisplayAlerts

Application.ScreenUpdating Property (Excel) 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 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 is usually a better choice.

Upvotes: 1

Related Questions