Copy&paste works; Filldown doesn't

  • Thread starter Thread starter darius
  • Start date Start date
D

darius

Hi

I have this code which works

With ActiveCell
.Offset(-1, 12).Copy
.Offset(0, 12).PasteSpecial
End With

It's poor practice for a macro to use the clipboard, so I try to do this

With ActiveCell
Range(.Offset(-1, 12).Address(RowAbsolute:=False, _
ColumnAbsolute:=False) & ":" & _
.Offset(0, 12).Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).FillDown
End With

I probably don't need all the absolute:=false, but anyway, this seems
like it should work but doesn't. The formula in activecell.offset(-1,12)
doesn't get copied down.

Thanks.
 
Darius,

Your code works for me.
Click to expand...

Hmm, that's weird. I used msgbox to print out the range argument and it
IS correct (M20:M21). Is doing a .select necessary before .filldown? I
tried it and got an error: select operation on range failed (or something
like that).
 
Are you doing this from a commandbutton in xl97? If so, change the
takefocusonclick property of the commandbutton to false.
 
Are you doing this from a commandbutton in xl97? If so, change the
takefocusonclick property of the commandbutton to false.
Click to expand...

Ah! So it's a focus issue. See, I wouldn't have figured that out
myself.

I set takefocusonclick to false, but it still didn't work because the
button is on a different worksheet than the data I'm acting on. When
user clicks button, I switch to a named cell on a different worksheet
using application.goto and do my work then. It seems the focus IS on the
right worksheet, but maybe not? How do I ensure the worksheet has the
focus?
 
This is another type of problem.

When you are working in the sheet module, any unqualified range refers to
the sheet containing the code

so

Application.Goto Range("myrange")

would be seen as

Application.GoTo Sheet1.Range("myRange")

even though Sheet2 may be the active sheet the time and myRange is a named
range on sheet2. It really doesn't make any difference what the active
sheet is. I added this, because some people will do

Worksheets("Sheet2").Activate
Range("A1").Value = 3


If the code is in the Sheet1 code module, Range("A1") would refer to Sheet1
rather than Sheet2 (the active sheet).

So unlike in a general module, you need to qualify it

Application.Goto worksheets("Sheet2).Range("MyRange")

Or an alternative

Application.Goto ThisWorkbooks.Names("MyRange").RefersToRange

You can overcome some of this strange behavior by putting your code in a
general module and then just calling it from the click event

Private Sub Commandbutton1_Click()
MyMacro
End Sub

rather than having the code in the Click event itself.
 
This is another type of problem.

When you are working in the sheet module, any unqualified range
refers to the sheet containing the code

so

Application.Goto Range("myrange")

would be seen as

Application.GoTo Sheet1.Range("myRange")

even though Sheet2 may be the active sheet the time and myRange is a
named range on sheet2. It really doesn't make any difference what
the active sheet is. I added this, because some people will do

Worksheets("Sheet2").Activate
Range("A1").Value = 3


If the code is in the Sheet1 code module, Range("A1") would refer to
Sheet1 rather than Sheet2 (the active sheet).

So unlike in a general module, you need to qualify it

Application.Goto worksheets("Sheet2).Range("MyRange")

Or an alternative

Application.Goto ThisWorkbooks.Names("MyRange").RefersToRange

You can overcome some of this strange behavior by putting your code
in a general module and then just calling it from the click event

Private Sub Commandbutton1_Click()
MyMacro
End Sub

rather than having the code in the Click event itself.
Click to expand...

I tried both
Application.Goto worksheets("Sheet2).Range("MyRange")

Or an alternative

Application.Goto ThisWorkbooks.Names("MyRange").RefersToRange
Click to expand...

neither did the trick. So I ended up specifying the worksheet on the
range I'm working on:

worksheets("Sheet2").Range( long_expression_here).FillDown

and that did it.

Strange.
 
Back
Top