VBA Formulas not allowing copy and paste as values

[JazzSP8]

Hey All

I've got a strange one here.

I've a Workbook that pulls in data from Various Sources via VBA, mixes it altogether and then spits it back out in to a new Workbook that will be emailed out.

It works quite well, almost.

As part of the final part of the process I decided it was going to the easiest to use Formulas to build the last sheet before saving it out as its own Workbook.

When the workbook has been saved, because I've used formulas, this still links to the original Workbook that created it so I need to break this and so I Copy and Paste the formulas as values as that is all I need.

This bit doesn't work so well - The workbook is left with blank columns where the results of the formulas should be.

If I remove the Copy and Paste the values the Workbook retains the formulas linking back to the book.

While trying to debug I've noticed that if I set a Break Point anywhere in the code then it will work correctly, on resuming and the final Workbook has all the columns filled in as they should be with no formulas involved.

I am baffled by this now to be honest - I've even reached the point where I had a message box appear that said "Press OK" before the final workbook was saved, just in case it was the little break that was allowing the Copy / Paste to function correctly.

This is the code that deals with that section, again, this works but just not without a breakpoint somewhere. You can put a break point anywhere after the formulas have been inserted and it will paste as values correctly when the code completes.

If I remove the Copy / Paste values stuff then the workbook will save with Formulas linking back to the original workbook.

Range("C2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[1],Pricing!C[-2]:C[14],17,FALSE),"""")"
Range("C2").AutoFill Destination:=Range("C2:C" & LastRow)

Range("E2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],Pricing!C[-4]:C[13],18,FALSE),"""")"
Range("E2").AutoFill Destination:=Range("E2:E" & LastRow)

Range("G2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],Pricing!C[-6]:C[12],19,FALSE),"""")"
Range("G2").AutoFill Destination:=Range("G2:G" & LastRow)

For x = 2 To LastRow
    If Cells(x, "H").Value = "" Then
        Range("H" & x).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-4],'Old Brochure'!C[-7]:C[-4],4,FALSE),"""")"
    End If
Next x

Range("I2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-5],'Old Brochure'!C[-8]:C[-3],6,FALSE),"""")"
Range("I2").AutoFill Destination:=Range("I2:I" & LastRow)

Range("J2").FormulaR1C1 = "=IFERROR((RC[-1]-RC[8])/RC[-1],"""")"
Range("J2").AutoFill Destination:=Range("J2:J" & LastRow)

Range("K2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-7],Pricing!C[-10]:C[-5],6,FALSE),"""")"
Range("K2").AutoFill Destination:=Range("K2:K" & LastRow)

Range("L2").FormulaR1C1 = "=IF(OR(NOT(VLOOKUP(RC[-8],'Old Brochure'!C[-11]:C[-3],9,FALSE=0)),ISERROR(VLOOKUP(RC[-8],'Old Brochure'!C[-11]:C[-3],9,FALSE))),"""",VLOOKUP(RC[-8],'Old Brochure'!C[-11]:C[-3],9,FALSE))"
Range("L2").AutoFill Destination:=Range("L2:L" & LastRow)

Range("M2").FormulaR1C1 = "=IFERROR((RC[-1]-RC[5])/RC[-1],"""")"
Range("M2").AutoFill Destination:=Range("M2:M" & LastRow)

Range("N2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-10],Data!C[-13]:C[-8],6,FALSE),"""")"
Range("N2").AutoFill Destination:=Range("N2:N" & LastRow)

Range("O2").FormulaR1C1 = "=IFERROR((RC[-1]-RC[2])/RC[-1],"""")"
Range("O2").AutoFill Destination:=Range("O2:O" & LastRow)

Range("P2").FormulaR1C1 = "=IFERROR(RC[-6]-RC[-1],"""")"
Range("P2").AutoFill Destination:=Range("P2:P" & LastRow)

Range("Q2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-13],Pricing!C[-16]:C[-15],2,FALSE),"""")"
Range("Q2").AutoFill Destination:=Range("Q2:Q" & LastRow)

Range("R2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-14],Pricing!C[-17]:C[-15],3,FALSE),"""")"
Range("R2").AutoFill Destination:=Range("R2:R" & LastRow)

Range("S2").FormulaR1C1 = "=IF(RC[-2]=0,""A"",IFERROR((ABS(RC[-2]-RC[-1])/RC[-2]),""""))"
Range("S2").AutoFill Destination:=Range("S2:S" & LastRow)

Range("T2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-16],Pricing!C[-19]:C[-15],5,FALSE),"""")"
Range("T2").AutoFill Destination:=Range("T2:T" & LastRow)

Range("U2").FormulaR1C1 = "=IFERROR((RC[-1]-RC[-3])/RC[-1],"""")"
Range("U2").AutoFill Destination:=Range("U2:U" & LastRow)

Range("V2").FormulaR1C1 = "=IFERROR(RC[-2]-RC[-13],"""")"
Range("V2").AutoFill Destination:=Range("V2:V" & LastRow)

Application.StatusBar = "Making things look pretty"

Range("H2:H" & LastRow).NumberFormat = "$#,##0.00"
Range("I2:I" & LastRow).NumberFormat = "$#,##0.00"
Range("J2:J" & LastRow).NumberFormat = "0.00%"
Range("K2:K" & LastRow).NumberFormat = "General"
Range("L2:L" & LastRow).NumberFormat = "$#,##0.00"
Range("M2:M" & LastRow).NumberFormat = "0.00%"
Range("N2:N" & LastRow).NumberFormat = "$#,##0.00"
Range("O2:O" & LastRow).NumberFormat = "0.00%"
Range("P2:P" & LastRow).NumberFormat = "0.00%"
Range("Q2:Q" & LastRow).NumberFormat = "$#,##0.00"
Range("R2:R" & LastRow).NumberFormat = "$#,##0.00"
Range("S2:S" & LastRow).NumberFormat = "$#,##0.00"
Range("T2:T" & LastRow).NumberFormat = "$#,##0.00"
Range("U2:U" & LastRow).NumberFormat = "0.00%"
Range("V2:V" & LastRow).NumberFormat = "$#,##0.00"

Sheets("Imported Brochure File").Select
Sheets("Imported Brochure File").Copy

ChDir ActiveWorkbook.Path

MM = Month(Now)

If MM = 1 Then FDate = "January"
If MM = 2 Then FDate = "February"
If MM = 3 Then FDate = "March"
If MM = 4 Then FDate = "April"
If MM = 5 Then FDate = "May"
If MM = 6 Then FDate = "June"
If MM = 7 Then FDate = "July"
If MM = 8 Then FDate = "August"
If MM = 9 Then FDate = "September"
If MM = 10 Then FDate = "October"
If MM = 11 Then FDate = "November"
If MM = 12 Then FDate = "December"

FinalFile = FinalFile & " for PMs " & FDate & " Selection"

With ActiveWorkbook
    .KeepChangeHistory = True
    .ChangeHistoryDuration = 100
End With

    Columns("C:C").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("E:E").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Columns("H:I").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("K:K").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("L:L").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("N:N").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("Q:Q").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Columns("R:R").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Columns("T:T").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ActiveWorkbook.SaveAs Filename:=FinalFile & ".xlsx", FileFormat:=xlOpenXMLWorkbook, AccessMode:=xlShared

Has anyone had any experience with this at all?

I've tried other Copy and Paste methods, I've moved the code to different parts - I've tried Application.Calculate, I've activated Calculation before Save, I've used Application.Wait.

Really am at a loss as to why a break point sorts out my problem but it won't "just work".

Thanks in advance for any help that can be provided.

 

[JazzSP8]

Sorry to bump this, but I actually solved it and thought I should post how I fixed it for anyone stumbling along this in the future.

(I never got round to uploading the file for people to look at as the Work Prevention Department here has seen fit to block all sharing sites from our Internet access)

Anyways...

The problem was actually much earlier in the code than I thought it was. I gave up on the formula solution I had in place and rewrote that section of the code using "Find" to get the data I needed and paste it into place.

That still didn't work, so I knew something was wrong elsewhere so I started setting the Breakpoint earlier in the code, it would always complete as it should with a Breakpoint no matter where I put it.

Eventually I reasoned that if there was nothing in the Cells if I just let it run and used formulas or find / copy then the chances are the data was never there for the formula to return a result or anything to find, so I went to the very start.

As a part of pulling all the data together there is a query on two databases. I'll be the first to admit I'm not very knowledgable when it comes to this side of things (and Access as a whole), to get the code for the macro I recorded it and made little tweaks where I knew I could.

I ended up with something like this;

Sheets("Pricing").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=G:Brochure Selection Process.acc" _
        , _
        "db;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Passw" _
        , _
        "ord="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Trans" _
        , _
        "actions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:D" _
        , _
        "on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex D" _
        , "ata=False;Jet OLEDB:Bypass UserInfo Validation=False"), Destination:=Range( _
        "$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("PullEverything")
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .SourceDataFile = "G:Brochure Selection Process.accdb"
        .ListObject.DisplayName = "Table_Brochure_Selection_Process.accdb"
        .Refresh BackgroundQuery:=True
    End With

I noticed that there was two ".BackgroundQuery" lines in there, I don't know what they do so I've always left them alone, I'll hack out the bits of this type of code I know I don't need but leave the rest alone.

But it did cross my mind that the "Background" thing might be causing the table to update when everything else was finished and therefore only populating later than I expected, so I changed it to;

Sheets("Pricing").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=G:Brochure Selection Process.acc" _
        , _
        "db;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Passw" _
        , _
        "ord="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Trans" _
        , _
        "actions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:D" _
        , _
        "on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex D" _
        , "ata=False;Jet OLEDB:Bypass UserInfo Validation=False"), Destination:=Range( _
        "$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("PullEverything")
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .SourceDataFile = "G:Brochure Selection Process.accdb"
        .ListObject.DisplayName = "Table_Brochure_Selection_Process.accdb"
        .Refresh BackgroundQuery:=False
    End With

(The .BackgroundQuery(s) to False and not True)

And, do you know what? - It worked

I'm guessing by telling Excel it wasn't a "background" thing then it went ahead and populated the table while the code was running?

Anyways... Moral of the story?

"Don't get obsessed with one particular part of the code where you *think* the problem lies"

I shall now set about regrowing all the hair I've pulled out over the past couple of days over this...