Macro to utilise Hidden Sheets

[DaveRadford]

Afternoon,

I have a couple of macros that i call on that refer to other worksheets within the workbook, i want to hide these so the users of the spreadsheet will not see the data and will not know what is happening in the background.

Bit of Background Info:

I am attempting to it this way as i have a list in a form, depending on what the user selects would determine what is selectable in the next form control.

As data within the list will have the duplicates codes but different SubCodes i am filtering and then creating a list for named range to display in form. Users will nto want to see duplicate data in the 1st list hence this way.

My issue is i dont want users to see what im doing and i dont want them to have to see the sheet.

Any help would be appreciated.

My macros are:

Sub WSFilter()
Sheets("WS").Select
Columns("C:C").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$C$9").AutoFilter Field:=1, Criteria1:=Range("L1").Value, _
Operator:=xlAnd
End Sub

Sub CreateWSList()
Columns("A:B").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("WSList").Select
Columns("A:B").Select
ActiveSheet.Paste

Sheets("WS").Select
Selection.AutoFilter
Sheets("Week 16").Select

Thanks.

 

[Andrew Poulsom]

Just avoid selecting, eg:

Sub WSFilter()
    With Sheets("WS")
        .Columns("C:C").AutoFilter
        .Range("$A$1:$C$9").AutoFilter Field:=1, Criteria1:=.Range("L1").Value, _
Operator:=xlAnd
    End With
End Sub

 

[DaveRadford]

Many thanks Andrew that worked a treat. I can understand the with sheets part however i think im running in to trouble with the syntax of the when copying etc.

The Second macro copied before copies this filtered data in to a seperate worksheet (minus the filter) so that a named range can be created for a validation box...and in my case a userform.

Another quick question is....if i am now using 'with sheets' does this mean that i no longer have to specify the sheet to return to "week 16" as i hated having to do this, as there will be "Week 17" etc and if im not moving sheets then im really hoping it is not necessary.

Sub CreateWSList()

With Sheets("WS")
.Columns("A:B").Select 'Selects Data to copy
Application.CutCopyMode = False
Selection.Copy 'Copies Data
Sheets("WSList").Select 'Selects new Sheet
.Columns("A:B").Select 'Selects columns to paste to
ActiveSheet.Paste 'pastes the data

Sheets("WS").Select 'selects previous sheet
Selection.AutoFilter 'turns the filter off
Sheets("Week 16").Select 'goes back to the original form

End With

End Sub

Thanks,

 

[Andrew Poulsom]

Untested:

Sub CreateWSList()
    Application.CutCopyMode = False
    With Sheets("WS")
        .Columns("A:B").Copy Sheets("WSList").Range("A1")
        .AutoFilterMode = False
    End With
End Sub

 

[DaveRadford]

Magic, i got around the "Do you want to replace the contents of the destination cells?" by selecting the data in wslist and clearing its contents before its copied.

However one thin to note that if you use .delete instead .clearcontents then the defined name will lose its relationship with the cells.

Quick Question Andrew as i am 99% there :D

There is still one part of the code that unforunately is hardcoded:
.Range("$A$1:$C$9").AutoFilter Field:=1, Criteria1:=.Range("L1").Value,

I have a form that opens for users to make their selections and in it i have the usual:

ActiveCell = ListBox1.Value

Now i would like to copy what the user selects to L1 so that each time they select a new job number it will update the filter criteria.

I have something like this:

Private Sub ListBox1_Click()

ActiveCell = ListBox1.Value

With Sheets("WS")

"L1".value = ListBox1.Value

End With

End Sub

 

[Andrew Poulsom]

Try:

Sheets("WS").Range("L1").Value = ListBox1.Value

 

[DaveRadford]

That works great, thanks for all your help.

If you have a spare minute, i have a second post.

http://www.mrexcel.com/forum/showthread.php?p=2292727#post2292727

its to do with the same problem however not directly a result or macros with hidden sheets so hence the new thread.

Thanks,