Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
fnd = "#REF!"
rplc = ""
For Each sht In ActiveWorkbook.Worksheets
    sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next sht


Option Explicit
Public Sub ExportWorksheetAndSaveAsCSV()
'On Error GoTo Errorcatch
Application.ScreenUpdating = False
Dim wbkExport As Workbook
Dim shtToExport As Worksheet
Dim myWorksheet As Variant
Dim myFolder As Variant
Dim myFilename As Variant
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
myWorksheet = "tmpSheet"
myFolder = ThisWorkbook.Worksheets("Summary").Range("B2").Value
myFilename = ThisWorkbook.Worksheets("Summary").Range("B3").Value
Sheets(myWorksheet).Visible = True
Set shtToExport = ThisWorkbook.Worksheets(myWorksheet)     'Sheet to export as CSV
Set wbkExport = Application.Workbooks.Add
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
'Convert to Values (do not copy Formulas)
ActiveSheet.UsedRange.Value = shtToExport.UsedRange.Value
Application.DisplayAlerts = False                       'Possibly overwrite without asking
'MsgBox "The name of the active sheet is " & ActiveSheet.Name
'MsgBox "The name of the active Workbook is " & ActiveWorkbook.Name
    With ActiveSheet
        'We select the sheet so we can change the window view
        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        'ViewMode = ActiveWindow.View
        'ActiveWindow.View = xlNormalView
        'Turn off Page Breaks, we do this for speed
        '.DisplayPageBreaks = False
        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1
            'We check the values in the A column in this example
            With .Cells(Lrow, "A")
                If Not IsError(.Value) Then
                    If .Value = "0" Then .EntireRow.Delete
                    'This will delete each row with the Value "0"
                    'in Column A, case sensitive.
                End If
            End With
        Next Lrow
    End With
With ActiveWorkbook
    'Saves the new workbook to given folder / filename:
    .SaveAs Filename:=myFolder & myFilename, FileFormat:=xlCSV
    'Closes the file
    .Close False
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Sheets(myWorksheet).Visible = False
MsgBox "Created Dialer File: " & myFilename
'Errorcatch: MsgBox Err.Description
End Sub

Get a list of all worksheets in your workbook

Reference: https://www.datanumen.com/blogs/3-quick-ways-to-get-a-list-of-all-worksheet-names-in-an-excel-workbook/

  • Formulas tab and click the “Name Manager” button → New
  • Enter ListSheets as the Name and the below formula into the Refers to field.
  • Have a worksheet with A column with number of sheets on each row. and Use a formula to create hyperlink to all worksheets
  • =INDEX(ListSheets,A1)
  • then use the below to get the URL link for the worksheet
  • =HYPERLINK("#'"&B1&"'!A1",B1)

Reference: https://www.datanumen.com/blogs/3-methods-to-create-a-list-of-hyperlinks-to-all-worksheets-in-an-excel-workbook/

Dependant Dropdowns

  • excel.txt
  • Last modified: 2020/02/07 14:09
  • by gerardorourke