Excel

References
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

dialerfile-export-to-csv_1-0.zip

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
        .Select
 
        '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.
  • =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
  • 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