VBA - Filter Possibility or Other Macro Oprions

FawnCC

New Member
Joined
Mar 29, 2013
Messages
19
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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,510
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 29, 2013
Messages
19
I know it's a stupid question but why is it 'Each c In sh1' and all the other places that shows up??
 

FawnCC

New Member
Joined
Mar 29, 2013
Messages
19
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

New Member
Joined
Mar 29, 2013
Messages
19
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

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
Top