Copy range from one open workbook's worksheet to another.

[J.W. Aldridge]

Check to see if a workbook entitled "All_to_One...." is open.
("..." represents other miscellaneous wording).

If open, select 1st worksheet in this workbook. (Will only be one
worksheet in workbook.)

Copy this range.
Range("a4:f4").Select
Range(Selection, Selection.End(xlDown)).Select

Go to workbook entitled "Apples and oranges"
Go to sheet "blueberries"
Paste at range a4:f4.

 

[Joel]

Set DestSht = Workbooks("Apples and oranges") _
.Sheets("blueberries")
For Each bk In Workbooks
If UCase(bk.Name) = "ALL_TO_ONE...." Then
LastRow = bk.Range("A4").End(xlDown).Row
bk.Range("B4:F" & LastRow).Copy _
Destination:=DestSht.Range("A4")
Exit For
End If
Next bk

 

[Joel]

I forgot to include a sheet reference in the All_toOne workbook

Set DestSht = Workbooks("Apples and oranges") _
.Sheets("blueberries")
For Each bk In Workbooks
If UCase(bk.Name) = "ALL_TO_ONE...." Then
With bk.Sheets(1)
LastRow = .Range("A4").End(xlDown).Row
.Range("B4:F" & LastRow).Copy _
Destination:=DestSht.Range("A4")
Exit For
End If
Next bk

 

[J.W. Aldridge]

the latter part of the workbook name changes.
Is this the correct way to say that in this code?

"ALL_TO_ONE*"

 

[Patrick Molloy]

you've already got another thread re this

however for exact likes you'd use "=" but where the sheet names begin the
same but end differently, then yes, the aterisk is the 'any character'
symbol and you use it with the 'LIKE' condition

as given in Rick Rothstein's reply to your "Search Open Workbooks..."
question

so

IF mytext LIKE "ABC*" THEN

END IF

would be true for text string starting with "ABC" such ABCD and ABCFG