Sheet reference syntax error

  • Hi there,


    I keep thinking that this is one of those posts that I should already know the answer to, but I still seem to be struggling!


    All I want to do is write a reference to a cell that includes the workbook name, the sheet and then the cell. Yet for whatever reason, I get a run-time error 9.


    The line in question if this:


    Code
    Application.Workbooks("Tracing precedents").Sheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveWorkbook.Name


    As you'll see, what I am trying to do is put the name of the activeworkbook into sheet3 of my "Tracing precedents" book, (where "Tracing Precedents" is obviously not the active book).


    Although I know that I could set a variable equal to the name of the activeworkbook, and then split my code as follows, I feel that this would be a bit of a bodge, and that there must be a better way.


    ie.


    Code
    Dim Workbook_Name as string
    Workbook_Name=activeworkbook.name
    Workbooks("Tracing Precedents").select
    Sheets("Sheet3").range...................................etc.=Workbook_Name


    Many thanks for all your help,


    Rich

  • Re: Sheet reference syntax error


    Playing around a bit, I get that error in two ways. If the file name is misspelled (upper lower do not matter, I don't think, but if there is an extra space or sopmething in the filename). Also, if the file being referenced is not/NOT open you'll get error 9.

  • Re: Sheet reference syntax error


    Thanks for the quick reply Thomach.


    I agree that you would get the error if anything is spelt incorrectly, or as you say, if the workbook is closed. However, I don't believe that to be the case this time.


    I think that Andy Pope has covered this before, but unfortunately I can't find his post on it. However, if I remember correctly, it was to do with attempting to carry out two tasks in one line(?), and the solution made use of the "Goto" command.


    But even if that was the problem, I still can't remember how to solve it!!


    Cheers,


    Rich

  • Re: Sheet reference syntax error


    Does the workbook name not requries the .xls extention?[vba]Application.Workbooks("Tracing precedents.xls").Sheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveWorkbook.Name [/vba]

  • Re: Sheet reference syntax error


    Hi Andy,


    Thanks for your thoughts on this.


    Curiously I don't think you do need the .xls reference, and even with it, the line still fails.


    What seems to be happening is that "Range" is somehow no longer a valid attribute of "Sheets" when I start the code with application.workbooks etc.


    I've had problems with this before, but have never fully understood why it occurs.


    Many thanks as ever,


    Rich

  • Re: Sheet reference syntax error


    I ran across a similar issue like this once I believe I was told to change it from .value to .text.


    If that helps, great. If not, I ruled out one method for you.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: Sheet reference syntax error


    For me the code fails, with Error 9, without the .xls
    Other than that the error is caused by a reference to something that does not exists, but Tom has already pointed this out.


    The Goto thing you are remebering is for activating a cell on a non active sheet.

  • Re: Sheet reference syntax error


    Thanks for your help guys.


    I'll have a play around and see if I can get it to work.


    As it is , I have had to solve it using the more messy method of defining variables etc, but I know this will continue to bug me if I can't get it to work the original way!!


    Cheers,


    Rich

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!