Use Cell Value in Code

[ETracker]

I would like to know if it is possible to use the valve from a cell valve in the code to reference an open file to select and to run the code on.

An example world be a file that would contain the code and would use cell A2 to run the code to that file that is already open. The file name could look like 98123-CB-R05

The line of code using now is:

Windows("98123-CB-R05.xls").Activate

Would like it to be somthing like:

Windows("cellvalve A2").Activate

Not sure if this is possible, but would like to know if it is and what would the code look like.

Thanks for any help
ETracker

[GTO]

By example and presuming that the sheet (where the referenced cell A2 resides) is named "References":

Windows(WorkSheets("References").Cells(2,1).Value).Activate

Or, you could refer to the workbook:

Workbooks(Worksheets("References").Cells(2, 1).Value).Activate

Hope this helps,

Mark

[ETracker]

Thanks Mark for the quick response.

I have tried both and for some reason I can not get it to activate the second workbook in the second workbook. I may need to reference a sheet in the activated workbook, not sure.

What I am trying to do is to have the code and reference in one workbook or lets call it the base workbook and have the file of the second workbook name in cell A2, note this second workbook will already be open, I just need to be able to reference the second workbook from the base workbook to be able to run the code on the referenced workbook. This will allow me to have the code in the base workbook and not placed in the referenced workbook. Hopfully I will be able to run the code in the reference workbook and then go back to the base workbook.

Thanks
ETracker :dunno

[GTO]

Is the workbook with the code in it "safe" to share? (IE - no private or proprietory info)

I would suggest posting either that or an example wb.

Mark

[ETracker]

Again, Thanks very much for your help with this issue.

Attached is the base file with the code and the second file that should open when running the code from the base file using cell A2 to activate the secondary file. The second file will be attached next.

Again thanks for any help you can give me to help solve this problem.

ETracker

[ETracker]

Here is the secondary file to run the code on.

Again, thanks for your help.

ETracker

[GTO]

Hello there,


..I have tried both and for some reason I can not get it to activate the second workbook...

Well, you really didn't try what I said, as you changed 'Value' to 'Valve'. My BB Chevy has valves, Excel has values... (hopefully that got a chuckle)

Sub UseCellValve()
Windows(Worksheets("Sheet1").Cells(2, 1).Valve).Activate
Sheets("Sheet2").Select
Range("D11").Select
ActiveCell.FormulaR1C1 = "This is a test"
End Sub

Sub UseCellValve1()
Workbooks(Worksheets("Sheet1").Cells(2, 1).Value).Activate
Sheets("Sheet2").Select
Range("D11").Select
ActiveCell.FormulaR1C1 = "This is a test"
End Sub

Now that we have that fixed, let's address a couple of small issues that will help you. In comparison to the above, I would point out the advantages to something similar to the below.

Sub UseCellValue_3()
Workbooks(ThisWorkbook.Worksheets("Sheet1").Cells(2, 1).Value) _
.Worksheets("Sheet2").Cells(11, 4).Value = "...Yet another test..."
End Sub

Now for ease, we'll call the first workbook Parent and the second Child.

In the code you are pursuing, you use Parent to first (1) Activate Child, then (2) Select a sheet, then (3) Select a cell, and then (4), write data to the cell.

Rather than four steps, we can do this in one step, and in my opinion, this one step is "safer". For instance, yours requires that Parent is active when the code first starts, elsewise, the code will fail, as it would be lookin at the value in A2 of "Sheet1" of Child, as which workbook "Sheet1" was in wasn't specified. I hope that made sense.

Anyways, that's why I have ThisWorkbook specified in the last example <Workbooks(ThisWorkbook.Worksheets("Sheet1").Cells(2, 1)...> , and skipping all the sellecting/activating is quite quicker.

Hope this was of help,

Mark

[ETracker]

Again, Thanks Mark for your time in helping me with getting this to work. I really enjoyed the laugh this morning,
My BB Chevy has valves, Excel has values...
It is amazing what happens or what donesn't happen when you insert a "V" for a "U", they almost look the same, but they don't work the same.

After replacing the "V" with a "U" it worked great. I will also give your one liner a try because anything faster or quicker is always better in the code world.

Again, thanks for your time and I will be on my way to making this process work this morning.

ETracker :bow: :clap: :bow:

[GTO]

Happy to help :friends: Mark