Progress bar to show that the macro is running

F

frayner

New Member
Joined
Jun 29, 2007
Messages
8
Hello all,

I'm a first time poster, and so apologies if the below is a stupid question.

I have a lengthy macro which takes Excel approximately 1 minute to work through.

At present, the user can see everything that the macro is doing, which I think makes it look a little amateurish.

What I'd like to do is present a blank screen whilst the macro is running with a progress bar to show that it hasn't frozen. From looking through various posts, I realise that progress bars that go from 1 - 100 are particularly tricky to incorporate, and so I was really looking for a progress bar that would just cycle through and repeat until the end of the macro.

Does anyone know of such a thing?

Many thank in advance for your help,

Fran
 
Sort by date Sort by votes
Thank you. I'm still new at macros, so "if written well" is an open question on a lot of my code. In fact, you could probably tell that my original code is only SLIGHTLY modified from the mess rendered by the macro recorder.
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Feel free to post it so it can be cleaned up. ;)

Just make sure to give adequate description(s) as to what you're trying to accomplish.

Smitty
 
Upvote 0
Thanks for all of your suggestions guys, I really need to go back to the code and rewrite most of it. As I had taken most of it from recording rather than coding, much of it is obsolete.

The only thinkg I'd like to know now, is how to get a progress indicator to show whilst the macro is running.

I presume that if I get the code right, I can insert something before my macro to raise a userform, and something at the end to close it down. What I need help with is how to do this, and whether I can get a moving graphic on the form to reassure the user that the program hasn't frozen.

Any ideas?

Thanks
 
Upvote 0
Well, for one thing. There is no way to know how long a macro will take to run. 2 main reasons for this

1. The amount of data the macro manipulates varies each time it is run.
2. System Resources Vary

So if there is no way to know how long the macro will take, then there is no way to calculate a percentage of progress.

The only way you could really make the macro do something to let the user know it's not locked up would be..

1. Identify sections of code that take long times
2. Create a userform and set the "SHOWMODAL" Property to False
3. Put a Lable on the form that says "Still Running" or something like that
4. And another lable that will have a number in it, that you can incriment at various times in your macro..

before AND after sections you identified that take time you can put some code like this.

Code: 
userform1.show
userform1.label1.caption = userform1.label1.caption + 1

If you have some loops in your code like

for x = 1 to 100

put that code here so it will incriment with each loop
other code
next x

Other than that, it's not really worthwhile in my opinion to make a progress indicator.

You're time will be better spent (IMHO) concentrating on improving your code with the previous mentioned methods.

If your code currently takes for example, 1 minute to run. I can almost garuntee that Eliminating SELECT from your code will make it run in probably 10 seconds or less. This will prevent users from "thinking it's frozen" in the first place, eliminating your need for a progress indicator.

As Smitty Said, Feel free to post your code, we'll be glad to help make it more efficient.
 
Upvote 0
Hi Jonmo1, thanks for taking the time to reply to my post.

As requested, I've shown my code below. This is trigerred from a simple userform which asked the user if they are ready to run the macro.

I suspect that much of this could be done away with!!


Private Sub CommandButton2_Click()
'
UserForm1.Hide
Sheets("CR12 Input").Select
Cells.Select
Selection.Copy
Sheets("Futures Output").Select
Cells.Select
ActiveSheet.Paste
Sheets("CR12 Input").Select
Cells.Select
Selection.Copy
Sheets("Bloomberg Output").Select
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("TIPS").Select
Rows("38:38").Select
Selection.ClearContents
Cells.Select
Range("A31").Activate
Selection.EntireRow.Hidden = False
Range("A1").Select
Sheets("Bounce Off").Select
Range("C1508").Select
Selection.ClearContents
Cells.Select
Range("A1000").Activate
Selection.EntireRow.Hidden = False
ActiveWindow.ScrollRow = 944
ActiveWindow.ScrollRow = 940
ActiveWindow.ScrollRow = 936
ActiveWindow.ScrollRow = 924
ActiveWindow.ScrollRow = 899
ActiveWindow.ScrollRow = 871
ActiveWindow.ScrollRow = 835
ActiveWindow.ScrollRow = 800
ActiveWindow.ScrollRow = 721
ActiveWindow.ScrollRow = 674
ActiveWindow.ScrollRow = 628
ActiveWindow.ScrollRow = 569
ActiveWindow.ScrollRow = 513
ActiveWindow.ScrollRow = 458
ActiveWindow.ScrollRow = 424
ActiveWindow.ScrollRow = 403
ActiveWindow.ScrollRow = 293
ActiveWindow.ScrollRow = 269
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 133
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 945
ActiveWindow.ScrollRow = 944
ActiveWindow.ScrollRow = 941
ActiveWindow.ScrollRow = 936
ActiveWindow.ScrollRow = 928
ActiveWindow.ScrollRow = 911
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 779
ActiveWindow.ScrollRow = 737
ActiveWindow.ScrollRow = 691
ActiveWindow.ScrollRow = 647
ActiveWindow.ScrollRow = 601
ActiveWindow.ScrollRow = 554
ActiveWindow.ScrollRow = 505
ActiveWindow.ScrollRow = 467
ActiveWindow.ScrollRow = 424
ActiveWindow.ScrollRow = 385
ActiveWindow.ScrollRow = 303
ActiveWindow.ScrollRow = 263
ActiveWindow.ScrollRow = 224
ActiveWindow.ScrollRow = 183
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 945
ActiveWindow.ScrollRow = 944
ActiveWindow.ScrollRow = 941
ActiveWindow.ScrollRow = 935
ActiveWindow.ScrollRow = 926
ActiveWindow.ScrollRow = 915
ActiveWindow.ScrollRow = 890
ActiveWindow.ScrollRow = 873
ActiveWindow.ScrollRow = 853
ActiveWindow.ScrollRow = 800
ActiveWindow.ScrollRow = 764
ActiveWindow.ScrollRow = 723
ActiveWindow.ScrollRow = 687
ActiveWindow.ScrollRow = 633
ActiveWindow.ScrollRow = 602
ActiveWindow.ScrollRow = 565
ActiveWindow.ScrollRow = 530
ActiveWindow.ScrollRow = 489
ActiveWindow.ScrollRow = 454
ActiveWindow.ScrollRow = 387
ActiveWindow.ScrollRow = 356
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 242
ActiveWindow.ScrollRow = 220
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("A1").Select
Sheets("Bloomberg Output").Select
Cells.Select
ActiveWindow.Zoom = 55
Sheets("Futures Output").Select
Cells.Select
ActiveWindow.Zoom = 70
Sheets("CR12 Input").Select
Cells.Select
ActiveWindow.Zoom = 60
Sheets("TIPS").Select
Cells.Select
Range("A31").Activate
ActiveWindow.Zoom = 80
Sheets("SWAP Checker").Select
Cells.Select
ActiveWindow.Zoom = 65
Sheets("Bounce Off").Select
Cells.Select
Range("A1000").Activate
ActiveWindow.Zoom = 80
Sheets("Bounce Off").Select
Sheets("CR12 Input").Select
Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Sheets("SWAP Checker").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("K:AA").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("K5").Select
ActiveCell.FormulaR1C1 = "CR12 Value"
Range("L5").Select
ActiveCell.FormulaR1C1 = "Total"
Range("M5").Select
ActiveCell.FormulaR1C1 = "Difference"
Range("K5:M5").Select
Selection.Font.Bold = True
Range("K8").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],'CR12 Input'!R2C11:R1120C32,22,FALSE))"
Range("K8").Select
Selection.Copy
Range("K9:K1089").Select
ActiveSheet.Paste
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-1]="""",RC[-1]+R[1]C[-1],"""")"
Range("L8").Select
Selection.Copy
Range("L9:L1046").Select
ActiveSheet.Paste
Columns("L:L").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("L:L").Select
Selection.Replace What:="#VALUE!", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("M8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-3]-RC[-1])"
Range("M8").Select
Selection.Copy
Range("M9:M1089").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("M:M").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Selection.Font.Bold = True
Columns("K:K").EntireColumn.AutoFit
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=276
ActiveWindow.ScrollRow = 271
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 265
ActiveWindow.ScrollRow = 261
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 252
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 229
ActiveWindow.ScrollRow = 219
ActiveWindow.ScrollRow = 206
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 177
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 1
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("M:M").Select
Range("M319").Activate
Selection.NumberFormat = "#,##0.00"
Sheets("CR12 Input").Select
Range("A1").Select
Sheets("SWAP Checker").Select
Range("A1").Select
Sheets("Futures Output").Select
Columns("A:X").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("A:X").EntireColumn.AutoFit
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.ClearContents
Range("D1").Select
Selection.ClearContents
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Selection.Delete Shift:=xlToLeft
Range("D1").Select
ActiveCell.FormulaR1C1 = "Mkt Price(Rep Ccy)"
Range("D2").Select
Columns("D:D").EntireColumn.AutoFit
Columns("F:H").Select
Selection.Delete Shift:=xlToLeft
Columns("G:AH").Select
Selection.Delete Shift:=xlToLeft
Range("G1").Select
ActiveCell.FormulaR1C1 = "Contract Size"
Range("G2").Select
Columns("G:G").EntireColumn.AutoFit
Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'Data, do not amend'!R1C4:R3000C5,2,FALSE)"
Range("G2").Select
Selection.Copy
Range("G3:G5430").Select
ActiveSheet.Paste
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("H1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Value"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Difference"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-7]="""","""",RC[-5]*RC[-4]*RC[-1]/RC[-2])"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-8]="""","""",RC[-1]-RC[-4])"
Range("H2:I2").Select
Selection.Copy
Range("H3:H5880").Select
ActiveSheet.Paste
Columns("E:E").Select
Selection.NumberFormat = "#,##0.00"
Columns("H:H").Select
Selection.NumberFormat = "#,##0.00"
Columns("I:I").Select
Selection.NumberFormat = "#,##0.00"
Range("H2:I2").Select
Selection.Copy
Range("H3:H5880").Select
ActiveSheet.Paste
Columns("H:I").Select
Range("I1").Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Futures Output").Select
Columns("J:W").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="FINANCIAL FUTURES"
Columns("H:I").Select
Range("I1").Activate
Columns("H:I").EntireColumn.AutoFit
Range("A1").Select
Sheets("Bloomberg Output").Select
Columns("F:F").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("K:K").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.LargeScroll ToRight:=4
ActiveWindow.SmallScroll ToRight:=-8
Columns("AZ:AZ").Select
Selection.Cut
ActiveWindow.LargeScroll ToRight:=-4
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=22
Columns("AB:AB").Select
Selection.Cut
ActiveWindow.LargeScroll ToRight:=-2
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=19
Columns("AD:AD").Select
Selection.Cut
ActiveWindow.LargeScroll ToRight:=-2
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=23
Columns("AG:AG").Select
Selection.Cut
ActiveWindow.LargeScroll ToRight:=-2
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=28
Columns("AN:AN").Select
Selection.Cut
ActiveWindow.LargeScroll ToRight:=-2
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("O:DJ").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 1
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D2").Select
ActiveCell.FormulaR1C1 = "Corp Isin"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],"" "",RC[-1])"
Columns("E:E").EntireColumn.AutoFit
Selection.AutoFill Destination:=Range("E2:E1500"), Type:=xlFillDefault
Range("E2:E1500").Select
ActiveWindow.ScrollRow = 489
ActiveWindow.ScrollRow = 484
ActiveWindow.ScrollRow = 474
ActiveWindow.ScrollRow = 457
ActiveWindow.ScrollRow = 439
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 396
ActiveWindow.ScrollRow = 373
ActiveWindow.ScrollRow = 348
ActiveWindow.ScrollRow = 321
ActiveWindow.ScrollRow = 296
ActiveWindow.ScrollRow = 269
ActiveWindow.ScrollRow = 246
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 184
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 1
Range("D2").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("D2:D1500"), Type:=xlFillDefault
Range("D2:D1500").Select
ActiveWindow.ScrollRow = 484
ActiveWindow.ScrollRow = 479
ActiveWindow.ScrollRow = 474
ActiveWindow.ScrollRow = 464
ActiveWindow.ScrollRow = 451
ActiveWindow.ScrollRow = 433
ActiveWindow.ScrollRow = 414
ActiveWindow.ScrollRow = 393
ActiveWindow.ScrollRow = 369
ActiveWindow.ScrollRow = 344
ActiveWindow.ScrollRow = 318
ActiveWindow.ScrollRow = 294
ActiveWindow.ScrollRow = 271
ActiveWindow.ScrollRow = 251
ActiveWindow.ScrollRow = 229
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 194
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 163
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Range("E2:E1500").Select
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("D:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("C:C").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=3
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-1])=17,MID(RC[-1],6,3),IF(LEN(RC[-1])=16,MID(RC[-1],6,2),IF(LEN(RC[-1])=15,MID(RC[-1],6,1))))"
Range("G2").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G2:G1500"), Type:=xlFillDefault
Range("G2:G1500").Select
ActiveWindow.ScrollRow = 477
ActiveWindow.ScrollRow = 476
ActiveWindow.ScrollRow = 472
ActiveWindow.ScrollRow = 466
ActiveWindow.ScrollRow = 457
ActiveWindow.ScrollRow = 447
ActiveWindow.ScrollRow = 436
ActiveWindow.ScrollRow = 426
ActiveWindow.ScrollRow = 413
ActiveWindow.ScrollRow = 399
ActiveWindow.ScrollRow = 388
ActiveWindow.ScrollRow = 371
ActiveWindow.ScrollRow = 353
ActiveWindow.ScrollRow = 338
ActiveWindow.ScrollRow = 321
ActiveWindow.ScrollRow = 301
ActiveWindow.ScrollRow = 284
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 163
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Range("H2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]<1,RC[-1]-RC[-1]-RC[-1],RC[-1])"
Range("H2").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("H2:H1500"), Type:=xlFillDefault
Range("H2:H1500").Select
ActiveWindow.ScrollRow = 484
ActiveWindow.ScrollRow = 481
ActiveWindow.ScrollRow = 472
ActiveWindow.ScrollRow = 464
ActiveWindow.ScrollRow = 451
ActiveWindow.ScrollRow = 438
ActiveWindow.ScrollRow = 421
ActiveWindow.ScrollRow = 406
ActiveWindow.ScrollRow = 388
ActiveWindow.ScrollRow = 366
ActiveWindow.ScrollRow = 346
ActiveWindow.ScrollRow = 324
ActiveWindow.ScrollRow = 301
ActiveWindow.ScrollRow = 279
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 236
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 193
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Columns("H:H").Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("H2:H1500").Select
Selection.Copy
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("F:F").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("G:H").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").EntireColumn.AutoFit
Columns("E:E").Select
Selection.NumberFormat = "#,##0"
Columns("G:G").EntireColumn.AutoFit
Range("G1").Select
ActiveCell.FormulaR1C1 = "CR12e GAI"
Range("F1").Select
ActiveCell.FormulaR1C1 = "CR12e Days"
Columns("G:G").Select
Selection.ColumnWidth = 17.86
Range("H2").Select
ActiveCell.FormulaR1C1 = "=ROUND(RC[-1],0)"
Columns("H:H").Select
Selection.NumberFormat = "#,##0"
Range("H2").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("H2:H1500"), Type:=xlFillDefault
Range("H2:H1500").Select
Selection.Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.NumberFormat = "#,##0"
Range("F1:G1").Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("H1").Select
ActiveCell.FormulaR1C1 = "Bloomberg Days"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Bloomberg Accrued"
Range("H1:I1").Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
Range("H2").Select
Columns("H:H").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").EntireColumn.AutoFit
Range("H1:I1500").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range("J1").Select

ActiveCell.FormulaR1C1 = "Day Difference"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Value Difference"
Range("J1:K1").Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
Range("K3").Select
Columns("J:J").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
Range("J2").Select
ActiveCell.FormulaR1C1 = "=if"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-2]"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-2]"
Range("J2:K2").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("J2:K1500"), Type:=xlFillDefault
Range("L1").Select
ActiveCell.FormulaR1C1 = "Days Check"
Range("M1").Select
ActiveCell.FormulaR1C1 = "3% Value Check"
Range("L1:M1").Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
Range("K3").Select
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").EntireColumn.AutoFit
Range("L2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0,""OK"",""CHECK"")"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF((SQRT(POWER(RC[-2],2))/(RC[-6]+0.000000001))<0.03,""OK"",""CHECK"")"
Range("L2:M2").Select
Application.CutCopyMode = False
Selection.Copy
Range("L3:L1500").Select
ActiveSheet.Paste
Range("N1").Select
ActiveCell.FormulaR1C1 = "Overall"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2]&RC[-1])"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Overall Check"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""OKOK"",""OK"",""CHECK"")"
Range("N2:O2").Select
Application.CutCopyMode = False
Selection.Copy
Range("N3:N1500").Select
ActiveSheet.Paste

ActiveCell.FormulaR1C1 = "Day Check"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Accrued Check"
Range("J1:K1").Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
Range("K3").Select
Columns("J:J").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
Range("J2").Select
ActiveCell.FormulaR1C1 = "=if"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-2]"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=RC[-2],""ok"",""CHECK THIS ONE !"")"
Range("J2:K2").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("J2:K1500"), Type:=xlFillDefault
Range("J2:K1500").Select
ActiveWindow.ScrollRow = 484
ActiveWindow.ScrollRow = 482
ActiveWindow.ScrollRow = 480
ActiveWindow.ScrollRow = 475
ActiveWindow.ScrollRow = 471
ActiveWindow.ScrollRow = 463
ActiveWindow.ScrollRow = 456
ActiveWindow.ScrollRow = 445
ActiveWindow.ScrollRow = 433
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 405
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 373
ActiveWindow.ScrollRow = 357
ActiveWindow.ScrollRow = 341
ActiveWindow.ScrollRow = 327
ActiveWindow.ScrollRow = 313
ActiveWindow.ScrollRow = 298
ActiveWindow.ScrollRow = 283
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 227
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 1
Range("H2:H1500").Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("I2:I1500").Select
Selection.NumberFormat = "#,##0"
Range("I2").Select
Columns("K:K").ColumnWidth = 18.14
Columns("J:J").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("L2").Select
ActiveWindow.SmallScroll ToRight:=-5
Columns("A:A").ColumnWidth = 10.86
Columns("B:B").ColumnWidth = 9.29
Columns("C:C").ColumnWidth = 23
Columns("D:D").ColumnWidth = 54.43
ActiveWindow.SmallScroll ToRight:=6
Range("A1").Select
Sheets("Bounce Off").Select
Range("A5:B1500").Select
Selection.ClearContents
Sheets("Bloomberg Output").Select
Cells.Select
Selection.Replace What:=" Corp Isin", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("C2:C1500").Select
Selection.Copy
Sheets("Bounce Off").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Bloomberg Output").Select
ActiveWindow.ScrollRow = 774
ActiveWindow.ScrollRow = 771
ActiveWindow.ScrollRow = 765
ActiveWindow.ScrollRow = 760
ActiveWindow.ScrollRow = 748
ActiveWindow.ScrollRow = 729
ActiveWindow.ScrollRow = 712
ActiveWindow.ScrollRow = 690
ActiveWindow.ScrollRow = 668
ActiveWindow.ScrollRow = 644
ActiveWindow.ScrollRow = 618
ActiveWindow.ScrollRow = 588
ActiveWindow.ScrollRow = 550
ActiveWindow.ScrollRow = 514
ActiveWindow.ScrollRow = 483
ActiveWindow.ScrollRow = 450
ActiveWindow.ScrollRow = 426
ActiveWindow.ScrollRow = 399
ActiveWindow.ScrollRow = 374
ActiveWindow.ScrollRow = 348
ActiveWindow.ScrollRow = 323
ActiveWindow.ScrollRow = 296
ActiveWindow.ScrollRow = 272
ActiveWindow.ScrollRow = 250
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 177
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 1
Range("E2:E800").Select
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
Sheets("Bounce Off").Select
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Bloomberg Output").Select
Application.CutCopyMode = False
Range("A1").Select
Sheets("TIPS").Select
Range("B8").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'CR12 Input'!R2C11:R1000C28,18,FALSE)"
Range("B8").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste
Range("B11").Select
ActiveSheet.Paste
Range("B13").Select
ActiveSheet.Paste
Range("B14").Select
ActiveSheet.Paste
Range("B15").Select
ActiveSheet.Paste
Range("B16").Select
ActiveSheet.Paste
Range("D8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],'CR12 Input'!R1C11:R1000C30,20,FALSE)"
Range("D8").Select
Selection.Copy
Range("D10").Select
ActiveSheet.Paste
Range("D11").Select
ActiveSheet.Paste
Range("D13").Select
ActiveSheet.Paste
Range("D14").Select
ActiveSheet.Paste
Range("D15").Select
ActiveSheet.Paste
Range("D16").Select
ActiveSheet.Paste
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Range("E8:E16").Select
Selection.Copy
Range("D8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("F8").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'CR12 Input'!R2C11:R1000C36,26,FALSE)"
Range("F8").Select
Selection.Copy
Range("F10").Select
ActiveSheet.Paste
Range("F11").Select
ActiveSheet.Paste
Range("F13").Select
ActiveSheet.Paste
Range("F14").Select
ActiveSheet.Paste
Range("F15").Select
ActiveSheet.Paste
Range("F16").Select
ActiveSheet.Paste
Range("G8").Select
Application.CutCopyMode = False
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Sheets("CR12 Input").Select
ActiveWindow.ScrollRow = 1130
ActiveWindow.ScrollRow = 1117
ActiveWindow.ScrollRow = 1103
ActiveWindow.ScrollRow = 1080
ActiveWindow.ScrollRow = 1050
ActiveWindow.ScrollRow = 1017
ActiveWindow.ScrollRow = 976
ActiveWindow.ScrollRow = 939
ActiveWindow.ScrollRow = 892
ActiveWindow.ScrollRow = 839
ActiveWindow.ScrollRow = 782
ActiveWindow.ScrollRow = 729
ActiveWindow.ScrollRow = 678
ActiveWindow.ScrollRow = 618
ActiveWindow.ScrollRow = 567
ActiveWindow.ScrollRow = 521
ActiveWindow.ScrollRow = 474
ActiveWindow.ScrollRow = 424
ActiveWindow.ScrollRow = 357
ActiveWindow.ScrollRow = 304
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 197
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 1
Sheets("TIPS").Select
Selection.Delete Shift:=xlToLeft
Range("G8").Select
ActiveCell.FormulaR1C1 = _
"=IF('CR12 Input'!R2C3=""C"",""0"",VLOOKUP(TIPS!RC[-6],'CR12 Input'!R1C11:R1135C37,27,FALSE))"
Range("G8").Select
Selection.Copy
Range("G10").Select
ActiveSheet.Paste
Range("G11").Select
ActiveSheet.Paste
Range("G13").Select
ActiveSheet.Paste
Range("G14").Select
ActiveSheet.Paste
Range("G15").Select
ActiveSheet.Paste
Range("G16").Select
ActiveSheet.Paste
Columns("A:G").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H8").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("H8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=((RC[-4]*RC[-6]*RC[-3])/(RC[-2]*100))+RC[-1]"
Range("H8").Select
Selection.Copy
Range("H10").Select
ActiveSheet.Paste
Range("H11").Select
ActiveSheet.Paste
Range("H13").Select
ActiveSheet.Paste
Range("H14").Select
ActiveSheet.Paste
Range("H15").Select
ActiveSheet.Paste
Range("H16").Select
ActiveSheet.Paste
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Range("I8").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-8],'CR12 Input'!R2C11:R1136C32,22,FALSE)"
Range("I8").Select
Selection.Copy
Range("I10").Select
ActiveSheet.Paste
Range("I11").Select
ActiveSheet.Paste
Range("I13").Select
ActiveSheet.Paste
Range("I14").Select
ActiveSheet.Paste
Range("I15").Select
ActiveSheet.Paste
Range("I16").Select
ActiveSheet.Paste
Range("I17").Select
Application.CutCopyMode = False
Range("J8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("J8").Select
Selection.Copy
Range("J10").Select
ActiveSheet.Paste
Range("J11").Select
ActiveSheet.Paste
Range("J13").Select
ActiveSheet.Paste
Range("J14").Select
ActiveSheet.Paste
Range("J15").Select
ActiveSheet.Paste
Range("J16").Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="#DIV/0!", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False
Range("A1").Select
Sheets("TIPS").Select
Range("B37").Select
Selection.ClearContents
Range("A1").Select

Sheets("Bounce Off").Select
Range("A1").Select
Sheets("SWAP Checker").Select
Range("A1").Select
Sheets("CR12 Input").Select
Range("A1").Select
Sheets("Data, do not amend").Select
Range("A1").Select
Sheets("Futures Output").Select
Columns("B:B").ColumnWidth = 44.29
Columns("C:C").Select
Columns("B:B").ColumnWidth = 48.43
Range("A1").Select
Sheets("Bloomberg Output").Select
Columns("B:B").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select
Sheets("SWAP Checker").Select
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = 50
.PrintErrors = xlPrintErrorsDisplayed
End With
Sheets("Futures Output").Select
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = 65
.PrintErrors = xlPrintErrorsDisplayed
End With
Sheets("TIPS").Select
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = 70
.PrintErrors = xlPrintErrorsDisplayed
End With

Sheets("Bounce Off").Select
C = Range("B2").Value
Range("A1").Select
MsgBox "The date is currently set to " & C & ". If this is incorrect please amend. Save the file in your test environment and re-open it on the Bloomberg machine. Remember to use Ctrl+y before leaving the BB terminal!"
End Sub
 
Upvote 0
OK, here's for starts...PLEASE PLEASE Save a Backup Copy of your file before using this....

I have not tested, I've only gone through and removed Select...
There is probably alot more that can be done for efficiency, but just making the code manipulate sheets/cells without actually selecting them will help exponentially....

there is One line I commented out because I couldn't tell what cell you wanted "Day Check" In...You should be able to adjust it though..Or let me know..

Code: 
Private Sub CommandButton2_Click()
'
UserForm1.Hide
Sheets("CR12 Input").Cells.Copy Sheets("Futures Output").Range("A1")
Sheets("CR12 Input").Cells.Copy Sheets("Bloomberg Output").Range("A1")
Application.CutCopyMode = False
Sheets("TIPS").Rows("38:38").ClearContents
Sheets("TIPS").Rows("31:31").EntireRow.Hidden = False
Sheets("Bounce Off").Range("C1508").ClearContents
Sheets("Bounce Off").Rows("1000:1000").EntireRow.Hidden = False

'The Zoom Function is an exception that the Sheet MUST be Selected.
Sheets("Bloomberg Output").Select
ActiveWindow.Zoom = 55
Sheets("Futures Output").Select
ActiveWindow.Zoom = 70
Sheets("CR12 Input").Select
ActiveWindow.Zoom = 60
Sheets("TIPS").Select
Range("A31").Activate
ActiveWindow.Zoom = 80
Sheets("SWAP Checker").Select
ActiveWindow.Zoom = 65
Sheets("Bounce Off").Select
ActiveWindow.Zoom = 80

Sheets("CR12 Input").Columns("K:K").TextToColumns Destination:=Sheets("CR12 Input").Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

Sheets("SWAP Checker").Cells.Copy
Sheets("SWAP Checker").Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

With Sheets("SWAP Checker")
    .Columns("K:AA").Delete Shift:=xlToLeft
    .Range("K5").FormulaR1C1 = "CR12 Value"
    .Range("L5").FormulaR1C1 = "Total"
    .Range("M5").FormulaR1C1 = "Difference"
    .Range("K5:M5").Font.Bold = True
    .Range("K8").FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],'CR12 Input'!R2C11:R1120C32,22,FALSE))"
    .Range("K8").Copy .Range("K9:K1089")
    .Columns("K:K").Copy
    .Columns("K:K").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Application.CutCopyMode = False
    .Range("L8").FormulaR1C1 = "=IF(R[-1]C[-1]="""",RC[-1]+R[1]C[-1],"""")"
    .Range("L8").Copy .Range("L9:L1046")
    Application.CutCopyMode = False
    .Columns("L:L").Copy
    .Columns("L:L").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    .Columns("L:L").Replace What:="#VALUE!", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

    .Columns("L:L").Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
    Application.CutCopyMode = False
    .Range("M8").FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-3]-RC[-1])"
    .Range("M8").Copy .Range("M9:M1089")
    Application.CutCopyMode = False
    With .Columns("M:M").Interior
        .ColorIndex = 36
        .Pattern = xlSolid
        .Font.Bold = True
    End With

    .Columns("K:M").EntireColumn.AutoFit
    .Columns("L:L").EntireColumn.AutoFit
    .Columns("M:M").EntireColumn.AutoFit
    .Columns("M:M").Copy
    .Columns("M:M").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Application.CutCopyMode = False
    
    .Columns("M:M").NumberFormat = "#,##0.00"
End With

With Sheets("Futures Output")
    .Columns("A:X").Delete Shift:=xlToLeft
    .Columns("A:X").EntireColumn.AutoFit
    .Columns("C:C").Delete Shift:=xlToLeft
    .Columns("D:D").Delete Shift:=xlToLeft
    .Columns("E:E").ClearContents
    .Range("D1").ClearContents
    .Columns("D:D").TextToColumns Destination:=.Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

    .Columns("D:D").Delete Shift:=xlToLeft
    .Range("D1").FormulaR1C1 = "Mkt Price(Rep Ccy)"
    .Columns("D:D").EntireColumn.AutoFit
    .Columns("F:H").Delete Shift:=xlToLeft
    .Columns("G:AH").Delete Shift:=xlToLeft
    .Range("G1").FormulaR1C1 = "Contract Size"
    .Columns("G:G").EntireColumn.AutoFit
    .Range("G2").FormulaR1C1 = _
"=VLOOKUP(RC[-5],'Data, do not amend'!R1C4:R3000C5,2,FALSE)"
    .Range("G2").Copy .Range("G3:G5430")
    Application.CutCopyMode = False
    .Columns("G:G").Copy
    .Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

    .Columns("G:G").Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
    Application.CutCopyMode = False
    .Range("H1").ActiveCell.FormulaR1C1 = "Value"
    .Range("I1").FormulaR1C1 = "Difference"
    .Range("H2").FormulaR1C1 = "=IF(RC[-7]="""","""",RC[-5]*RC[-4]*RC[-1]/RC[-2])"
    .Range("I2").FormulaR1C1 = "=IF(RC[-8]="""","""",RC[-1]-RC[-4])"
    .Range("H2:I2").Copy .Range("H3:H5880")
    .Columns("E:E").NumberFormat = "#,##0.00"
    .Columns("H:H").NumberFormat = "#,##0.00"
    .Columns("I:I").NumberFormat = "#,##0.00"
    .Range("H2:I2").Copy .Range("H3:H5880")
    .Columns("H:I").Copy
    .Columns("H:I").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Application.CutCopyMode = False
    .Columns("J:W").Delete Shift:=xlToLeft
    .Range("A1").AutoFilter
    .Range("A1").AutoFilter Field:=1, Criteria1:="FINANCIAL FUTURES"
    .Columns("H:I").EntireColumn.AutoFit
End With
With Sheets("Bloomberg Output")
    .Columns("F:F").Cut
    .Columns("A:A").Insert Shift:=xlToRight
    .Columns("K:K").Cut
    .Columns("B:B").Insert Shift:=xlToRight
    .Columns("AZ:AZ").Cut
    .Columns("C:C").Insert Shift:=xlToRight
    .Columns("D:D").Insert Shift:=xlToRight
    .Columns("AB:AB").Cut
    .Columns("E:E").Insert Shift:=xlToRight
    .Columns("C:C").EntireColumn.AutoFit
    .Columns("A:A").EntireColumn.AutoFit
    .Columns("E:E").EntireColumn.AutoFit
    .Columns("AD:AD").Cut
    .Columns("F:F").Insert Shift:=xlToRight
    .Columns("AG:AG").Cut
    .Columns("G:G").Insert Shift:=xlToRight
    .Columns("H:H").Insert Shift:=xlToRight
    .Columns("G:G").EntireColumn.AutoFit
    .Columns("AN:AN").Cut
    .Columns("I:I").Insert Shift:=xlToRight
    .Columns("J:J").Insert Shift:=xlToRight
    .Columns("J:J").Insert Shift:=xlToRight
    .Columns("J:J").Insert Shift:=xlToRight
    .Columns("J:J").Insert Shift:=xlToRight
    .Columns("J:J").Insert Shift:=xlToRight
    .Columns("O:DJ").Delete Shift:=xlToLeft
    .Columns("D:D").Insert Shift:=xlToRight
    .Range("D2").FormulaR1C1 = "Corp Isin"
    .Range("E2").FormulaR1C1 = "=CONCATENATE(RC[-2],"" "",RC[-1])"
    .Columns("E:E").EntireColumn.AutoFit
    .Range("E2").AutoFill Destination:=.Range("E2:E1500"), Type:=xlFillDefault
    .Range("D2").AutoFill Destination:=.Range("D2:D1500"), Type:=xlFillDefault
    .Range("E2:E1500").Copy
    .Range("C2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Application.CutCopyMode = False
    .Columns("D:E").Delete Shift:=xlToLeft
    .Columns("C:C").EntireColumn.AutoFit
    .Columns("H:H").Insert Shift:=xlToRight
    .Range("G2").FormulaR1C1 = _
"=IF(LEN(RC[-1])=17,MID(RC[-1],6,3),IF(LEN(RC[-1])=16,MID(RC[-1],6,2),IF(LEN(RC[-1])=15,MID(RC[-1],6,1))))"
    .Range("G2").AutoFill Destination:=.Range("G2:G1500"), Type:=xlFillDefault
    .Range("H2").FormulaR1C1 = "=IF(RC[1]<1,RC[-1]-RC[-1]-RC[-1],RC[-1])"
    .Range("H2").AutoFill Destination:=.Range("H2:H1500"), Type:=xlFillDefault
    .Columns("H:H").NumberFormat = "0"
    
    With .Columns("H:H")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    .Range("H2:H1500").Copy
    .Range("F2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Application.CutCopyMode = False
    
    With .Columns("F:F")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    .Columns("G:H").Delete Shift:=xlToLeft
    .Columns("E:E").EntireColumn.AutoFit
    .Columns("E:E").NumberFormat = "#,##0"
    .Columns("G:G").EntireColumn.AutoFit
    .Range("G1").FormulaR1C1 = "CR12e GAI"
    .Range("F1").FormulaR1C1 = "CR12e Days"
    .Columns("G:G").ColumnWidth = 17.86
    .Range("H2").FormulaR1C1 = "=ROUND(RC[-1],0)"
    .Columns("H:H").NumberFormat = "#,##0"
    .Range("H2").AutoFill Destination:=.Range("H2:H1500"), Type:=xlFillDefault
    .Range("H2:H1500").Copy
    .Range("G2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Application.CutCopyMode = False
    .Columns("H:H").Delete Shift:=xlToLeft
    .Columns("G:G").NumberFormat = "#,##0"
    With .Range("F1:G1")
        Selection.Font.Bold = True
        Selection.Font.Underline = xlUnderlineStyleSingle
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    .Range("H1").FormulaR1C1 = "Bloomberg Days"
    .Range("I1").FormulaR1C1 = "Bloomberg Accrued"
    
    With .Range("H1:I1")
        .Font.Bold = True
        .Font.Underline = xlUnderlineStyleSingle
    End With
    
Columns("H:I").EntireColumn.AutoFit
    With Range("H1:I1500")
        .Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
    
    .Range("J1").FormulaR1C1 = "Day Difference"
    .Range("K1").FormulaR1C1 = "Value Difference"
    
    With .Range("J1:K1")
        .Font.Bold = True
        .Font.Underline = xlUnderlineStyleSingle
    End With
    
    .Columns("J:K").EntireColumn.AutoFit
    .Range("J2").FormulaR1C1 = "=if"
    .Range("J2").FormulaR1C1 = "=RC[-4]-RC[-2]"
    .Range("K2").FormulaR1C1 = "=RC[-4]-RC[-2]"
    .Range("J2:K2").AutoFill Destination:=Range("J2:K1500"), Type:=xlFillDefault
    .Range("L1").FormulaR1C1 = "Days Check"
    .Range("M1").FormulaR1C1 = "3% Value Check"
    
    With .Range("L1:M1")
        .Font.Bold = True
        .Font.Underline = xlUnderlineStyleSingle
    End With
    
    .Columns("L:M").EntireColumn.AutoFit
    .Range("L2").FormulaR1C1 = "=IF(RC[-2]=0,""OK"",""CHECK"")"
    .Range("M2").FormulaR1C1 = "=IF((SQRT(POWER(RC[-2],2))/(RC[-6]+0.000000001))<0.03,""OK"",""CHECK"")"
    .Range("L2:M2").Copy .Range("L3:L1500")
    .Range("N1").FormulaR1C1 = "Overall"
    .Range("N2").FormulaR1C1 = "=CONCATENATE(RC[-2]&RC[-1])"
    .Range("O1").FormulaR1C1 = "Overall Check"
    .Range("O2").FormulaR1C1 = "=IF(RC[-1]=""OKOK"",""OK"",""CHECK"")"
    .Range("N2:O2").Copy .Range("N3:N1500")
    'Not Sure What Cell You Want "Day Check" In here...
    'ActiveCell.FormulaR1C1 = "Day Check"
    .Range("K1").FormulaR1C1 = "Accrued Check"
    With .Range("J1:K1")
        .Font.Bold = True
        .Font.Underline = xlUnderlineStyleSingle
    End With
    
    .Columns("J:K").EntireColumn.AutoFit
    .Range("J2").FormulaR1C1 = "=RC[-4]-RC[-2]"
    .Range("K2").FormulaR1C1 = "=IF(RC[-4]=RC[-2],""ok"",""CHECK THIS ONE !"")"
    .Range("J2:K2").AutoFill Destination:=.Range("J2:K1500"), Type:=xlFillDefault
    With .Range("H2:H1500")
        .NumberFormat = "0"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    .Range("I2:I1500").NumberFormat = "#,##0"
    .Columns("K:K").ColumnWidth = 18.14
    
    With .Columns("J:J")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    .Columns("A:A").ColumnWidth = 10.86
    .Columns("B:B").ColumnWidth = 9.29
    .Columns("C:C").ColumnWidth = 23
    .Columns("D:D").ColumnWidth = 54.43
End With

Sheets("Bounce Off").Range("A5:B1500").ClearContents

With Sheets("Bloomberg Output")
    .Cells.Replace What:=" Corp Isin", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

    .Range("C2:C1500").Copy
    Sheets("Bounce Off").Range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False
    .Range("E2:E800").Copy
    Sheets("Bounce Off").Range("B5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False
End With

With Sheets("TIPS")
    .Range("B8").FormulaR1C1 = "=VLOOKUP(RC[-1],'CR12 Input'!R2C11:R1000C28,18,FALSE)"
    .Range("B8").Copy .Range("B10:B11")
    .Range("B8").Copy .Range("B13:B16")
    Application.CutCopyMode = False
    .Range("D8").FormulaR1C1 = "=VLOOKUP(RC[-3],'CR12 Input'!R1C11:R1000C30,20,FALSE)"
    .Range("D8").Copy .Range("D10:D11")
    .Range("D8").Copy .Range("D13:D16")
    Application.CutCopyMode = False
    
    .Columns("D:D").Copy
    .Columns("D:D").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Application.CutCopyMode = False
    .Columns("E:E").Insert Shift:=xlToRight
    .Columns("D:D").TextToColumns Destination:=.Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Application.CutCopyMode = False
    .Columns("D:D").TextToColumns Destination:=.Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

    .Range("E8:E16").Copy
    .Range("D8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

    Application.CutCopyMode = False
    .Columns("E:E").Delete Shift:=xlToLeft
    .Range("F8").FormulaR1C1 = _
"=VLOOKUP(RC[-5],'CR12 Input'!R2C11:R1000C36,26,FALSE)"
    .Range("F8").Copy .Range("F10:F11")
    .Range("F8").Copy .Range("F13:F16")

    Application.CutCopyMode = False
    .Columns("G:G").Insert Shift:=xlToRight
    .Columns("G:G").Delete Shift:=xlToLeft
    .Range("G8").FormulaR1C1 = _
"=IF('CR12 Input'!R2C3=""C"",""0"",VLOOKUP(TIPS!RC[-6],'CR12 Input'!R1C11:R1135C37,27,FALSE))"
    .Range("G8").Copy .Range("G10:G11")
    .Range("G8").Copy .Range("G13:G16")

    .Columns("A:G").Copy
    .Columns("A:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Application.CutCopyMode = False
    .Range("H8").Copy
    .Range("H8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

    .Range("H8").Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
    Application.CutCopyMode = False
    .Range("H8").FormulaR1C1 = "=((RC[-4]*RC[-6]*RC[-3])/(RC[-2]*100))+RC[-1]"
    .Range("H8").Copy .Range("H10:H11")
    .Range("H8").Copy .Range("H13:H16")

    Application.CutCopyMode = False
    .Range("I8").FormulaR1C1 = _
"=VLOOKUP(RC[-8],'CR12 Input'!R2C11:R1136C32,22,FALSE)"
    .Range("I8").Copy .Range("I10:I11")
    .Range("I8").Copy .Range("I13:I16")

    Application.CutCopyMode = False
    .Range("J8").FormulaR1C1 = "=RC[-1]-RC[-2]"
    .Range("J8").Copy .Range("J10:J11")
    .Range("J8").Copy .Range("J13:J16")

    .Cells.Copy
    .Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

    .Cells.Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
    .Cells.Replace What:="#DIV/0!", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
    Application.CutCopyMode = False

    .Range("B37").ClearContents
End With

Sheets("Futures Output").Columns("B:B").ColumnWidth = 48.43
Sheets("Bloomberg Output").Columns("B:B").EntireColumn.Hidden = True
Sheets("SWAP Checker").Select
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = 50
.PrintErrors = xlPrintErrorsDisplayed
End With
Sheets("Futures Output").Select
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = 65
.PrintErrors = xlPrintErrorsDisplayed
End With
Sheets("TIPS").Select
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = 70
.PrintErrors = xlPrintErrorsDisplayed
End With

Sheets("Bounce Off").Select
c = Range("B2").Value
Range("A1").Select
MsgBox "The date is currently set to " & c & ". If this is incorrect please amend. Save the file in your test environment and re-open it on the Bloomberg machine. Remember to use Ctrl+y before leaving the BB terminal!"
End Sub
 
Upvote 0
Wow, thanks so much. That code is less than a quarter of the stuff I had in there. After a little tweaking it works like a charm.

Thanks again,

Fran
 
Upvote 0
No Problem, Was it much Faster?

Just for future reference, did you get the jist of what I did?

1 main thing, was using WITH

just put With Sheets("Sheetname")

then anytime you need to specify THAT sheet name, preceed the code with a period like..

.Range("A1")

and when your code would have switched to another sheet, put
END With

Then With Sheets("Newname")

The other main thing was deleting select...

Any time you have some code that Selects a cell, Then DOES something with SELECTION, you can just delete everything from Select to Selection..

example

Range("A1").Select
Selection.copy
Range("B1").Select
Selection.PasteSpecial

can change to

Range("A1").copy
Range("B1").PasteSpecial
 
Upvote 0
Another Tip...

Your code is using ZOOM on several pages...That is one of the exceptions where the sheet MUST be selected and active to be used...

If that is used only so that a specific range will be visible, you might consider changing the FONT size and then AutoFit instead..

something like
Code: 
With Sheets("Sheetname").Range("A1:G100")
    .Font.Size = 8
    .EntireColumn.AutoFit
    .EntireRow.AutoFit
End With
Experiment with the Font Size to get The size you want.
This way you don't have to select the sheet, and code will be faster.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Guna13
  • Solved
How do we know Macro running Status - Progress Bar Method
2
Replies
12
Views
2K
portews
P
B
  • Question
Milestone Shape Rollup to Showing Up *Behind* Summary Task Bar
Replies
3
Views
598
6StringJazzer
6StringJazzer
G
  • Question
Incorporating Progress Bar into existing Userform
Replies
3
Views
1K
6StringJazzer
6StringJazzer
G
  • Question
Excel VBA Macro steps through ok but when run it causes duplication
Replies
2
Views
3K
Gassy
G
W
  • Question
VBA Progress Bar not Working
Replies
7
Views
6K
bobsan42
bobsan42

Forum statistics

Threads
1,215,055
Messages
6,122,902
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top