PDA

View Full Version : Reset Autofilter and Unselect specific numbers



woolen
04-23-2012, 10:04 AM
Hello! I already have vba that selects a range from Worksheet named "Datos" and pastes it in a worksheet named "Specific Data". This pasted data is then used to automatically make a graph. The problem is that the range changes each time new data is pasted.

What I need: I need to add to the VBA below so that once the data is pasted in the worksheet "Specific Data", a filter will be reset for Column I only (reset the filter to everything selected), and then always UNSELECT the numbers 0 and $0, that appear in column I.

Please help, and thank you in advance!!








Sub Show_Data_and_Graph()
Dim LR As Long
' Show_Data_and_Graph
Macro
' This macro should find the data for the product selected, copy and
paste it into another worksheet, and change the graph to reflect the data. Also,
the results of the data should be copied and pasted from the "Datos"
sheet.
'


Application.ScreenUpdating = False

With Sheets("Datos")


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

.Range("A61:M" & LR).AutoFilter Field:=1,
Criteria1:=Sheets("Herramienta").Range("C3").Value

Sheets("See Data").UsedRange.Clear

.AutoFilter.Range.Copy
Sheets("See
Data").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone,
SkipBlanks:=False,
Transpose:=False
.AutoFilterMode =
False

End
With

Sheets("Datos").Select


For val1 = 62 To 674 Step 1


Cells(val1, 1).Select


If Cells(val1, 1) = Sheets("herramienta").Range("C3").Value
Then
ActiveCell.Offset(0, 14).Select
Range(ActiveCell,
ActiveCell.Offset(6, 1)).Copy


Sheets("Specific Data").Select

Range("O2").Select
Selection.PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:=
_
xlNone, SkipBlanks:=False,
Transpose:=False
Exit For


Else
End If
Next val1



Sheets("see data").Rows(1).Font.Bold =
True
Sheets("see
data").Select
Selection.Interior.ColorIndex =
19

Application.CutCopyMode =
False
Application.ScreenUpdating =
True

Application.Goto Sheets("Specific
Data").Range("A1"), True
End Sub