MACRO help to repeat copy paste save

[davin2929]

Hi,

Does anyone know if it's possible to open each file in a folder and copy a worksheet in each of the files? The names of the files vary but are located within one folder. I would like to grab a worksheet named "Sales Summary" out of each of the .xls files within the folder.

So if there were 12 files in a folder I would like to grab 12 Sales Summary worksheets, 1 out of each of the files.

 

[Joe4]

Sure.

I am actually helping someone who is opening all the Excel files from a certain directory (http://www.mrexcel.com/forum/showthread.php?t=580800).

Starting from something like that, we can build this to do what you want:

Sub MyCopySheets()
 
    Dim wbOrig As Workbook
    Dim wbOpen As Workbook
    Dim strPath As String
    Dim strExtension As String
    
    Application.ScreenUpdating = False
 
'   Capture current workbook
    Set wbOrig = ActiveWorkbook
 
'   Set path to open Excel files from
    strPath = "G:\C\Temp\"
    
    ChDrive Left(strPath, 3)
    ChDir strPath
    strExtension = Dir("*.xls*")
 
'   Loop through all files in directory
    Do While strExtension <> ""
'   Open workbook
        Set wbOpen = Workbooks.Open(strPath & strExtension)
        wbOpen.Sheets("Sales Summary").Activate
        Cells.Copy
'   Insert new worksheet and paste
        wbOrig.Activate
        Sheets.Add After:=Sheets(Sheets.Count)
        Range("A1").Activate
        ActiveSheet.Paste
        Application.CutCopyMode = False
'   Go back and close file that we copied from
        wbOpen.Close SaveChanges:=False
        strExtension = Dir
    Loop
 
    Application.ScreenUpdating = True
 
End Sub