Copy certain sheet into a new excel workbook from multiple workbooks. 2

[robles74]

Good afternoon to everyone.
**Attending the Skip's suggestion I move this thread to this forum**

Again I need your help, I try to do myself but I can't.

I need to copy certain sheet into a new excel workbook from multiple workbooks, I have near to hundred "production reports" in one folder in a shared drive inside the production reports I have several sheets and until now I was able to do this task with the sheets named "ElectReport" and with the sheets named "Time_Out" everything fine. Now I need to retrieve another sheet BUT this tab/sheet varies in how is named, this sheet SHOULD be "named" as the employee number (8 digits ie. 20153280), the task for my code works fine because all the tabs for ElectReport has "ElectReport" and when is copied/pasted in the new workbook simply puts "ElectReport" for the first tab copied "ElectReport1" for the second, "ElectReport2" for the third and so on, the same for the "Time_Out" tabs. But due a different employee numbers my macro does not work, here is the code:

Dim objExcel As Excel.Application
Set objExcel = CreateObject("excel.application")
objExcel.Workbooks.Add
objExcel.Visible = True
'''******

'''******

'
'
    Application.ScreenUpdating = True
    Application.Visible = True
        Application.EnableEvents = False
       Dim X As Variant
       X = Application.GetOpenFilename _
           ("All File extensions (*.xl*), *.xl*", 2, "Open Files", , True)
        If IsArray(X) Then
           Workbooks.Add
           A = ActiveWorkbook.Name
       For y = LBound(X) To UBound(X)
       Application.StatusBar = "Importing Files: " & X(y)
         Workbooks.Open X(y)
         b = ActiveWorkbook.Name
           For Each Hoja In ActiveWorkbook.Sheets(Array("ElectReport")) 'here is the problem I think
            Hoja.Copy After:=Workbooks(A).Sheets(Workbooks(A).Sheets.Count)
           Next
           Workbooks(b).Close False
       Next
       Application.StatusBar = "Ready"
    End If
    Range("a1").Select
    Call UnhideAllSheets
    Call UnprotectAll
    Call DelEmptySheets
    MsgBox ("yes no")
    Call RenameSheets
    Call Sort_Active_Book
    ActiveWorkbook.Worksheets(1).Activate
        Application.Goto Reference:="R1C1"
    Application.ScreenUpdating = False
End Sub

Any help with this will be really appreciated.

Thanks in advance and best regards.

Hi,

This question would be best addressed in forum707: VBA Visual Basic for Applications (Microsoft). This forum is not designed for VBA questions. Please repost and then delete this thread.

Please tell us how you will know that a sheet name is an employee number. Are there other sheets in these workbooks that have known names where the logic might be any sheet other than this list of known sheet names?

Answering the first Skip's question I know the sheet name is the employee number because I design the book to accept his/her employee number taking the number from the system (windows) login info, each user/employee has his/her own login and it is PROHIBITED to use other user name.

For the second question, YES the workbook have 9 (nine) sheets in, no one change the name everyone remains with the same name all the time the only one who change the name is the Employee number sheet the names for ALL sheets are: AllInfo, ToDetAction, ToDetQueue, ExtractedInfo, ToCons, ElectReport, Time_Out, LastConct and the sheet with changing name/numbers.

Again thanks in advance.

 

[SkipVought]

Hi,

What application are you coding in?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[robles74]

Excel 2010

 

[SkipVought]

I suspected so. If you're coding in Excel, then you do not need to declare or create the Excel Application object.

Sub test()
    Dim wbMASTER As Workbook
    Dim X As Variant
    Dim ws As Worksheet
    Dim y As Integer
'    objExcel.Workbooks.Add    [b]<<<< Why are you adding a workbook here?[/b]
'''******

'''******

'
'
    Application.ScreenUpdating = True
    Application.Visible = True
    
    X = Application.GetOpenFilename _
           ("All File extensions (*.xl*), *.xl*", 2, "Open Files", , True)
    If IsArray(X) Then
        Set wbMASTER = Workbooks.Add
        
        For y = LBound(X) To UBound(X)
            Application.StatusBar = "Importing Files: " & X(y)
            With Workbooks.Open(X(y))
                For Each ws In .Worksheets
[highlight #FCE94F]'[b]THIS is where you determine which sheet is the employee sheet: any sheet name NOT in the list yuo provided[/b]
                    Select Case ws.Name
                        Case "AllInfo", "ToDetAction", "ToDetQueue", "ExtractedInfo", "ToCons", "ElectReport", "Time_Out", "LastConct"
                        Case Else
                            ws.Copy After:=wbMASTER.Sheets(wbMASTER.Sheets.Count)
                    End Select[/highlight]
                Next
                .Close False
            End With
        Next
        Application.StatusBar = "Ready"
    End If
    Call UnhideAllSheets
    Call UnprotectAll
    Call DelEmptySheets
    MsgBox ("yes no")
    Call RenameSheets
    Call Sort_Active_Book
    Application.ScreenUpdating = False
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Some remarks:

- you don't need first four lines of code, you are already in excel and another instance is nowhere used,

- [tt]Workbooks.Add[/tt] and [tt]Workbooks.Open( )[/tt] are functions that return workbook, it's more robust to use it rather than [tt]ActiveWorkbook.Name[/tt]:

Dim wbTmp As Workbook
Set wbTmp=Workbooks.Add

- if you need to loop through sheets, use collection, not single object. If you plan to copy worksheets rather than all sheets, use worksheets collection. For single sheet there is no need to use [tt]For Each...Next[/tt]:

ActiveWorkbook.Sheets("ElectReport").Copy After:=Workbooks(A).Sheets(Workbooks(A).Sheets.Count)

To copy all sheets, what you probably need:

For Each Hoja In ActiveWorkbook.Sheets
    Hoja.Copy After:=Workbooks(A).....

You may test if Hoja is a worksheet you want to copy first.
BTW, you can use [tt]For Each...Next[/tt] to loop through array

For Each y in X
    Application.StatusBar = "Importing Files: " & y
    Workbooks.Open y

- if you need specific name for copied sheet, you can for instance change name referring to it by index, in the code above:

ActiveWorkbook.Sheets("ElectReport").Copy After:=Workbooks(A).Sheets(Workbooks(A).Sheets.Count)
Workbooks(A).Sheets(Workbooks(A).Sheets.Count).Name="20153280"

- [tt]MsgBox ("yes no")[/tt] is rather annoying.

combo

 

[robles74]

Skip and Combo I don't have enough words to say thank you. Finally I have an approach to my goal.

Let me explain some points in the thread.

As you can see I'm very novice with VBA and this code is a "collection" of many examples in the web, my own experience and common sense.

This report (the production report) should be the root to many, many other reports in my project/work like: time out reports, payroll files (based on times and production), access databases, print many reports for the employees (how much expect to receive as payment) and many others.

That's the reason why I need to "create" another excel instance/workbook in the beginning of code, also when I finally (for example) have all the "ElectReports" I need to perform many other tasks/subroutines linked to. Then here is the explanation to the annoying message box this one is a kind of "check point" or error handler in the case of mistake by one or various employees.

At this time I have more clear what I can do with this report and it was thanks to your support.

Again many thanks.

Now the final question, how can I close this thread?

 

[SkipVought]

Now the final question, how can I close this thread?"

You have stated a resolution and offered your thanks. Therefore it is presumed complete.

However, it remains open, as other members may comment of add their suggestions. At some point in the future, management will close the thread an make it un-editable.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]