filtering rows

**peteknopp** · 02-16-2010, 07:28 AM

is it because highlighted line has EXTRACTEDDATA and elsewhere it reads EXTRACTEDLIST

I edited the macro to read extractedlist and the macro ran but with no data on the extractedlist sheet

**romperstomper** · 02-16-2010, 07:41 AM

Small change to the code:

Code:

Sub ExtractData()
'JBeaucaire 2/16/2010
Dim LR As Long
Dim wksExtract as Worksheet
Application.ScreenUpdating = False

Set wksExtract = Sheets("ExtractedList")
wksExtract.Cells.Clear

With Sheets("Data")
LR = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("F1") = "Key"
.Range("F2:F" & LR).FormulaR1C1 = "=ISNUMBER(MATCH(RC1,MasterList!C1,0))"
.Range("F:F").AutoFilter Field:=1, Criteria1:="TRUE"
.Range("A1:E" & LR).Copy wksExtract.Range("A1")
.AutoFilterMode = False
.Range("F:F").ClearContents
End With

Application.ScreenUpdating = True
wksExtract.Activate
Beep
End Sub

**peteknopp** · 02-16-2010, 08:00 AM

all sorted.

changed from extracteddata to extractedlist in the macro wording and my problem was i was running bootcamp with mac leopard and windows side by side.

I then copied new data from the excel file open on the mac into the data file on win 7 and thats when data disappeared on the extracted sheet.

I then copied the file from the mac to win 7, opened it, copied the new data, pasted into the data sheet and the macro worked fine.

The mac really does not like vbm and my fault for copying data from an open program.

Once again, many thanks and sorry for the rambling posts but we got there in the end!

**romperstomper** · 02-16-2010, 08:06 AM

JBeaucaire's code assumes you have the data sheet active when you start. If you don't (e.g. you have the ExtractedList sheet active), it won't work properly.
Office 2008 doesn't support VBA but prior versions do, and supposedly the next version will again, from what I hear. Your alternative in 2008 is to use Applescript.

**JBeaucaire** · 02-16-2010, 11:46 AM

No, that last line of code just brings up the extracted list onscreen. Sorry about the typo.

I don't use a MAC, so I can't give you Mac specifics about macro usage. This is how I would install the macro into my workbook:

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

**romperstomper** · 02-16-2010, 11:55 AM

As mentioned, Office 2008 doesn't support VBA. Sad, but true.

**JBeaucaire** · 02-16-2010, 11:55 AM

Originally Posted by romperstomper

JBeaucaire's code assumes you have the data sheet active when you start. If you don't (e.g. you have the ExtractedList sheet active), it won't work properly.

Hehe, it doesn't do that. I've used a With Sheets("Data") syntax to point all the code that works on that sheet to connect to it properly. So it works regardless of the active sheet.

The issue may be Mac-related, and I can't speak to that.

**romperstomper** · 02-16-2010, 11:59 AM

Yes it does:

Code:

LR = Range("A" & Rows.Count).End(xlUp).Row

Your last row variable depends on the active sheet.

**JBeaucaire** · 02-16-2010, 12:43 PM

Duh, you're right, it does. But you guessed incorrectly at which sheet should be active...my bad for making you guess.

Code:

Option Explicit

Sub ExtractData()
'JBeaucaire  2/16/2010
Dim LR As Long
Application.ScreenUpdating = False

Sheets("ExtractedList").Cells.Clear

    With Sheets("Data")
        LR = .Range("A" & .Rows.Count).End(xlUp).Row      'moved to here
        .Range("F1") = "Key"
        .Range("F2:F" & LR).FormulaR1C1 = "=ISNUMBER(MATCH(RC1,MasterList!C1,0))"
        .Range("F:F").AutoFilter Field:=1, Criteria1:="TRUE"
        .Range("A1:E" & LR).Copy Sheets("ExtractedList").Range("A1")
        .AutoFilterMode = False
        .Range("F:F").ClearContents
    End With

Application.ScreenUpdating = True
Sheets("ExtractedData").Activate
Beep
End Sub