Filter, Cut and Paste in a protected worksheet in VBA

tazrad

New Member
Joined
Aug 30, 2005
Messages
9
I work for people who are excel stupid. According to my boss, they cannot use the autofilter. So he wants a button. :devilish:

I want to design a button that when pushed would
go to the data worksheet perform a autofilter on the item then copy and paste the data into the worksheet with the button on it.

I have 19 filter items so I guess I need 19 buttons with labels of each divisions.

Is there any way to have one button label Report and the users choose their own division?

I am working with this code but it is too choppy. Any Suggestions?


Help Please with my bosses.

Code:
Sub Button1_Click()
'
' Button1_Click Macro
' 

'
    Sheets("Status - Item Detail Report").Select
    Selection.AutoFilter Field:=1, Criteria1:="Administration"
    Range("A1:H365").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A22").Select
    ActiveSheet.Paste
End Sub
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

How's this:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> Button1_Click()
    <SPAN style="color:#00007F">With</SPAN> Sheets("Status - Item Detail Report")
        .Unprotect "PasswordGoesHere"
        .AutoFilter Field:=1, Criteria1:="Administration"
        .Range("A1:H365").Copy Sheets("Sheet1").Range("A22")
        .Protect "PasswordGoesHere"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

As for the Criteria, you could use an InputBox (which will allow users to misspell, etc.), or build a simple user form with a Combo Box loaded with selections. You could also use a sheet change event with Data Validation on an unprotected cell.

Which one sounds the most reasonable to you?

Hope that helps,

Smitty
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
There is no need for 19 buttons.

What you should do is create a dropdown with the different departments.

This could be done either using Data>Validation..., or with a combobox from the Forms or Control Toolbox toolbar.

You would then refer to the value selected in this dropdown in the code for the filter.

By the way it might be better to use advanced filter.

With that you can copy the data to another location, and in code that other location can be on another worksheet.

Let say you have the dropdown either in A1 on Sheet1 or linked to it.
Code:
Sub Button1_Click()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim wsCrit As Worksheet
Dim LastRow As Long

    Set wsSrc = Worksheets("Status - Item Detail Report")
    Set wsDst = Worksheets("Sheet1")
    Set wsCrit = Worksheets.Add
    
    LastRow = wsSrc.Range("A" & Rows.Count).End(xlUp).Row
    wsCrit.Range("A1") = wsSrc.Range("A1")
    wsCrit.Range("A2") = wsDst.Range("A1")
    
    wsSrc.Range("A1:H" & LastRow).AdvancedFilter xlFilterCopy, wsCrit.Range("A1:A2"), wsDst.Range("B1")
    Application.DisplayAlerts = False
    wsCrit.Delete
    Application.DisplayAlerts = True
    
End Sub
 

tazrad

New Member
Joined
Aug 30, 2005
Messages
9
Code:
Sub Button1_Click()
    With Sheets("Status - Item Detail Report")
        .Unprotect "aaa"
        .AutoFilter Field:=1, Criteria1:="Administration"
        .Range("A1:H365").Copy Sheets("Sheet1").Range("A22")
        .Protect "aaa"
    End With
End Sub

I am getting an error in the autofilter line.  "Runtime error 448.  Named agrument not found.
 

tazrad

New Member
Joined
Aug 30, 2005
Messages
9
i got the code to work but i still have choppy screen where it is working to copy and paste. is there a way to hide the working part.

here is the code:
Code:
Button3_Click Macro
'
'

'
    With Sheets("Status - Item Detail Report").Select
        Selection.AutoFilter Field:=1, Criteria1:="Administration"
        Range("A1:H365").Select
        Selection.Copy
        Sheets("Sheet1").Select
        Range("A22").Select
        ActiveSheet.Paste
     End With
End Sub
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Put Application.ScreenUpdating = False at the beginning of the code and set it to true at the end.

Also note that you can generally eliminate Select statements:

Code:
        Range("A1:H365").Select 
        Selection.Copy 
        Sheets("Sheet1").Select 
        Range("A22").Select 
        ActiveSheet.Paste

can be:

Code:
Range("A1:H365").Copy Sheets("Sheet1").Range("A22")

Smitty
 

Watch MrExcel Video

Forum statistics

Threads
1,113,859
Messages
5,544,708
Members
410,630
Latest member
Maggie28
Top