Help with VB for Filtering

fiesta

Board Regular
Joined
Nov 4, 2005
Messages
84
Hi,
I have a main sheet, "sheet1" which I filter using the AutoFilter on the Data
Menu along with the VB coding I copied from a book i'm reading. ie:
Sub Copy_with_Autofilter()
Dim rng As Range

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset((1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Copy _
Sheets("Sheet2").Range("A6")
End With
End Sub

The coding works very well, with one exception, it overwrites any data thats
already in sheet2.
Is there any code that I can add to the above so that it will automatically
add data to the next empty row in sheet2.
Will be grateful for any help.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Hi

Try changing
Sheets("Sheet2").Range("A6")
to
Sheets("Sheet2").Range("A" & sheets("sheet2").cells(rows.count,"A").end(xlup).row +1)


Tony
 
Upvote 0
Hi Tony
Thanks very much, that works perfect.
Can I ask you another question on the same subject, is it possible to
automate the Autofilter procedure without using the Data Menu and if so
have you any idea what the VB coding would be.
Thanks once again for you help.
 
Upvote 0
Hi

What do you mean by "automate the autofilter"? You can set the autofilter along the lines of
range("a1").autofilter
and turn it of
activesheet.autofiltermode = false

Tony
 
Upvote 0
Hi
Sorry Tony
In my Sheet1 are Racehorse Results that I download every day which I then filter via two coloumns D & F D=Distance & F=Age.
ie: D =5f & F =3yo this would then go in sheet2 there would be 12 sheets
altogether.
sheet2=5f, sheet3=6f and so on.
Is there any way that can be automated with VB code.
Thanks
 
Upvote 0
Hi

So you want to split the data fromsheet1 into 12 other sheets, based on certain requirements for each of the sheets?

If so:
1) What happens to any existing data on the output sheets
2) where are the relevant criteria for each sheet, and are they fixed / variable.
3) How do you want this to happen? On the press of a button, or whenever the workbook is opened???
4) How much data is there on sheet1, and how much is being copied to the output sheets?

Probably more questions but.....


Tony
 
Upvote 0
fiesta
Your example is just a little unclear to me. :rolleyes:

You have 12 sheets that you want to copy filtered data from .... correct ?
Each of these 12 sheets should be copied to "Sheet2" ... correct ?
What criteria would you use from each sheet ??
 
Upvote 0
Hi Tony
Yes thats right , the data in sheet1 will be split into 12 other sheets based
on the criteria in (2) under.

1) I want to keep all the existing data on the output sheets.
2) The relevant criteria is in Sheet1 column "D" and are variables.
ie: 5f, 6f, 7f, 1m, 1m2f, 1m4f, 1m5f, 2m, 2yo5f, 2yo6f, 2yo7f
and 2yo1m.
3) Ideally pressing a button.
4) At the moment just the daily download which would never be more
than 10 rows a day filtered to the appropriate sheets (12).
Hope this helps and thanks once again.
Sorry for the delay.
 
Upvote 0
How it works...
Procedure Goes does Column D of the Sheet "Sheet1". It then looks for a Sheet name that matches the Value in Column D. If the sheet is found it then automatically copies the row to the Target Sheet. If the sheet is not found it will ask the user if it wants the sheet made and the info copied to it.

Assumptions:
1- Source Sheet name is "Sheet1"

Code:
Public Sub CopyHorseData()
    With Sheets("Sheet1")
    For rw = 2 To Cells(65536, 4).End(xlUp).Row
        If Len(Trim(.Cells(rw, 4).Value)) > 0 Then
        If SheetExists(.Cells(rw, 4).Value) Then
            .Cells(rw, 4).EntireRow.Copy
            With Sheets(.Cells(rw, 4).Value)
            .Paste Destination:=.Range("A" & .Cells(65536, 4).End(xlUp).Row + 1)
            End With
        End If
        End If
    Next rw
    End With
    
    Application.CutCopyMode = False
End Sub

Private Function SheetExists(sname) As Boolean
'   Returns TRUE if sheet exists in the active workbook
    Dim OrigSh As String
    Dim x As Object
    
    OrigSh = ActiveSheet.Name
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(sname)
    If Err = 0 Then
        SheetExists = True
    Else
        ln1 = "The Sheet " & sname & " does not exist." & vbNewLine
        ln2 = "Do you want to make sheet and copy data ?"
        Style = vbQuestion + vbYesNo
        Title = "Make New Sheet"
        msg = ln1 & ln2
        
        If MsgBox(msg, Style, Title) = vbYes Then
            Application.ScreenUpdating = False
            Sheets.Add
            ActiveSheet.Name = sname
            SheetExists = True
            Sheets(OrigSh).Activate
            Application.ScreenUpdating = True
            Else
            SheetExists = False
            
        End If
    End If
End Function
 
Upvote 0
Help with VB coding for filtering

Hi Nimrod
Sorry for the delay in answering.
Your coding works perfectly after a minor adjustment to my data.
I would like to thank you very much for your help its very much
appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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