Delete VBA code

[tlindeman]

Is it possible to create a macro to delete code from antoher macro on certain date, for example here is a samlple of my code:

Application.DisplayAlerts = False
Workbooks.Open Filename:="R:\ACCT\FINREPORTING\FS2011\Ratios\CAPX Ratios\Indebtedness.xlsx", UpdateLinks:=3
Windows("Enterprise KPI's.XLSM").Activate
Sheets("DEBT").Select
If Range("N16").Value = 1 Then
Range("N2").Formula = "=[Indebtedness.xlsx]Schedule_Jun!$E$7"
Range("N3").Formula = "=[Indebtedness.xlsx]Schedule_Jun!$E$8"
Range("N4").Formula = "=[Indebtedness.xlsx]Schedule_Jun'!$E$9"
Range("N8").Formula = "=[Indebtedness.xlsx]Schedule_Jun!$E$13"
Range("N9").Formula = "=[Indebtedness.xlsx]Schedule_Jun!$E$14"
Range("N10").Formula = "[Indebtedness.xlsx]Schedule_Jun!$E$15"
Range("N11").Formula = "[Indebtedness.xlsx]Schedule_Jun!$E$16"
Range("N12").Formula = "[Indebtedness.xlsx]Schedule_Jun!$E$17"
Else
Range("N2").Formula = "=L2"
Range("N3").Formula = "='Cash Flow Projection'!EG42/1000"
Range("N4").Formula = "=L4"
Range("N8").Formula = "=SUM('[2011 Balance Sheet by Month.xlsx]BS'!$BV$55+'[2011 Balance Sheet by Month.xlsx]BS'!$BV$61)"
Range("N9").Formula = "=L9"
Range("N10").Formula = "=L10"
Range("N11").Formula = "=L11"
Range("N12").Formula = "=L12"
End If
If Range("P16").Value = 1 Then
Range("P2").Formula = "=[Indebtedness.xlsx]Schedule_Jul!$E$7"
Range("P3").Formula = "=[Indebtedness.xlsx]Schedule_Jul!$E$8"
Range("P4").Formula = "=[Indebtedness.xlsx]Schedule_Jul'!$E$9"
Range("P8").Formula = "=[Indebtedness.xlsx]Schedule_Jul!$E$13"
Range("P9").Formula = "=[Indebtedness.xlsx]Schedule_Jul!$E$14"
Range("P10").Formula = "[Indebtedness.xlsx]Schedule_Jul!$E$15"
Range("P11").Formula = "[Indebtedness.xlsx]Schedule_Jul!$E$16"
Range("P12").Formula = "[Indebtedness.xlsx]Schedule_Jul!$E$17"
Else

After June, I would like to delete all the lines that refer to the ranges in N. After July I would like to delete all the lines that refer to range P. I know I can manually delete, however, I have extensive amount of code and would like to create the macro to run.

Thank You
Tony

 

[ChrisM]

Not to derail you completely, but the similarity in your bits of code seems to lend itself to an algorithm that takes the current month, then uses that to calculate the rows/cells to operate on.

If June is in column N and July in P, then it seems you skip a column each month. Where you have:

.Range("N2").Formula = xxx

turn this into a value keyed off of the month number. Column N is the 14th column, June is the sixth month, so:

.Cells(2,((Month(Date))*2)+2).Formula = xxx

So June = 6*2 + 2 = 14 (column N)
July = 7*2 +2 = 16 (column P)
etc

That takes care of your formula cells. Now just fix the linked cells:

'Schedule_Jun'
'Schedule_Jul'
etc

It should be very easy to dynamically generate that last "Jun" part based on the current month.

 

[tlindeman]

Dim VBProj As VBIDE.VBProject

 

[VoG]

Check again that you have set a reference to Microsoft Visual Basic For Applications Extensibility 5.3

 

[tlindeman]

Sorry,
I did not have it checked, now it is checked and I am getting an error "Object does not support his property or method"

The following is highlighted
Set VBProj = ActiveWorkbook.VBProj

Tony

 

[VoG]

That should be

Set VBProj = ActiveWorkbook.VBProject

 

[tlindeman]

Works perfectly, thank you for all of your help and patience.

Tony