Paste special formulas wont work?

[T-rev]

i have multiple excel worksheets (12)

11 of which use data queries

1 for a master list.

the Master list is programmed with 4 VBA buttons, 1 to clear the list, 1 to extract the data from the other sheets making a new list, 1 to format the list, and the last 1 to add formulas (IF functions that produce TRUE/FALSE statements).

for some reason when i use the macro to copy my formulas from L3:Q3000 using paste special (formulas)

The result is False for every value.... The formulas are definately correct, and when i save the document and re-open they display the correct results.... Why is excel doing this?

Thanks in advance

T-rev - (Excel2007)

 

[Weaver]

What's the calculation status? Are you setting it to manual whilst running the macro?

 

[AlphaFrog]

Perhaps you need to refresh your querry table after you copy the formulas.

Example:

Worksheets("Sheet2").Range("B10").QueryTable.Refresh BackgroundQuery:=False

 

[T-rev]

The Macro is..

Private Sub CommandButton4_Click()
'
' AddFormulas Macro
Application.ScreenUpdating = True
Range("L3").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-10]>=0.1,RC[-10]<1),TRUE,FALSE)"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-11]>=RC[-8],TRUE,FALSE)"
Range("N3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-11]>0,TRUE,FALSE)"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-4]>=0,RC[-4]<=10),TRUE,FALSE)"
Range("L3:Q3").Select
Selection.Copy
Range("L4:Q3000").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Fill in the value that you want to delete
'Tip: use DeleteValue = "<>ron" to delete rows without ron
DeleteValue = ""

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("D3:D" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With



End Sub


Could it be that i have screen updating - True after calculation - Manual?

 

[T-rev]

Thanks for the quick replies too

 

[AlphaFrog]

Try puttiing this as the last line of your macro.

[COLOR="Red"]ActiveSheet.Calculate[/COLOR]
End Sub

 

[T-rev]

Excellent, that fixed it :D

Thanks a million..

 

[T-rev]

feel like helping on another problem? that did something to a different macro....

 

[AlphaFrog]

Post a new question. Helping is what this forum is about.

 

[T-rev]

i sure will :D is there a way to attach the file though, i think its something best explained if you can see exactly what i'm trying to do...