How do I reference a workbook using a codename?

toddot04

New Member
Joined
Jul 1, 2015
Messages
13
So here is my problem. I have 2 open workbooks. I need to copy a set of cells and paste it to a sheet in another workbook. I have to be able to reference the code-name because the file name is always changing because of users needing to update it.

Also in the best case I would like to be able to have it look into only the open workbooks because more then likely their will be lots of the same codename because of identical wb from other jobs on someone computer.

So as it stands I have:

DRun.ActiveSheet.Range("A4:k7").copy
Activewb("MR").Inventory.Range("A3").paste

Always comes back with an error.

I have no idea how to get this to work. New here and going crazy.
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
So here's a theoretical answer to maybe prod one of the experts along using Named Ranges......
Couldn't you just select the entire workbook as a range and then give the codename to that range? I guess maybe that only works if you know "where" to look for the range, which is probably the ever changing file name....
I wish i were handier at this to give more help than that. All of the work i've done is "in" the workbook vs "on" the workbook.

in "laymans VBA" after naming your range, then maybe it reads something like:
Code:
DRun.ActiveSheet.range("A4.k7").copy
Activewb("").Range("codename").Select  'change codename to whatever you want
With Selection Cells.("A3").Paste
End With
that's probably horrible code. Please do NOT try it on real data. Again this is theoretical - sorry if i'm wasting your time.
 
Upvote 0
I can look into, because I haven't looked at it that way. Thanks for some help :)


So this is what i came up with.

Sub Test()


Dim MR As Workbook
Dim myFileName As Variant
Dim SCR As Range, TGT As Range


Set SCR = Sheets("Run Report").Range("A4:k20")


myFileName = Application.GetOpenFilename
If myFileName = False Then
Exit Sub 'user hit cancel
End If


Set MR = Workbooks.Open(Filename:=myFileName)


MR.Sheets("Inventory").Select


Set TGT = Range("A3").Resize(SCR.Rows.Count, SCR.Columns.Count)


End Sub


But it doesn't copy over the SCR to TGT. it just opens the workbook and slect the right tab. I need help fixing this code now
 
Upvote 0
You might want to look at this reference
http://www.mrexcel.com/forum/excel-questions/253546-worksheets-info-reference-loop-add-etcetera.html

You also might be interested in this UDF that, given a workbook and a string, returns the worksheet in that workbook whose codename is the string

Code:
Function SheetFromCodeName(strCodeName As String, Optional wb As Workbook) As Worksheet
    If wb Is Nothing Then Set wb = ThisWorkbook
    
    With wb
        Set SheetFromCodeName = .Sheets(.VBProject.VBComponents(strCodeName).Properties("Index").Value)
    End With
End Function
 
Upvote 0
I don't think I know enough to apply your UDF.

Would That go with my code? If it does do I add it to the start of end?
Also, does that function go into the wb of both open workbooks?

I hate asking people for this kind of help, but I see this has a sharp learning curve for me.
 
Upvote 0
I misinterpret the OP. CodeName is a property of a worksheet, not a workbook or file.

As I understand it, you have a bunch of open workbooks. The users keep changing the names of those workbooks. And you want to reference one of those workbooks, no matter what changes the user makes to its name.

If you were to loop through the open workbooks, looking for your particular one, what would indicate that you have found the one that you want?

You could create the equivalent of the CodeName property of a workbook by using Custom Document Properties.
But, for the user to change a workbook's name, they have to use SaveAs. This makes two different workbooks.
 
Last edited:
Upvote 0
They user would only have 2 open workbooks. But closed wbs would be in the same folder with the open ones that have the same WB codename. The wb name's changes with each job.

Also the Active workbook with the macro is DRUN. The other workbook's codename is MR.

I don't know what would indicate that I found the right one besides the name.xls would be job# MorningReport.
 
Last edited:
Upvote 0
Creating a new file would be too complicated by these users as this macro is bypassing a user issues.
 
Upvote 0
If they are changing the name of an Excel file, they are either using SaveAs or they are chaining the name using the OS features.
What inside the workbook tells you that its the one that you are looking for?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top