One part of VBA works, second doesn't

bosko2

New Member
Joined
Nov 3, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Hi there fellow Excel users,

I'm stumped with this piece of VBA, specifically the "Selection.AutoFilter". If I have that statement "Selection.AutoFilter" in the first sheet, that sheet ends up and AutoFilter but not the second sheet. The strange thing is when I place the exact statement for the second sheet, both sheets don't end up with an AutoFilter.

Any ideas? And thank you in advance.

VBA Code:
    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
[QUOTE]
    Selection.AutoFilter
[/QUOTE]
    Selection.WrapText = True
    Selection.HorizontalAlignment = xlLeft
    Selection.VerticalAlignment = xlVAlignTop

    Set DbExtract = ThisWorkbook.Sheets("Actions")
    Set DuplicateRecords = ThisWorkbook.Sheets("My team's actions")

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

    DbExtract.Range("A1:BF9999").SpecialCells(xlCellTypeVisible).Copy
    DuplicateRecords.Cells(1, 1).PasteSpecial
    
    Sheets("Actions").Select
    ActiveWindow.SelectedSheets.Visible = False
    Selection.WrapText = True
    Selection.HorizontalAlignment = xlLeft
    Selection.VerticalAlignment = xlVAlignTop
[QUOTE]
    Selection.AutoFilter
[/QUOTE]
    
    Sheets("Report Creator").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Safety Accountability Dashboard").Select
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

MrTomasz

Board Regular
Joined
May 16, 2014
Messages
84
Office Version
  1. 365
Platform
  1. Windows
use:

VBA Code:
Sheets("Investigations").UsedRange.AutoFilter
    
Sheets("Actions").UsedRange.AutoFilter
 

bosko2

New Member
Joined
Nov 3, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
H
use:

VBA Code:
Sheets("Investigations").UsedRange.AutoFilter
   
Sheets("Actions").UsedRange.AutoFilter
Hi MrTomasz, thank you for giving it a crack. Unfortunately it still behaves the same way. The sheets titled 'Investigations' and 'Actions' are the master data sheets respectively, which has been filtered by the user. The new sheets which I want filtered are 'My team's investigations' and 'My team's investigations'. Or does the code you provided need to be placed in a different area of the code?

Thanks again mate,
MIke
 

bosko2

New Member
Joined
Nov 3, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
H

Hi MrTomasz, thank you for giving it a crack. Unfortunately it still behaves the same way. The sheets titled 'Investigations' and 'Actions' are the master data sheets respectively, which has been filtered by the user. The new sheets which I want filtered are 'My team's investigations' and 'My team's investigations'. Or does the code you provided need to be placed in a different area of the code?

Thanks again mate,
MIke
Sorry again MrTomasz, I changed the names of the sheets to the newly named sheets and it works!

Thank you so much, this has been stumping me for weeks!!
 

bosko2

New Member
Joined
Nov 3, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry to bug you again, now the second part of the script doesn't format:
VBA Code:
    Sheets("Actions").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("My team's actions").UsedRange.AutoFilter
[QUOTE]
    Selection.WrapText = True
    Selection.HorizontalAlignment = xlLeft
[/QUOTE]
 

MrTomasz

Board Regular
Joined
May 16, 2014
Messages
84
Office Version
  1. 365
Platform
  1. Windows
was it working before? in the above example first you call "Actions" sheet, next "My team's actions". Which one is correct?

maybe try:

VBA Code:
with Sheets("My team's actions").UsedRange
    .AutoFilter
    .WrapText = True
    .HorizontalAlignment = xlLeft
end with
 

bosko2

New Member
Joined
Nov 3, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
I'll put my code into 4 sections, hopefully that will make tings easier.
Note 1. The sheet called "Safety Accountability Dashboard" is the end product, in addition to two data sheets called 'My team's actions' and 'My team's investigations'.

Note: 2. The sheet called "Actions" houses all my companies actions and starts of hidden. The sheet called 'My team's actions' starts off as a blank worksheet that is hidden.

Note 3. The sheet called "Investigations" houses all my companies investigations and starts of hidden.

Note 4. The sheet called 'My team's investigations' starts off as a blank worksheet that is hidden.

Okay the first part of the code starts below (it is primarily taking values placed into drop-down boxes and will concurrently filter both the "Investigations" and "Actions" sheets (i.e. the master sheets):

VBA Code:
Sub Report_Builder()


With Sheets("Report Creator")
   If .Cells(2, 1) <> "" Then
      Sheets("Investigations").Range("A1").AutoFilter 14, .Cells(2, 1).Value
   End If
   If .Cells(2, 2) <> "" Then
      Sheets("Investigations").Range("A1").AutoFilter 15, .Cells(2, 2).Value
   End If
   If .Cells(2, 3) <> "" Then
      Sheets("Investigations").Range("A1").AutoFilter 16, .Cells(2, 3).Value
   End If
   .Select
End With

Sheets("Report Creator").Select

Sheets("Safety Accountability Dashboard").Visible = True

Sheets("Safety Accountability Dashboard").Select


The second part of the code copies the filtered data from the "Investigations" sheet and paste special into the blank 'My team's investigations" sheet.
VBA Code:
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
    Sheets("My team's investigations").UsedRange.AutoFilter
    Selection.WrapText = True
    Selection.HorizontalAlignment = xlLeft
    Selection.VerticalAlignment = xlTop


The third part of the code copies the filtered data from the "Actions" sheet and paste special into the blank 'My team's actions' sheet.
VBA Code:
    Set DbExtract = ThisWorkbook.Sheets("Actions")
    Set DuplicateRecords = ThisWorkbook.Sheets("My team's Actions")

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

    DbExtract.Range("A1:BF9999").SpecialCells(xlCellTypeVisible).Copy
    DuplicateRecords.Cells(1, 1).PasteSpecial
    
    Sheets("Actions").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("My team's actions").UsedRange.AutoFilter
    Selection.WrapText = True
    Selection.HorizontalAlignment = xlLeft
    Selection.VerticalAlignment = xlTop



The fourth part of the code ensures all the relevant sheets are either hidden or visible:
VBA Code:
 Sheets("Report Creator").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Safety Accountability Dashboard").Select


End



End Sub



The purpose of the code works, it talks filtered data from a user input, then it filters the relevant master data sheets, copies over to the already names sheets, and displays the "Safety Accountability Dashboard".

I'm having trouble formatting the two sheets that receive the filtered and copied data ('My team's investigations' and 'My team's actions'. To be more specific, I require the following formatting to be the same on the two new sheets ('My team's investigations' and 'My team's actions'):
  • Row one bold
  • Row one filter
  • Wrap text
  • Horizontal Alignment Left
  • Vertical Alignment Right
I have pasted the entire block below:
VBA Code:
Sub Report_Builder()


With Sheets("Report Creator")
   If .Cells(2, 1) <> "" Then
      Sheets("Investigations").Range("A1").AutoFilter 14, .Cells(2, 1).Value
   End If
   If .Cells(2, 2) <> "" Then
      Sheets("Investigations").Range("A1").AutoFilter 15, .Cells(2, 2).Value
   End If
   If .Cells(2, 3) <> "" Then
      Sheets("Investigations").Range("A1").AutoFilter 16, .Cells(2, 3).Value
   End If
   .Select
End With


Sheets("Report Creator").Select

Sheets("Safety Accountability Dashboard").Visible = True

Sheets("Safety Accountability Dashboard").Select



    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
    Sheets("My team's investigations").UsedRange.AutoFilter
    Selection.WrapText = True
    Selection.HorizontalAlignment = xlLeft
    Selection.VerticalAlignment = xlTop

    Set DbExtract = ThisWorkbook.Sheets("Actions")
    Set DuplicateRecords = ThisWorkbook.Sheets("My team's Actions")

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

    DbExtract.Range("A1:BF9999").SpecialCells(xlCellTypeVisible).Copy
    DuplicateRecords.Cells(1, 1).PasteSpecial
    
    Sheets("Actions").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("My team's actions").UsedRange.AutoFilter
    Selection.WrapText = True
    Selection.HorizontalAlignment = xlLeft
    Selection.VerticalAlignment = xlTop
    
    Sheets("Report Creator").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Safety Accountability Dashboard").Select


End



End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,163
Messages
5,640,512
Members
417,148
Latest member
pe3087te

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top