One part of VBA works, second doesn't

B

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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
use:

VBA Code: 
Sheets("Investigations").UsedRange.AutoFilter
    
Sheets("Actions").UsedRange.AutoFilter
 
Upvote 0
H
MrTomasz said:
use:

VBA Code: 
Sheets("Investigations").UsedRange.AutoFilter
   
Sheets("Actions").UsedRange.AutoFilter
Click to expand...
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
 
Upvote 0
bosko2 said:
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
Click to expand...
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!!
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
VBA for sheet formatting
Replies
1
Views
341
bosko2
B
B
  • Question
Formatting a range of data as a Table
Replies
4
Views
270
bosko2
B
E
  • Solved
[VBA] File saving does not work/new version not uploaded
Replies
4
Views
489
NdNoviceHlp
NdNoviceHlp
B
  • Question
Assistance with formatting and adding a filter using VBA
Replies
4
Views
572
JoeMo
J
R
  • Question
[VBA] Trying to use sheet names in listbox as source to run code - can't figure out reference
Replies
1
Views
564
rossross
R

Forum statistics

Threads
1,214,646
Messages
6,120,717
Members
448,985
Latest member
chocbudda

Share this page

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
Back
Top