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
 

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.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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