Copy from a workbook without knowing its full name

[kgkev]

I would like to copy the data from a work book that I know is open, however i do not know the full name of the workbook

I do know the work book name will be in the format

mm RED[SIZE=1][I]1234567[/I][/SIZE] ddmmyy.xls

and the sheet name will be

RED[SIZE=1]1234567[/SIZE]

Using just this RED indicator is it possible to copy the entire contents of this sheet?




Alternative option.....I used to manually copy this data and then run a macro..

Sub Stage1()
Application.ScreenUpdating = False
Sheets("INVOICE").Cells.ClearContents
'On Error GoTo Handler


Sheets("INVOICE").Range("A1").PasteSpecial xlValues
.
.

This has worked well, but now this line cancelled the copied cell so the pastespecial runs into an error.

 Sheets("INVOICE").Cells.ClearContents

is it possible to run clearcontents without losing the clipboard?

 

[RoryA]

You can loop:

Function GetWorkbookByName(theName as string) as workbook
dim wb as workbook
for each wb in application.workbooks
if ucase$(wb.name) like "*" & ucase$(theName & "*") then
    set getworkbookbyname = wb
    exit for
end if
next wb
end function

 

[kgkev]

If I want to expand that to then loop through the sheets wb...

I tried...

Function GetWorkbookByName(theName As String) As Workbook
Dim wb As Workbook
Dim wbs As Worksheet
For Each wb In Application.Workbooks
If UCase$(wb.Name) Like "*" & UCase$(theName & "*") Then
    For Each wbs In wb
        If UCase$(wbs.Name) Like "*" & UCase$(theName & "*") Then
        Set GetWorkbookByName = wbs
        Exit For
        End If
    Next wbs
End If
Next wb
End Function

but got an error on

For Each wbs In wb

"object doesn't support this property or method"

 

[RoryA]

You use the function to get the workbook, then loop through it:

Dim wb as workbook 
set wb = GetWorkbookByName("RED#######")
If not wb is nothing then
   dim ws as worksheet
    for each ws in wb.worksheets
       ' do something
    next
end if

 

[kgkev]

Struggled to make this work, turns out the file was in protected view. it seems excel doesn't see this as a current Workbook

Got it working now though, thanks