Making the run of Macro not visible

**kaffal** · 03-08-2009, 09:21 AM

Hi all, I have a macro that will locate files in a folder, then it will copy the worksheets and paste into the current workbook.

However It need to open up to 100 workbooks and copy and paste into the new workbook. During the run of the macro, I can see files opening and closing on the toolbar. is that possible to make those opening and closing of files not seen by the user?

Pls Advise.

Sub RunCodeOnAllXLSFiles()
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   Application.EnableEvents = False


    'Macro code to copy from multiple workbooks to current workbook

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True

End Sub

**royUK** · 03-08-2009, 09:49 AM

Does the rest of your code activate or select the workbooks?

**kaffal** · 03-08-2009, 10:14 AM

yes, the macro works like this. It will search through the folder for file name containing a cell value. Once Found, it will open the workbook and copy the entire sheet and paste to the new workbook. Once copied, it will close the workbook and continue to search for files in the directory till all are compared.

If the filename does not contain what the cell value specify, it will not open the files.

If we can see the opening and closing of the workbooks during the macro run, is that normal ? or is that a way to hide those ?

**royUK** · 03-08-2009, 10:38 AM

You don't need to activate or select these workbooks and/or sheets - this will result in faster, more efficient ode & remove theflashing effect.

**kaffal** · 03-08-2009, 11:44 AM

Hi royUK,
sorry I new to VB, I dun get what you mean
Maybe you could take a look at my code and comment

If .Execute > 0 Then 'Workbooks in folder

              For lCount = 1 To .FoundFiles.Count 'Loop through all.
                 'Open Workbook x and Set a Workbook variable to it
                 Set wbResults = Workbooks.Open(FileName:=.FoundFiles(lCount), UpdateLinks:=0)


                 'DO YOUR CODE HERE
                  TabName = TabNam & Range("A1").Value
                  EmptyCell = Range("B3").Value
                  If EmptyCell <> "" Then
                     ActiveSheet.Name = TabName
                                  
                     Sheets(TabName).Copy After:=wbCodeBook.Sheets(1)
                     i = i + 1
                                                        
                     wbCodeBook.Sheets("Menu").Cells(i, 2).Value = "Completed"
                     wbCodeBook.Sheets("Menu").Cells(i + 10, 3).Value = TabName
                  
                  
                  End If
                  wbResults.Close SaveChanges:=False
                 Next lCount
                 
            End If