0

I'm extremely new to this and most off my codes have come from recording.

I'm trying to complete a basic search using a filter box and then copy the cells onto a different work sheet ready to start again.

This is the macro I recorded, the problem I keep having is rather than "copy what is in Cell B7 in places the contents of what I record in the macro....="=meal", I need this to be Cell B7 from the concur expenses tab?

Sub MySub()
    Range("A13:G36").Select
    Selection.ClearContents

    Range("B7").Select
    Selection.Copy

    Sheets("Detail for selection").Select
    ActiveSheet.Range("$A$1:$F$55").AutoFilter Field:=6, Criteria1:="=*meal*", _
        Operator:=xlAnd

    Application.CutCopyMode = False
    Selection.Copy

    Sheets("Concur Expenses").Select
    Range("A12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Sheets("Detail for selection").Select
    ActiveSheet.Range("$A$1:$F$55").AutoFilter Field:=6

    Sheets("Concur Expenses").Select
    Cells.Select
    Cells.EntireColumn.AutoFit

    Range("B7").Select
End Sub

Any helps greatly appreciated,

Thanks

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Rachjulie
  • 1
  • I suggest you start browsing the internet for examples on filtered data and copy/paste data to a certain range. Your current code is imo a bad start. – JvdV Dec 12 '18 at 12:59
  • I recommend to read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to your code first, before doing anything else. – Pᴇʜ Dec 12 '18 at 13:12
  • I'd have a look at getting excel cell data, as a first venture into code. You are asking the wrong question, google "get value from excel cell in VBA" i agree with @JvdV and Peh also. – Nathan_Sav Dec 12 '18 at 13:20
  • Thanks, as I said I'm still learning. I managed to solve my problem and ended up with the following code which worked perfectly. – Rachjulie Dec 13 '18 at 16:24
  • ' Search Macro ' ' Application.ScreenUpdating = False Sheets("Concur Expenses").Range("A13:f100").ClearContents Sheets("Detail for selection").Select Worksheets("Detail for selection").Range("$A$1:$F$55").AutoFilter Field:=6, Criteria1:="*" & Worksheets("Concur Expenses").Range("B7").Value & "*", _ Operator:=xlAnd Range("A2:F100").Copy Sheets("Concur Expenses").Range("A13").PasteSpecial Sheets("Concur Expenses").Select Range("B7").Select End Sub – Rachjulie Dec 13 '18 at 16:25

0 Answers0