Request with a Macro and VBA

Hi, I would like to be assisted with a vba code/macro to accomplish below. I have attached a sample workbook. 1. The macro to hide all blanks rows ( deselect hyphen only) under " Description...

SAMPLE Order File.xlsx71 KB

Macros and VBA

HansVogelaar

Nov 22, 2022

A_SIRAT

Assuming that you want to save each sheet separately:

Sub HideAndSave()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim f As Variant
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
        ws.Range("B14").AutoFilter Field:=1, Criteria1:="<>-"
        ws.Copy
        Application.Dialogs(xlDialogSaveAs).Show ws.Range("K11").Value & ".xlsx"
        ActiveWorkbook.Close SaveChanges:=False
    Next ws
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

HansVogelaar

MVP

A_SIRAT

Here you go.

Sub HideAndSave()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim fp As String
    With Application.FileDialog(4) ' msoFileDialogFolderPicker
        If .Show Then
            fp = .SelectedItems(1)
            If Right(fp, 1) <> "\" Then
                fp = fp & "\"
            End If
        Else
            Beep
            Exit Sub
        End If
    End With
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
        ws.Range("B14").AutoFilter Field:=1, Criteria1:="<>-"
        ws.Copy
        ActiveWorkbook.SaveAs Filename:=fp & ws.Range("K11").Value & ".xlsx"
        ActiveWorkbook.Close SaveChanges:=False
    Next ws
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

A_SIRAT

Iron Contributor

Nov 23, 2022

Highly appreciated ..

Forum Discussion

Request with a Macro and VBA

Resources