List Macros - Overwrite on existing worksheet.

[Marty Plante]

I have existing code for listing macros in active workbook that creates a new sheet. I want to have it overwrite the existing "List_of_Macros" worksheet. I've created this with codename shtMacroList and the code is in the sheet and is runs on Worksheet_Activate()

I've been unable to find this code where it leaves the original worksheet and overwrites the data with updated list of macros. Any assistance would be appreciated. I do have this working perfectly with a table of contents code.

Sub ListMacros()
Dim VBComp As VBComponent
Dim wsTarget As Worksheet
Dim StartLine As Long
Dim iRow As Integer


Application.ScreenUpdating = False


Set wsTarget = Worksheets.Add
wsTarget.Range("A1") = "Macro"
wsTarget.Range("A1").Font.Bold = True
With wsTarget.Range("A1").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

iRow = 2

For Each VBComp In ThisWorkbook.VBProject.VBComponents
With VBComp.CodeModule
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
wsTarget.Cells(iRow, 1) = _
.ProcOfLine(StartLine, vbext_pk_Proc)
iRow = iRow + 1
StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Next VBComp

wsTarget.Range("A1").EntireColumn.AutoFit
Application.ScreenUpdating = True

End Sub

 

[footoo]

Replace this :

Set wsTarget = Worksheets.Add
wsTarget.Range("A1") = "Macro"
wsTarget.Range("A1").Font.Bold = True
With wsTarget.Range("A1").Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With

With this :

Set wsTarget = Worksheets("List of Macros")
With wsTarget
    Range(.[A2], .Cells(Rows.Count, 1).End(3)).ClearContents
End With

 

[Marty Plante]

Set wsTarget = Worksheets("List of Macros") With wsTarget Range(.[A2], .Cells(Rows.Count, 1).End(3)).ClearContents End With

 

[Marty Plante]

Worked perfectly. Thank you!

 

[smozgur]

@Marty Plante - In your future questions, that would be great if you could mark the post that helped to solve the problem in order to help future readers. No need to do anything for this question as I already switched the solution post as confirmed.

 

[Marty Plante]

@Marty Plante - In your future questions, that would be great if you could mark the post that helped to solve the problem in order to help future readers. No need to do anything for this question as I already switched the solution post as confirmed.

Yes, used the check mark on the right but it did give an error first time. I'll pay extra attention next time around.

 

[smozgur]

Yes, used the check mark on the right but it did give an error first time. I'll pay extra attention next time around.

No problem.

Note: That was not an error but actually a warning that you were marking your post as the solution to ask if you were sure.