Copying data based on multiple filter critera to new worksheets

M

MarkH95

New Member
Joined
Aug 6, 2015
Messages
2
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.

[TABLE="width: 505"]
<tbody>[TR]
[TD]Header1[/TD]
[TD]Header2[/TD]
[TD]Header3[/TD]
[TD]Header4[/TD]
[TD]Header5[/TD]
[TD]BF[/TD]
[/TR]
[TR]
[TD]UNKNOWNS[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF1 TEXTHERE[/TD]
[/TR]
[TR]
[TD]UNKNOWNS[/TD]
[TD]PV[/TD]
[TD]Placeholder2[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF2 TEXTHERE[/TD]
[/TR]
[TR]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]Placeholder3[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF3 TEXTHERE[/TD]
[/TR]
[TR]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]Placeholder4[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF4 TEXTHERE[/TD]
[/TR]
[TR]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]Placeholder4[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF7 TEXTHERE[/TD]
[/TR]
[TR]
[TD]UNKNOWNS[/TD]
[TD]PV[/TD]
[TD]Placeholder4[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF1 TEXTHERE[/TD]
[/TR]
[TR]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]Placeholder4[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF1 TEXTHERE[/TD]
[/TR]
[TR]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]Placeholder4[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF3 TEXTHERE[/TD]
[/TR]
[TR]
[TD]UNKNOWNS[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF4 TEXTHERE[/TD]
[/TR]
[TR]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF4 TEXTHERE[/TD]
[/TR]
[TR]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF7 TEXTHERE[/TD]
[/TR]
[TR]
[TD]UNKNOWNS[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF3 TEXTHERE[/TD]
[/TR]
[TR]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF4 TEXTHERE[/TD]
[/TR]
[TR]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]PV[/TD]
[TD]BF3 TEXTHERE[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Sort by date Sort by votes
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.

Code: 
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?
 
Upvote 0
onlyadrafter said:
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.

Code: 
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?
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA code for copy data from multiple sheets and print to Another defined sheet.
Replies
0
Views
341
sumit_engg1
S
E
  • Question Question
copy once to the last empty cell of a destination sheet
2 3
Replies
28
Views
2K
erfo
E
S
  • Solved
Return error if "Filter" doesn't return the required entry
Replies
2
Views
286
skittlz
S
I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
685
Peter_SSs
Peter_SSs
R
  • Question Question
Raw Data Filter
Replies
1
Views
178
RobP
R

Share this page

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