Macro to utilise Hidden Sheets

DaveRadford

Board Regular
Joined
Feb 18, 2010
Messages
63
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Just avoid selecting, eg:

Code:
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
 
Upvote 0
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,
 
Upvote 0
Untested:

Code:
Sub CreateWSList()
    Application.CutCopyMode = False
    With Sheets("WS")
        .Columns("A:B").Copy Sheets("WSList").Range("A1")
        .AutoFilterMode = False
    End With
End Sub
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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