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

J

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.
 
J

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
 
J

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

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*"
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top