Help again please

[Keith Crooks]

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

 

[tony h]

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

 

[Dave Peterson]

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.)

 

[L. Howard Kittle]

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.
(e-mail address removed)

HTH
Regards,
Howard

 

[tony h]

I did point out the likelyhood of missing named ranges in my early
reply.