Mark has attended:
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel PowerPivot course
Excel Dashboards for Business Intelligence course
Pasting A Value Into A Filter
I've used macro recorder to paste a value from sheet and then paste it into a filter on a separate sheet, it's returned the below code;
ActiveSheet.Range("$A$1:$S$2970").AutoFilter Field:=1, Criteria1:="5834"
The 5834 is the value that I've copied and pasted and I'd like to know how I'd change it to be whatever the value is that I've copied.
Thanks,
Mark
RE: Pasting A Value Into A Filter
Hi Mark,
Apologies for the late response, we're down to a handful of trainers due to illness and we're very busy training. Would it be possible to give me the complete code or all the steps you recorded? At the moment, I can't quite see exactly what you're doing with he data. E.g. what are your filtering criteria?
Kind regards
Marius Barnard
Excel Trainer
RE: Pasting A Value Into A Filter
Sheets("Validations").Visible = True
Sheets("Validation Mainframe").Select
Range("C4").Select
Selection.Copy
Sheets("Validations").Select
ActiveSheet.Range("$A$1:$S$2970").AutoFilter Field:=1, Criteria1:="5834"
RE: Pasting A Value Into A Filter
Hi Mark,
Below is some code which might help. In my example, I did the following:
I declared a variable (CopiedNumber) which will store the value you enter into a cell in a worksheet. (The value you wish to filter by)
Then, I told Excel to go to the sheet where the filter runs, and use the number stored in the variable as the filter criteria.
It works fine when I test it. You simply need to replace the sheet names and cell references to suit your worksheets.
Just make sure Option Explicit is enabled when using variables.
Sub Macro1()
Dim CopiedNumber As Single
CopiedNumber = Sheets("Sheet1").Range("A1")
Sheets("Sheet2").Select
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$10").AutoFilter Field:=1, _
Criteria1:=CopiedNumber
End Sub
I hope this helps.
Kind regards
Marius
RE: Pasting A Value Into A Filter
Hi,
Thanks for your help so far, I've written my next piece of code which copies the active line from the sheet once the filter has been applied and pastes it into a new workbook.
However, when pasting I can only write the code to get it to paste into cell A2 when I'd want to paste it into a new row underneath thus avoiding the "arrivals board" effect.
Thanks,
Mark
Dim CopiedNumber As Single
Dim wsL As Worksheet
Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim Lst As ListObject
Application.ScreenUpdating = False
CopiedNumber = Sheets("Validation Mainframe").Range("C4")
Sheets("Validations").Select
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$S$2970").AutoFilter Field:=1, _
Criteria1:=CopiedNumber
Set wsL = Sheets("Validations")
Set Lst = Sheets("Validations").ListObjects(1)
With Lst.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Set ws = Sheets("Validation Changes")
Set rng = Lst.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible).Copy _
Destination:=ws.Range("A2")
End If
Application.ScreenUpdating = True
RE: Pasting A Value Into A Filter
Hi Mark,
Thanks for your post. Below is some code which copies a selection, then goes to another sheet, finds the last row of data, goes to the next empty cell and pastes the copied data there.
Range("A2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Of course, you want to change the sheet names and cell references to work in your own sheets.
I hope you find this code useful.
Kind regards
Marius Barnard
