VBA - Filter Possibility or Other Macro Oprions

[FawnCC]

Hi,

I am attempting to filter a list depending on a list in a different spreadsheet. The master list the one that has each variable while the filtered list has multiple entries for each different variable and I need to seperate each variable so that I can copy it to the tab with the title found on the master list.

I was hoping that I could use a shifting filter to be able to do this but I don't know how and if it isn't possible does anyone have a different idea? I may be too focused in the wrong direction and the codeI currently have wouldn't be useful in this so...

I could really use some help!

Thank you!

 

[JLGWhiz]

Hi FawnCC, Welcome to MrExcel Message Board and Forum
So that we use the same terminology.
Excel**********Local
Workbook = File or Spreadsheet
Sheet or Worksheet = Tab

I will be using the Excel terminology. Please provide the following.

If you have more than one workbook involved, then use the names of the workbooks when referring to them. i.e. Workbooks("Master.xlsx") or .xlsm or .xls if applicable.
Use the sheet names and the workbook they are in when referring to the sheets. Sheets("Data") in Workbooks("Master").
Then specify the location of the data you want to work with. i.e. Items to be matched are located in Master wkbk sht 1 Cells A2:A25. And where to look for the matches. i.e. Need to match with items located in column B of sheet1 of Workbooks("Report"), and what to do if found, i.e. and copy columns B:G of each matched row to Sheets("Data") on the next available row starting in Col A.

This degree of detail will get faster and better responses to your needs.

Here is a generic procedure that would look at a list of variables in column A of sheet one in the same workbook, then filter a range in sheet two and copy any items identified to sheet 3.

Sub filtcpy()
Dim sh1 As Worksheet, Sh2 As Worksheet, sh3 As Worksheet, lr As Long, rng As Range
Set sh1 = Sheets(1)
Set Sh2 = Sheets(2)
Set sh3 = Sheets(3)
lr = Sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Sh2.Range("A1:J" & lr)
For Each c In sh1.Range("A2:A5")
    rng.AutoFilter 2, c.Value, VisibleDropDown:=False
        If Sh2.Range("B2") <> c Then
            Range("A3:J" & lr).SpecialCells(xlCellTypeVisible).Copy sh3.Cells(Rows.Count, 1).End(xlUp)(2)
        Else
            rng.SpecialCells(xlCellTypeVisible).Copy sh3.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    rng.AutoFilter
Next
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Your problem should certainly be 'doable'. Although some further tweaks may be required (eg the target sheets already exist with data that you want kept), you might find some of the suggestions that I made in this thread useful.
If testing those, make sure you use a copy of your file(s) and note the assumptions that I made.
Provide further details if that looks like the sort of thing you need but are not able to modify the code yourself.

 

[FawnCC]

I know it's a stupid question but why is it 'Each c In sh1' and all the other places that shows up??

 

[FawnCC]

Ok, with the info you've provided and some of the other stuff I've looked at this is what I've come up with. However, I'm still having trouble with the looping through my department list.

Sub Filter_CopyPaste()

'Adding Transfer sheet

Sheets.Add
Sheets("Sheet1").Name = "Transfer"
Sheets("Budget Labor").Activate

Dim Crit As String
Dim Crit1 As String
Dim BL As Long
Dim DL As Long

BL = Workbooks("Labor Info").Sheets("Budget Labor").Cells(Rows.Count, 1).End(xlUp).Row
DL = Workbooks("Labor Macro").Sheets("Dept List").Cells(Rows.Count, 1).End(xlUp).Row

'Filter Labor sheet
Workbooks("Labor Info").Sheets("Budget Labor").Activate
ActiveSheet.Range("A1:E" & BL).AutoFilter Field:=5, Criteria1:="<>"

I need to loop trough the dept list here.. Range B2 & C2 to the end of the list because then it will get copied and pasted to a tab in yet another workbook so that each will have it's own tab and can be updated as information changes.

Crit = Workbooks("Labor Macro").Sheets("Dept List").Range("B2")
Crit1 = Workbooks("Labor Macro").Sheets("Dept List").Range("C2")

ActiveSheet.Range("A1:E" & BL).AutoFilter Field:=1, Criteria1:="*" & Crit & "*", _
Operator:=xlOr, Criteria2:="*" & Crit1 & "*"

Cells.Select
Selection.Copy
Sheets("Transfer").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

I have a good idea of how to get it copied to the different tabs because a different part of this macro is responsible for creating the tabs that I will be populating with this information.

End Sub

 

[FawnCC]

For those like myself that look for these things, find the question, and then don't always find the answer here is what worked for me. It filters my data using two criteria and goes through my master list line by line so that I can then put the filtered results into a different spreadsheet before it goes to the next line which will go somewhere else.

Dim BL As Long</SPAN>
Dim DL As Long</SPAN>
Dim iLoop As Integer</SPAN>


BL = Workbooks("Labor Info").Sheets("Budget Labor").Cells(Rows.Count, 1).End(xlUp).Row</SPAN>
DL = Workbooks("Labor Macro").Sheets("Dept List").Cells(Rows.Count, 1).End(xlUp).Row</SPAN>

'Filter Labor sheet</SPAN>
Workbooks("Labor Info").Sheets("Budget Labor").Activate</SPAN>
ActiveSheet.Range("A1:E" & BL).AutoFilter Field:=5, Criteria1:="<>"</SPAN>

For iLoop = 2 To DL</SPAN>
ActiveSheet.Range("A1:E" & BL).AutoFilter Field:=1, Criteria1:=Workbooks("Labor Macro").Sheets("Dept List").Cells(iLoop, 2).Value, Operator:=xlOr, Criteria2:=Workbooks("Labor Macro").Sheets("Dept List").Cells(iLoop, 3).Value

Next iLoop


Thank you everyone for your help!</SPAN>