Hello,
I have the following code (as a module) in Excel 2010 that creates a .xlsx static copy of 3 specific sheets of large macro-enabled workbook (that I can't share here) with a button click. The code also removes certain objects, all hyperlinks, converts all formulas to just values, removes all data validation, and breaks all external workbook links. The only other thing I want the code to do is to keep the effects of any conditional formatting but remove the conditions.
- ''Creates a 3-sheet .xlsx static file removing all hyperlinks, formulas, and data validation
- Option Explicit
- Sub StaticReporter()
- 'Define variables
- Dim wb As Workbook
- Dim ws As Worksheet
- Dim l As Long
- Dim xOLE As Object
- Dim ExternalLinks As Variant
- Dim nm As Name
- Dim shp As Shape
- Dim fName
- 'Open Save As dialog box
- fName = Application.GetSaveAsFilename(fileFilter:="Excel Workbook (*.xlsx), *.xlsx")
- If fName = False Then
- 'MsgBox "Please enter a valid workbook name." <-- Undecided placeholder.
- Exit Sub
- Else
- GoTo ReportStart
- End If
- ReportStart:
- With Application
- .ScreenUpdating = False
- .DisplayAlerts = False
- 'Copy specific sheets
- Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
- 'Paste sheets as values; remove hyperlinks, data validation, and formulas; and select A1 on all sheets
- For Each ws In ActiveWorkbook.Worksheets
- ws.Cells.Copy
- ws.[A1].PasteSpecial Paste:=xlValues
- ws.Cells.Hyperlinks.Delete
- ws.Cells.Validation.Delete
- Application.CutCopyMode = False
- Cells(1, 1).Select
- ws.Activate
- 'Delete all command and toggle buttons
- For Each xOLE In ActiveSheet.OLEObjects
- If TypeName(xOLE.Object) = "CommandButton" Or TypeName(xOLE.Object) = "ToggleButton" Then
- xOLE.Delete
- End If
- Next
- 'Delete all but specific images or text boxes
- For Each shp In ActiveSheet.Shapes
- If shp.Name <> "Shape1" And shp.Name <> "Shape2" And shp.Name <> "Shape3" And shp.Name <> "Shape4" Then
- shp.Delete
- End If
- Next
- Next ws
- Cells(1, 1).Select
- 'Remove named ranges
- On Error Resume Next
- For Each nm In ActiveWorkbook.Names
- nm.Delete
- Next nm
- Set wb = ActiveWorkbook
- 'Create an array of any external links
- ExternalLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
- 'Loop through each external link and break it
- For l = 1 To UBound(ExternalLinks)
- wb.BreakLink Name:=ExternalLinks(l), Type:=xlLinkTypeExcelLinks
- Next l
- Sheets("Sheet1").Activate
- ActiveWindow.ScrollRow = 11
- Range("A1").Select
- ActiveWorkbook.SaveAs fName, FileFormat:=51, CreateBackup:=False
- .ScreenUpdating = True
- .DisplayAlerts = True
- End With
- End Sub
I've tried incorporating the following into different places of my code, but I can't seem to get anything to work.
Running Allen Wyatt's code as an independent module seems to have the most promise, however, when I plug in a
Call PasteFC()
before the
Next ws
line of my code, I get an error 13 type mismatch in the last case (the Expression case) of the ActiveCondition function at line:
If Application.Evaluate(FC.Formula1) Then.
I've also tried the responses in:
and
Since Wyatt Allen's code seems the closest to accomplishing what I'm after, is the error 13 the easiest to resolve? Or are there other, better venues?
Thanks in advance.