How to filter and copy a sheet from another

[bluegnu]

I have some code which whenever a sheet is accessed is copies the contents from a master worksheet and then applies an auto filter based upon the worksheet name.

The master sheet is call "All Features" and everything is copied off this. Then the filter code strips out the non-numeric characters from the sheet name and uses that as it's auto-filter. All the sheets are called something like "Inc 301" or "Inc201" for instance.

What would be better is if the filter could be applied to the "All Features" sheet before the copy is done, but when I tried this I could not get it to happen.

Here is the original code:

    Public Sub AllIncs()

   
    ActiveSheet.unprotect ("inform")
    
    
    Dim LastRow As Long
    Dim FirstRow As Long
    Dim LastCol As Integer
    Dim FirstCol As Integer

    ' Find the FIRST real row
    FirstRow = Sheets("All Features").Cells.Find(What:="*", _
      SearchDirection:=xlNext, _
      SearchOrder:=xlByRows).Row
      
    ' Find the FIRST real column
    FirstCol = Sheets("All Features").Cells.Find(What:="*", _
      SearchDirection:=xlNext, _
      SearchOrder:=xlByColumns).Column
    
    ' Find the LAST real row
    LastRow = Sheets("All Features").Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

    ' Find the LAST real column
    LastCol = Sheets("All Features").Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
        
'Select the ACTUAL Used Range as identified by the
'variables identified above
With Worksheets("All Features")
.unprotect
  .Range(.Cells(FirstRow, FirstCol), .Cells(LastRow, LastCol - 1)).Copy _
  Destination:=Range("A1")
End With
'Get Filter by sheetname
   Dim SheetName As String
   SheetName = Mid(ActiveSheet.Name, 4, Len(ActiveSheet.Name))
   
   If InStr(SheetName, " ") = 0 Then
      RemoveSpaces = SheetName
   Else
      SheetName = Left(SheetName, InStr(SheetName, " ") - 1) _
      & Right(SheetName, Len(SheetName) - InStr(SheetName, " "))
    End If
'Make Sure the sheet is a normal Inc Sheet
If Mid(ActiveSheet.Name, 1, 3) = "Inc" Then
  Range("A1").Select
   Selection.AutoFilter Field:=6, Criteria1:=SheetName
    Else
    Selection.AutoFilter Field:=6, Criteria1:="REPLACE THIS"
    End If
    Cells(1, 1).Select

    ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True

 End Sub

I thought I could add the filter to the:

With Worksheets("All Features")
.unprotect
  .Range(.Cells(FirstRow, FirstCol), .Cells(LastRow, LastCol - 1)).Copy _
  Destination:=Range("A1")
End With

bit but that doesn't seem to do anything.

any help would be appreciated.

 

[bluegnu]

this is Excel 2003 by the way

 

[SkipVought]

Is there a reason for copying data to all these different tabs?

Could you not accomplish the same result on ONE sheet from a dropdown control, and filter accordingly?

But you could filter on the master sheet, copy the data and pastespecial xlpastevalues.

I also do not understand why you are acquiring the first and last row/column. If your table is a valid table, there is not need to do that at all.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bluegnu]

To take each point seperately, the need for additional sheets is not my requirement. The people who use this don't like people using the master sheet and like the "visualness" of having seperate spreadsheets I believe. Not necessarily a great idea but it's what they want.

As for applying the filter to the master sheet, thats the first bit I'm stuck on. I can't seem to get the filter to apply in order to work out how to do the copy.

The acquiring the first/last row/column is something I asked myself as I was posting this question! You are right about this.

 

[SkipVought]

Turn on your macro record, apply the filter, observe your recorded code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bluegnu]

The number of times I've read that!

I'm not explaining myself very well at all. I understand how to apply the filter, the bit I'm struggling with is how to apply the filter from another sheet. The moment I select the master sheet it then runs a whole load of VBA and I get myself caught in a loop.

This is the code I tried (as an extract from my original code:

    Sheets("All Features").Select
    Sheets("All Features").unprotect
    Sheets("All Features").AutoFilter Field:=6, Criteria1:=SheetName
    Sheets(OriginatorSheetName).Select

but that just ping pongs be backwards and forwards

 

[SkipVought]

You should rarely ever use the select and activate methods for ANYTHING.

    with Sheets("All Features")
       .unprotect
       .AutoFilter Field:=6, Criteria1:=SheetName
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bluegnu]

Thanks for the response.

OK, I tried your suggestion and I get an error "Named Argument not found." Does that mean that it isn't supported in Excel 2003?

 

[SkipVought]

Criteria1:=SheetName

What is the VALUE of SheetName?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bluegnu]

Sheet name will be something like 301 or 303. It's be a 1 to 3 digit character.

 

[SkipVought]

"...will be something like ..."

I did not ask what the value is LIKE, I asked "What is the VALUE of SheetName" when the error occured. Use your debug tools.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

with Sheets("All Features")
.unprotect
.AutoFilter Field:=6, Criteria1:= 301
End With

For instance - you will need to populate that with a loop so:

For each sht in thisworkbook.worksheets
   if nm <> "res" [COLOR=green]'so it doesn;t process all features sheet[/color]
     nm = right(sht.name,3) [COLOR=green]'may need to do more here if sheet names are not consistently the same length[/color]
     with Sheets("All Features")
       .unprotect
       .AutoFilter Field:=6, Criteria1:= nm
       [COLOR=green]'copy code goes here[/color]
     End With
   end if
Next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bluegnu]

Thanks for the responses.

Skip, sorry for the ambiguity but I genuiunely meant it was something like 301 or 303 as I tried it on several sheets. I have sheet names ranging from "Inc 2" to "Inc302". The code I have strips out the Inc and any space and leaves me with the numeric. In the above examples, the debug shows the value to be Sheetname = "2" and Sheetname = "302" respectively.

Geoff, I'm not totally sure what your code will do - Am I right in saying that it will update every sheet in when the event is triggered?

thanks again for the help

 

[xlbo]

bluegnu - yes it should do - bit of a typo tho - nm sefinition should come 1st. What the code does is to loop through all the sheets in the workbook, check their name, extract the last 3 digits and use that value to filter the all features sheet:

For each sht in thisworkbook.worksheets
   nm = right(sht.name,3) 'may need to do more here if sheet names are not consistently the same length

   if nm <> "res" 'so it doesn;t process all features sheet
          with Sheets("All Features")
       .unprotect
       .AutoFilter Field:=6, Criteria1:= nm
       'copy code goes here
     End With
   end if
Next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bluegnu]

Thanks Geoff,

I still have the same problem - error "Named Argument not found" appears and ".AutoFilter Field:=6, Criteria1:= nm" is highlighted. nm = "302

 

[xlbo]

.Range("A1:Z1000").AutoFilter Field:=6, Criteria1:= nm


where A1:Z1000 is the range you want to filter

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bluegnu]

Thanks Geoff. I'll be playing around with this for a bit - I'm still struggling to get it to work and may just abandon it in favour of Skips original suggestion if I can get it acceptable to the users! (put it all in 1 sheet)

many thanks for your help.