Column Select not working in VBA

[Ignition1]

No idea why VBA is throwing out a run-time error with this bit of code -

Workbooks("Prospects.xls").Activate
ActiveSheet.Range("J2:J2000").Select
Selection.Copy
ThisWorkbook.Activate
Worksheets("BNIREF").Activate
ActiveSheet.Range("C2").Select
ActiveSheet.Paste
Worksheets("BNI All Prospects").Activate
ActiveSheet.Range("C2").Select
ActiveSheet.Paste 'Copy to All Pros
Application.CutCopyMode = False
Worksheets("BNIREF").Activate
Columns("B:C").Select

Basically after I select those Columns I'm just doing a simple Replace, I have the code for that -

Selection.replace What:="1", Replacement:=""

So why doesn't it like Columns("B:C").Select ??

 

[Ignition1]

Actually I figured it out. I had to put ActiveSheet.Columns blah blah blah.

**** VBA. :D

 

[wigi]

Glad you figured it out.

Here is more optimized code for you to look at. It will do the same as yours, but the screen will not flicker (a.o.)

Sub optimized_code()

    With Workbooks("Prospects.xls").Worksheets("NAMEOFTHESHEET").Range("J2:J2000")
        .Copy Destination:=ThisWorkbook.Worksheets("BNIREF").Range("C2")
        .Copy Destination:=ThisWorkbook.Worksheets("BNI All Prospects").Range("C2")
    End With
    
    ThisWorkbook.Worksheets("BNIREF").Columns("B:C").Replace What:="1", Replacement:=""

End Sub

Replace "NAMEOFTHESHEET" with the actual sheet name from which you want to copy.

Wigi

 

[Ignition1]

Nice, thanks very much. I'm not that familiar with some of the functions - like With.

I optimised instead doing -

Workbooks("Prospects.xls").ActiveSheet.Range("A2:A2000").Copy ThisWorkbook.Worksheets("BNIREF").Range("A2")
Workbooks("Prospects.xls").ActiveSheet.Range("A2:A2000").Copy ThisWorkbook.Worksheets("BNI All Prospects").Range("A2")

And also threw in the - Application.ScreenUpdating = False - at the start.