ActiveCell.value does not display anything

[pwr1965]

I have a number of values, originating from a given worksheet, that need to be displayed on another worksheet. This works great for most values with the following code:

Sheets("Invulblad").Select
Range("M4").Select
Selection.End(xlDown).Select
Sheets(Sheets.Count).Range("B13") = ActiveCell.Value

Here's the problem: it does not work if the active cell contains a formula. On the source worksheet I can see the formula result, but VBA does not copy the resulting value to its destination. Ideas anyone?

 

[Fluff]

Hi & welcome to MrExcel.
That code works for me & copies the result of the formula, what is it doing for you?

 

[Joe4]

Welcome to the Board!

You should be able to reduce all your code above down to this one line:

Sheets(Sheets.Count).Range("B13").Value = Sheets("Invulblad").Range("M4").End(xlDown).Value

 

[pwr1965]

Hi & welcome to MrExcel.
That code works for me & copies the result of the formula, what is it doing for you?

Hi Fluff,
Thanks for your swift response. What it does for me is exactly nothing; it returns an empty cell.
Not for source cells that contain text( these work just fine), but for source cells that contain a formula. I have cells that perform a VLOOKUP based on input and I want to use these VLOOKUP results in my final sheet.

 

[Fluff]

Are you sure that the formula in the active cell is not returning ""?

 

[pwr1965]

Welcome to the Board!

You should be able to reduce all your code above down to this one line:

Sheets(Sheets.Count).Range("B13").Value = Sheets("Invulblad").Range("M4").End(xlDown).Value

Hi Joe4,
Thanks a million, this makes my code a lot leaner. Alas, it did not solve my initial problem: all source cells containing formulas result in empty destination cells..

 

[pwr1965]

Are you sure that the formula in the active cell is not returning ""?

Positive. The sheet called "Invulblad" shows the desired result, but this is not carried over to the new sheet.

 

[Fluff]

Do you have formulae in col M that return "", especially if the formula is copied down further than needed?

 

[pwr1965]

Do you have formulae in col M that return "", especially if the formula is copied down further than needed?

Yes, that I have.

 

[Fluff]

In that case using

Range("M4").Select
   Selection.End(xlDown).Select

Will take you the very last row in col M that has a formula & it's probably returning ""