Copying data based on multiple filter critera to new worksheets

[MarkH95]

Hello,

I'm really sorry to bother everyone.

I am stuck with writing a complex macro and have been searching google for about a week and I've noticed many people come to this board and get assistance, so I too have come in search of help.

I am very new to VBA but I am trying to learn to improve excel based jobs at work. I have written a few very simple macros successfully so have very basic knowledge.

I am currently doing something with Autofilter, that works fine but the spreadsheet usually contains thousands of rows and I'm trying to write a macro to automate this to be quick and efficient.

I have one sheet of "master data" that I am filtering and copying to new sheets based on the filter criteria.

What I'm trying to do is this:

• Filter the data (via either autofilter or advanced filter) by around 60 different criteria. 50+ of the filters are on the same column (F).
• I'd like the macro to create a sheet with the same name as the filter criteria if it returns results. I have not yet found a way to do this, would the best way to be to create all the possible sheets I need and run code to delete the blanks?
• Copy the filtered data to the corresponding created sheet.
• Finally, I'd like the macro to fill the data it has copied in sheet 1 yellow, so that I can sweep up any that haven't been copied by using Autofilter by fill. This was also useful for when I was doing it manually to prevent me making duplicates. There will inevitably be anomolyous rows that don't have the proper text in them to filter so it would be easy for me to sweep these up manually if the copied data was filled on the master sheet.

Header1	Header2	Header3	Header4	Header5	BF
UNKNOWNS	PV	PV	PV	PV	BF1 TEXTHERE
UNKNOWNS	PV	Placeholder2	PV	PV	BF2 TEXTHERE
PV	PV	Placeholder3	PV	PV	BF3 TEXTHERE
PV	PV	Placeholder4	PV	PV	BF4 TEXTHERE
PV	PV	Placeholder4	PV	PV	BF7 TEXTHERE
UNKNOWNS	PV	Placeholder4	PV	PV	BF1 TEXTHERE
PV	PV	Placeholder4	PV	PV	BF1 TEXTHERE
PV	PV	Placeholder4	PV	PV	BF3 TEXTHERE
UNKNOWNS	PV	PV	PV	PV	BF4 TEXTHERE
PV	PV	PV	PV	PV	BF4 TEXTHERE
PV	PV	PV	PV	PV	BF7 TEXTHERE
UNKNOWNS	PV	PV	PV	PV	BF3 TEXTHERE
PV	PV	PV	PV	PV	BF4 TEXTHERE
PV	PV	PV	PV	PV	BF3 TEXTHERE

<tbody>
</tbody>

Above is an example sheet. I can't use the actual data as it's financial. The main filter criteria is the last column filtering for starts with BFX. X going from 1-52 (which represents weeks). I also want to pull out rows that contain certain text strings from other columns, eg. the unknowns in A and the placeholders in C. I've filled the other spaces with PV for placeholder value.

I have tried recording what I do but I don't think this helps me much as I don't know how to get it to "loop?" for every filter criteria, BF1*, BF2*, etc.

What I need help with is how to target the column of data and search for a particular string. I've tried INSTR but I can't get it to work at all when I try to target cells or ranges. I also need it to check through every cell in the column and copy everything that starts with the filter criteria.

I found this thread: http://www.mrexcel.com/forum/excel-...-copy-rows-based-criteria-new-sheet-file.html - While it seems to have the same sort of idea, I don't want only unique values, I want every row but sorted into the right sheet. I think that's the right lines but I don't know how to tweak it.

I also found this: http://www.mrexcel.com/forum/excel-...s-move-rows-another-sheet-based-criteria.html which does exactly what I want but only for 3 variables and they are exact as a pose to my partial text strings in a larger text string.

I'm really sorry for the long first post and I hope I conveyed what I meant concisely. I would greatly appreciate any assistance.

Thank you.

 

[onlyadrafter]

Hello,

This macro doesn't do all of your requirements, but it's a start. It will create sheets for all the different BF numbers and copy the relevant rows over.

Sub COPY_TO_SHEETS()
    Application.ScreenUpdating = False
    Range("G1").Value = "CHECK"
    Range("G2").Formula = "=left(F2,find("" "",F2)-1)"
    Range("G2").Copy
    Range("G3:G" & Range("F" & Rows.Count).End(xlUp).Row).PasteSpecial (xlPasteAll)
    Columns("G").Copy
    Range("G1").PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
    Range("G1:G" & Range("G" & Rows.Count).End(xlUp).Row).Select
    With Selection
    .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "AA1"), Unique:=True
    End With
    For Each MY_CELL In Range("AA2:AA" & Range("AA" & Rows.Count).End(xlUp).Row)
        Sheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Name = MY_CELL
        Sheets("Sheet1").Range("A1:F1").Copy Range("A1")
        Sheets("Sheet1").Select
    Next MY_CELL
    Columns("AA:AA").ClearContents
    For MY_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Range("A" & MY_ROWS & ":Z" & MY_ROWS).Copy
        With Sheets(Range("G" & MY_ROWS).Value)
            .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
        End With
    Next MY_ROWS
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

It does assume the only sheet in the workbook is the one with the source data.

Post back with further requirements, can't quite understand what you mean by PULL OUT OTHER ROWS and PLACEHOLDER requirements.

Is this of any use?

 

[MarkH95]

Hello,

This macro doesn't do all of your requirements, but it's a start. It will create sheets for all the different BF numbers and copy the relevant rows over.

Sub COPY_TO_SHEETS()
    Application.ScreenUpdating = False
    Range("G1").Value = "CHECK"
    Range("G2").Formula = "=left(F2,find("" "",F2)-1)"
    Range("G2").Copy
    Range("G3:G" & Range("F" & Rows.Count).End(xlUp).Row).PasteSpecial (xlPasteAll)
    Columns("G").Copy
    Range("G1").PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
    Range("G1:G" & Range("G" & Rows.Count).End(xlUp).Row).Select
    With Selection
    .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "AA1"), Unique:=True
    End With
    For Each MY_CELL In Range("AA2:AA" & Range("AA" & Rows.Count).End(xlUp).Row)
        Sheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Name = MY_CELL
        Sheets("Sheet1").Range("A1:F1").Copy Range("A1")
        Sheets("Sheet1").Select
    Next MY_CELL
    Columns("AA:AA").ClearContents
    For MY_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Range("A" & MY_ROWS & ":Z" & MY_ROWS).Copy
        With Sheets(Range("G" & MY_ROWS).Value)
            .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
        End With
    Next MY_ROWS
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

It does assume the only sheet in the workbook is the one with the source data.

Post back with further requirements, can't quite understand what you mean by PULL OUT OTHER ROWS and PLACEHOLDER requirements.

Is this of any use?

Thank you so much for taking the time to write that code and reply. I've spent the past quarter of an hour trying to work it out before replying to your post. It's actually amazing. It shreds the data properly and creates all the sheets and puts each row in it's relevant sheet. It's awesome. Thank you.

THe only sheet in the workbook is my source data so that's fine. What I mean by pull out other rows was also pull out say the unknowns (by targeting that substring of text) into their own sheet. So an additional filter.

You've really broken the back of this macro for me, hopefully I can work out the last details by examining your method and adding a few bits on.

Thank you so much for affording me your time. I am truly grateful.

Much obliged.