Copy Sheet from Open Workbook

[uncled]

I built an Excel reporting application that will create a Summary worksheet report on the fly. However, I want to allow the user to copy a previous Summary worksheet from another Open workbook (or closed) instead of generating a new Summary sheet.

So basically, I would like to cycle through any open workbooks looking for a worksheet titled "Summary" and if found, copy into the active/current workbook, otherwise, generate on the fly.

I can't figure out how to cycle through open workbooks to search for the Summary sheet.

thanks in advance for any advice!!

 

[PHV]

I can't figure out how to cycle through open workbooks
A starting point:
For Each wb in Application.Workbooks

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[uncled]

Thanks for the tip PHV, got me going in the right direction! However, now I'm trying to cycle through the worksheets of each open workbook to determine whether a worksheet exists. Here are the loops I have...I can't figure out how to reference the workbook and it's associated worksheets (i've currently got ActiveWorkbook in the code which is only reference the worksheets in my target workbook, not the other open workbooks)

For Each wb In Excel.Application.Workbooks
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "PIPELINE" Then
Worksheets("PIPELINE").Copy After:=ActiveSheet
Worksheets("PIPELINE").Name = myWrksheet
End If
Next
Next

Thanks again for any assistance!

 

[SkipVought]

For Each wb In Excel.Application.Workbooks
        For Each ws In [s]ActiveWorkbook[/s] [b]wb[/b].Worksheets
            If ws.Name = "PIPELINE" Then
                [b]ws[/b].Copy After:=ActiveSheet
                [b]ws[/b].Name = myWrksheet
            End If
        Next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[uncled]

Thanks Skip...that got me going in the right direction, howver, I'm not sure what I'm doing wrong here, but I can't seem to get the targeted worksheet copied to the main file. I've declared CurrentWorkbook as a String and assigned the source workbook. When I attempt the Copy command, I get a subscript out of range error and can't seem to figure out how to tell the program where to copy the worksheet.

Here's what I've got

For Each wb In Excel.Application.Workbooks
For Each ws In wb.Worksheets
If ws.Name = "PIPELINE" Then
Sheets("PIPELINE").Copy Before:=Workbooks(CurrentWorkbook).Sheets(1)
Worksheets("PIPELINE").Name = myWrksheet
End If
Next
Next

thanks!

 

[SkipVought]

Why are you using Sheets("PIPELINE") and Worksheets("PIPELINE"), UNQUALLIFIED to a workbook, when ws IS THAT SHEET???

            If ws.Name = "PIPELINE" Then
                ws.Copy Before:=Workbooks(CurrentWorkbook).Sheets(1)
                ws.Name = myWrksheet
            End If

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]