Posts by CSKLC

CSKLC · Aug 28th 2017

Re: VBA, to read a cell contents and use it as a filter

Quote from rory;799016
Welcome to the forum.

You need:
Code
machine = Range("B4").Value
Ideally you would specify the sheet as well for safety.
Display More

Thanks for the welcome and thanks for the code, it worked nicely first time. I'm getting back into using Excel and Access after many years break, been discovering how much I've forgotten, looking forward to learning a bit from the forum.
Cheers
Chris

CSKLC · Aug 28th 2017

I have written the code below mostly from key stroke macros. I'm stuck at trying to have the variable "machine" read the contents of the cell $B$4 and use it as a filter. The cell $B$4 is a VLOOKUP from a list. The code in question are the 4 lines from the DIM statement. The rest works fine. Any brilliant suggestions?

Code

Sub Macro1()
'
' Select Machine by Name Macro
'
' Keyboard Shortcut: Ctrl+l
'
  Dim machine As Variant
  machine = Array("$B$4")
    Sheets("Main Sheet").Select
    ActiveSheet.Range("$A$1:$O$36").AutoFilter Field:=3, Criteria1:=(machine)
    Range("C3:D14,G3:H14").Select
    Range("G3").Activate
    Selection.End(xlToLeft).Select
    Range("C3:D14,G3:G14,I3:J14").Select
    Range("I3").Activate
    Selection.Copy
    Sheets("Machine Sheet (P)").Select
    Range("B31").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Main Sheet").Select
    ActiveSheet.Range("$A$1:$O$36").AutoFilter Field:=3
    Sheets("Machine Sheet (P)").Select
    Range("B31").Select
    
End Sub

Display More