Forum Discussion
Solved
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...
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
Thank you Hans once again !
It works very well. Yes I wanted to save each sheet separately.
It works very well. Yes I wanted to save each sheet separately.
HansVogelaar
is it possible in addition to saving each sheet separately, to save them just once in my preferred location instead of clicking save for each and every worksheet. Thanks in advance.
is it possible in addition to saving each sheet separately, to save them just once in my preferred location instead of clicking save for each and every worksheet. Thanks in advance.
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- Highly appreciated ..