Overwrite sheet name?

    I am using the following code, which works fine. The only issue is that the "SHEET3" name is not recognised after the first time the macro is run!


    The macro saves the the names "SHEET3" but the next time it runs the sheet is not recognised??


    Any ideas?


    Code
    Range("a1:q52").Select
Range("a1:q52").Copy
Application.ScreenUpdating = False
Set wbopen = Workbooks.Open(Filename:="z:\Production Booking\weekly reference.xls")
Sheets("SHEET3").Range("A65536").End(xlUp).Activate
Range("a1:q52").PasteSpecial
wbopen.Close SaveChanges:=True
Application.ScreenUpdating = True


    many thanks in advance


    Rik :thanx:

    Re: Overwrite sheet name?


    the line that starts


    Sheets("Sheet3") etc etc


    Is the one that is highlighted by the debug file


    Any help would be apprechiated


    Thanks in advance

    Re: Overwrite sheet name?


    Hi there:


    The problem might be you may dont have sheet3 in your workbook or the name of the worksheet is not right.


    Try to copy the sheet name fromthe sheet tab.


    Right click on the Sheet Tab then choose Rename when it highlight use Ctrl+C (to copy) then paste the same in the code. I hope this will solve your problem



    Regards



    Maqbool

    Re: Overwrite sheet name?


    Code
    Sheets("SHEET3").Range("A65536").End(xlUp).Activate

    Hi


    The code you gave me worked but it now brings back the original problem I had where the pasted sheet does not drop down to the next avaliable line!


    this worked but now it does not drop down to the following line

    Code
    Application.GoTo Sheets("SHEET3").Range("A65536").End(xlUp)


    this dropped down to next avaliable space but overwrites the sheet name in the paste workbook

    Re: Overwrite sheet name?


    Argghh


    No that did not work either! It is just overlaying the previous paste, now dropping down to the next avaliable line!!

    Re: Overwrite sheet name?


    Hi MrCDP:


    Yes, it will paste always in the same range because this line in your code.


    Code
    Range("a1:q52").PasteSpecial



    Instead try to use

    Code
    Application.GoTo Sheets("SHEET3").Range("A65536").End(xlUp).offset(1,0) 
Activecell.PasteSpecial Paste:=xlvalues


    Regards


    Maqbool

    Re: Overwrite sheet name?


    that has worked to an extent but it has only pasted the number values not the lines etc of the spreadsheet, a great step forward though thank you!

    Re: Overwrite sheet name?


    HI Mr CDP:


    Try this way:


    Regards


    Maqbool

    Re: Overwrite sheet name?


    Quote

    The macro saves the the names "SHEET3" but the next time it runs


    I have noticed it does the same with macros I run to save files. It will work fine the first time but not the second. I haven't figured out why this is, yet. When I do I will post.

Participate now!

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

Register Yourself Login