+ Reply to Thread
Results 1 to 5 of 5

Help again please

  1. #1
    Keith Crooks
    Guest

    Help again please

    Hi guys, I posted a query on this group that was very quickly answered but I
    am not sure how to implement it.

    This is the code as recieved:

    Try something like this maybe. You will need to name the days five cells
    mon, tue, wed etc. Assign to a button named "Week Update" or whatever.
    Change F100 to whatever column suits you.

    Sub ListJobs()
    Dim mon As Range
    Dim tue As Range
    Dim wed As Range
    Dim thur As Range
    Dim fri As Range

    Range("mon").Copy Range("F100").End(xlUp).Offset(1, 0)
    Range("tue").Copy Range("F100").End(xlUp).Offset(2, 0)
    Range("wed").Copy Range("F100").End(xlUp).Offset(2, 0)
    Range("thur").Copy Range("F100").End(xlUp).Offset(2, 0)
    Range("fri").Copy Range("F100").End(xlUp).Offset(2, 0)

    End Sub

    I have put Dim lines into general declarationss and range("mon") etc into a
    button.

    I have in Cells
    A1:A5 Mon
    A6:A10 Tue
    A11:A15 Wed etc..

    In cells B1:B25 I have the jobs I have completed for those days, now what I
    want to happen is for any cell beteen B1:B25 that contains data to be placed
    to be copied to cells E75 onwards.

    However when i run the button I get an error message :METHOD 'RANGE' of
    Object '_Global' Failed
    and when I press debug it highlights the first line of code:
    Range("mon").Copy Range("b1:b5").End(xlUp).Offset(1, 0)

    many thanxs in advance

    keith



  2. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187
    The named ranges need to be on the worksheet. Select a cell and type a name (mon, tues) in the name box OR use the Insert Name (define or create)

    regards

  3. #3
    Dave Peterson
    Guest

    Re: Help again please

    Are you running xl97?

    And are you running the code from a control from the control toolbox toolbar
    placed on a worksheet?

    If that's the case, change the .takefocusonclick property to false for that
    control.

    Show the control toolbox toolbar.
    click on the design mode icon
    rightclick on the control
    choose properties
    change .takefocusonclick to false
    click on the design mode icon (to get out of that mode)

    Alternatively (if that control doesn't have this property), you could add this
    to the top of your procedure:

    Activecell.activate

    (This bug was fixed in xl2k.)

    Keith Crooks wrote:
    >
    > Hi guys, I posted a query on this group that was very quickly answered but I
    > am not sure how to implement it.
    >
    > This is the code as recieved:
    >
    > Try something like this maybe. You will need to name the days five cells
    > mon, tue, wed etc. Assign to a button named "Week Update" or whatever.
    > Change F100 to whatever column suits you.
    >
    > Sub ListJobs()
    > Dim mon As Range
    > Dim tue As Range
    > Dim wed As Range
    > Dim thur As Range
    > Dim fri As Range
    >
    > Range("mon").Copy Range("F100").End(xlUp).Offset(1, 0)
    > Range("tue").Copy Range("F100").End(xlUp).Offset(2, 0)
    > Range("wed").Copy Range("F100").End(xlUp).Offset(2, 0)
    > Range("thur").Copy Range("F100").End(xlUp).Offset(2, 0)
    > Range("fri").Copy Range("F100").End(xlUp).Offset(2, 0)
    >
    > End Sub
    >
    > I have put Dim lines into general declarationss and range("mon") etc into a
    > button.
    >
    > I have in Cells
    > A1:A5 Mon
    > A6:A10 Tue
    > A11:A15 Wed etc..
    >
    > In cells B1:B25 I have the jobs I have completed for those days, now what I
    > want to happen is for any cell beteen B1:B25 that contains data to be placed
    > to be copied to cells E75 onwards.
    >
    > However when i run the button I get an error message :METHOD 'RANGE' of
    > Object '_Global' Failed
    > and when I press debug it highlights the first line of code:
    > Range("mon").Copy Range("b1:b5").End(xlUp).Offset(1, 0)
    >
    > many thanxs in advance
    >
    > keith


    --

    Dave Peterson

  4. #4
    L. Howard Kittle
    Guest

    Re: Help again please

    Hi Keith,

    >I have in Cells
    >A1:A5 Mon
    >A6:A10 Tue
    >A11:A15 Wed etc..


    Does this mean you have 5 mon's followed by 5 tue's by 5 wed's etc., text
    entries in A1:A15? If so, then I assume you have no named ranges on your
    sheet. Check my other posts on how to enter named ranges.

    > Range("mon").Copy Range("b1:b5").End(xlUp).Offset(1, 0)


    This line is looking for named range "mon" and wants to copy it to the B1:B5
    range offset by 1 row. You want it to go to E75.

    Looks like your biggest error is not naming the ranges as needed. Once
    named then we can change the old code to this. (minus the >'s)

    > Range("mon").Copy Range("E200").End(xlUp).Offset(2, 0)
    > Range("tue").Copy Range("E200").End(xlUp).Offset(2, 0)
    > Range("wed").Copy Range("E200").End(xlUp).Offset(2, 0)
    > Range("thur").Copy Range("E200").End(xlUp).Offset(2, 0)
    > Range("fri").Copy Range("E200").End(xlUp).Offset(2, 0)


    Now, in E73 enter something like, "Jobs Done" or whatever. We need this so
    the code will start at E75.

    If you are going to assign the macro to a button, use a button from the
    FORMS tool bar. (Right click the upper tool bar anywhere and click on FORMS.
    There is a button icon there.)

    Again, I will be glad to look at a sample workbook if you want.
    [email protected]

    HTH
    Regards,
    Howard

    "Keith Crooks" <keithcrooks(no spam)@tiscali.co.uk> wrote in message
    news:[email protected]...
    > Hi guys, I posted a query on this group that was very quickly answered but
    > I am not sure how to implement it.
    >
    > This is the code as recieved:
    >
    > Try something like this maybe. You will need to name the days five cells
    > mon, tue, wed etc. Assign to a button named "Week Update" or whatever.
    > Change F100 to whatever column suits you.
    >
    > Sub ListJobs()
    > Dim mon As Range
    > Dim tue As Range
    > Dim wed As Range
    > Dim thur As Range
    > Dim fri As Range
    >
    > Range("mon").Copy Range("F100").End(xlUp).Offset(1, 0)
    > Range("tue").Copy Range("F100").End(xlUp).Offset(2, 0)
    > Range("wed").Copy Range("F100").End(xlUp).Offset(2, 0)
    > Range("thur").Copy Range("F100").End(xlUp).Offset(2, 0)
    > Range("fri").Copy Range("F100").End(xlUp).Offset(2, 0)
    >
    > End Sub
    >
    > I have put Dim lines into general declarationss and range("mon") etc into
    > a button.
    >
    > I have in Cells
    > A1:A5 Mon
    > A6:A10 Tue
    > A11:A15 Wed etc..
    >
    > In cells B1:B25 I have the jobs I have completed for those days, now what
    > I want to happen is for any cell beteen B1:B25 that contains data to be
    > placed to be copied to cells E75 onwards.
    >
    > However when i run the button I get an error message :METHOD 'RANGE' of
    > Object '_Global' Failed
    > and when I press debug it highlights the first line of code:
    > Range("mon").Copy Range("b1:b5").End(xlUp).Offset(1, 0)
    >
    > many thanxs in advance
    >
    > keith
    >




  5. #5
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187
    I did point out the likelyhood of missing named ranges in my early reply.



    Quote Originally Posted by L. Howard Kittle

    > Range("mon").Copy Range("b1:b5").End(xlUp).Offset(1, 0)


    This line is looking for named range "mon" and wants to copy it to the B1:B5
    range offset by 1 row. You want it to go to E75.

    Looks like your biggest error is not naming the ranges as needed. Once
    named then we can change the old code to this. (minus the >'s)

    >

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1