Copy from a workbook without knowing its full name

kgkev

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

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,963
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,251
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
38,963
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,251
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,108
Messages
5,857,428
Members
431,879
Latest member
KiwDaWabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top