JazzSP8
Well-known Member
- Joined
- Sep 30, 2005
- Messages
- 1,233
- Office Version
- 365
- Platform
- Windows
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.
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.
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.
Code:
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:=xlSharedHas 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.
