Posts by hmws

    Hi

    Several references online to this but I am missing something. "Reafidy July 2007" seemed appropriate but returns error 13 mismatch

    I keep wondering if it doesn't like the format "dd:mmm:yy"



    I have a text box in userform initialize displaying current date as "dd:mmm:yy" which works and displays as it should.


    Code
       txtread.Value = Range("f8").Offset(0, -3).Value
        txtbill.Value = Range("f8").Offset(0, -2).Value
        txtcurrdate.Value = Format(Now(), "dd:mmm:yy")

    I am trying to transfer/copy date from txtcurrdate to spreadsheet activecell.


    If I use activecell.value = txtcurrdate.value this returns the data but not in date format.


    Any help on this I would be grateful. Thanks a lot

    ....................' Adjust the Copy Instruction to the specific Sheet Names and Ranges
    ' Workbooks(Sourfile).Sheets("LJA").Range("C74:C118").Value = Workbooks(Mainfile).Sheets("LJA").Range("C54:C98").Value



    Workbooks(Sourfile).Activate
    Workbooks(Sourfile).Sheets("LJA").Range("C74:C118").Select
    Selection.Copy

    Windows("LJA databasetestvar.xlsm").Activate

    Range("C54").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Range("a3").Select

    End sub()




    Hi Carim
    I don't know how to copy code so it highlights in grey but where you noted to adjust copy range to suit - the above worked better for me.
    Otherwise really pleased with this and exactly what I wanted.
    Once again many thanks.
    Steve

    Hi Carim
    Thanks for the very speedy response.


    I can see this would work but I am trying to avoid putting any code in the source workbook. This is an orderform going out to her customers, didn't want coded workbooks going to them.
    I would like to start with destination as the active workbook - If I set destination as wkb1 is there some way of setting source as wkb2?


    I understand both workbooks must be opened.


    Once again many thanks - Steve

    [SIZE=12px]The code below does what I want but is it possible to activate the same range from a different named source workbook each time?


    Ideally I didn't want any code in the source file.


    I am doing this for a friend who has next to no knowledge at all with Excel. I know if she saves each order with the same name each time this will work from the destination workbook as below.


    Any ideas would be appreciated. Thanks very much




    Sub RetOrdfrmdata()
    '
    '
    Application.ScreenUpdating = False

    ' Windows("LOFtestvar.xlsx").Activate - SOURCE WORKBOOK
    Range("C74:C118").Select
    Selection.Copy

    Windows("LJA databasetestvar.xlsm").Activate - DESTINATION WORKBOOK

    Range("C54").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Range("a3").Select

    End Sub
    [/SIZE]

    Re: Problem with VBokcancel returning either 1 or 2


    Thanks so much for coming back Carim.


    I am doing this spreadsheet to try and help a nearby neighbour with his weather data. I am still waiting on him for exactly which data he wants and where, this was really just an example.


    Please don't worry, as long as the code is fundamentally ok I am happy to accept this, as long as there were no obvious errors which was the main thing. Initially in my code the 1 or 2 was appearing in whichever was active cell which was taking out entered data which was a bit annoying. Once I discovered the cause I selected a cell range at the end of the code.


    Equally my neighbour will be delighted with the end result from what he was doing previously and besides I may need help further down the line! :)


    Once again many thanks

    Re: Problem with VBokcancel returning either 1 or 2


    Quote from Carim;793771

    Hello,


    It would be great ...if you could post your code ... to see what might need to be modified ...


    HTH


    I am no expert by any means in VBA. I have created some code which does what it should but I have a minor problem
    I have a message box displayed with vbOKCancel. On clicking cancel I get returned in a cell 2, if clicking ok 1 is returned. I know that is a result of clicking the button as I tried bypassing the message box and the code worked fine but equally I know clicking cancel it Exits the sub. Is there anyway I can stop the numbers appearing? Or do I just have the 1 or 2 finishing in any blank cell?


    It may just be me with the way I have created the code.


    Thanks a lot