Copy from a workbook without knowing its full name

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
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

Code:
mm RED[SIZE=1][I]1234567[/I][/SIZE] ddmmyy.xls
and the sheet name will be
Code:
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..

Code:
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.
Code:
 Sheets("INVOICE").Cells.ClearContents


is it possible to run clearcontents without losing the clipboard?
 
Last edited:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can loop:

Code:
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

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
If I want to expand that to then loop through the sheets wb...

I tried...



Code:
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
Code:
For Each wbs In wb

"object doesn't support this property or method"
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You use the function to get the workbook, then loop through it:

Code:
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

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,026
Messages
5,526,325
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top