Autofilter VBA

[bosko2]

Hello fellow Excel users!

I have developed a report that enables users to drill down through our company hierarchy. The below VBA achieves this. However, I received feedback that they wanted the ability to produce a company level report. Currently the code and associated drop-downs begin a level down from the company view. Is there a way I could alter the script below if there are no selections made, it brings ALL data across (i.e. no need for filtering)? If needed I can paste my entire script in...

Sub Report_Builder()


With Sheets("Report Creator")
   Sheets("Actions").Range("A1").AutoFilter 11, .Cells(2, 1).Value
   
   If .Cells(2, 2) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 12, .Cells(2, 2).Value
   End If
   If .Cells(2, 3) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 13, .Cells(2, 3).Value
   End If
   .Select
End With

Thanks for any assistance,
Mike

 

[Fluff]

Maybe

Sub Report_Builder()


With Sheets("Report Creator")
   If .Cells(2, 1) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 11, .Cells(2, 1).Value
   End If
   If .Cells(2, 2) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 12, .Cells(2, 2).Value
   End If
   If .Cells(2, 3) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 13, .Cells(2, 3).Value
   End If
   .Select
End With

 

[bosko2]

Maybe

Sub Report_Builder()


With Sheets("Report Creator")
   If .Cells(2, 1) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 11, .Cells(2, 1).Value
   End If
   If .Cells(2, 2) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 12, .Cells(2, 2).Value
   End If
   If .Cells(2, 3) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 13, .Cells(2, 3).Value
   End If
   .Select
End With

Perfect!!!!!
Thank you so much, I greatly appreciate your help.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[bosko2]

Hey again

You're welcome & thanks for the feedback.

Fluff, can I ask one more question please? It's part of the above code and your solution? I would be extremely grateful if you could take a look at the second part of the issue?

 

[Fluff]

What is the 2nd part of the issue?

 

[bosko2]

Thanks again Fluff, within the two separate sheets the gets created (further below in that script that you solved for me).

I would like the top row to be bold and have a simple filtered applied, see the below section of the script where I've been attempting it ) specific section is in blue dad:

        Dim DbExtract, DuplicateRecords As Worksheet
    Set DbExtract = ThisWorkbook.Sheets("Investigations")
    Set DuplicateRecords = ThisWorkbook.Sheets("My team's investigations")

    Sheets("Safety Accountability Dashboard").Select
    Sheets("Investigations").Visible = True
    Sheets("Investigations").Select
    Sheets("My team's investigations").Visible = True

    DbExtract.Range("A1:BF9999").SpecialCells(xlCellTypeVisible).Copy
    DuplicateRecords.Cells(1, 1).PasteSpecial
   
    Sheets("Investigations").Select
    ActiveWindow.SelectedSheets.Visible = False
    Selection.AutoFilter
    Selection.WrapText = True
    Selection.HorizontalAlignment = xlLeft
    Selection.VerticalAlignment = xlVAlignTop

Thank you in advance!
Mike

 

[bosko2]

Sorry, the text colour did not work. I have narrowed it to the below VBA:

Sheets("Investigations").Select
    ActiveWindow.SelectedSheets.Visible = False
    Selection.AutoFilter
    Selection.WrapText = True
    Selection.HorizontalAlignment = xlLeft
    Selection.VerticalAlignment = xlVAlignTop

I would like the top row to be bold and have a simple filtered applied.

 

[Fluff]

This has nothing to do with your original question, so needs a new thread.
When you start one, you will need to state the sheet name & the range.