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
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