0

I keep getting a 1004 error - im really trying to get my head round VBA and ive got a lot of this down! I recorded the macro and then edited it adding the user input box and wild card temrms which im sure would be;

"*" & search & "*"

My goal is to have the macro open the user input box when sheet SP is opened. Then search sheet "product table" for the user input. Copy the cell contents, and paste to cell in "SP"

My ultimate goal is to then search every other coloumn for the same string and copy to the bottom of the table ( the next empty row ) - But i would like to try and give this a go myself.

I am stuck on this 

Range("A1:I500").Select

this is where i get the error 1004

below is my script

Private Sub Worksheet_Activate()

    'do input box

    Dim search
    search = InputBox("What are you searching for?")


    'delete old data

    Application.ScreenUpdating = False
    Range("B7:I500").Select

    'do autofilter

    Sheets("Product Table").Select
    Range("A1:I500").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$I$809").AutoFilter Field:=1, Criteria1:="*" & search & "*", _
        Operator:=xlAnd
    Columns("A:I").Select
    Range("I1").Activate
    Selection.Copy
    ActiveSheet.Range("$A$1:$I$809").AutoFilter Field:=1
    Sheets("SP").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F16").Select
    Application.ScreenUpdating = True
End Sub

Does anyone have an idea why this is happening?

Community
  • 1
  • 1
Lewis Morris
  • 1,916
  • 2
  • 28
  • 39
  • 1
    Pleeeease, [**how to avoid using select/active statements**](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select):) – Dmitry Pavliv Feb 25 '14 at 20:41
  • one more tip: since you are working with multiply sheets in `Worksheet_Activate` event, add this line `Application.EnableEvents = False` in the beggining of the code and `Application.EnableEvents = True` in the end. See [**this link for details**](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) – Dmitry Pavliv Feb 25 '14 at 21:24

1 Answers1

0

I can't remember the exact syntax, but I think you need to focus your sheet:

Sheets("Product Table").Activate

Put this before you try and select the range

Edit

Ok. I've recreate the error, but not in the same place as you. I am getting the error at:

Range("B7:I500").Select

Can you double-check - I think the error may be caused because you are trying to select data in your current workbook from within Worksheet_Activate.

Edit 2

Right got it! The error is being caused, as I thought because you are trying to acces cells in a sheet that is not yet active. In order to resolve it you need to eplicitly reference the sheet name that is loaded in Worksheet_Activate, for example:

Sheets("MyWorkSheet").Activate
Sheets("MyWorkSheet").Range("B7:I500").Select

Obviously replace MyWorkSheet with the correct name.

Andrew
  • 2,315
  • 3
  • 27
  • 42