vba code runs in f8 but not in f5

Question

So I have looked at the same question and answer but it does not help with my problem.

here is the code

    Private Sub Update_To_Search_Click()
 Dim itmx As ListItem
 Set itmx = ListView1.FindItem(Number_Selected.Text, lvwText) ', , lvwPartial)
 If itmx Is Nothing Then
 MsgBox "No Record", vbCritical
 Else
 ListView1.ListItems(itmx.Index).Selected = True
 ListView1.SetFocus
 End If


Dim myindex As Integer
 Number_Selected.Text = Me.ListView1.SelectedItem
 myindex = Me.ListView1.SelectedItem.Index
 TextBox2.Text = Me.ListView1.ListItems.Item(myindex).SubItems(1)
 TextBox3.Text = Me.ListView1.ListItems.Item(myindex).SubItems(2)
 TextBox4.Text = Me.ListView1.ListItems.Item(myindex).SubItems(3)
 TextBox5.Text = Me.ListView1.ListItems.Item(myindex).SubItems(4)
 TextBox6.Text = Me.ListView1.ListItems.Item(myindex).SubItems(5)
 TextBox7.Text = Me.ListView1.ListItems.Item(myindex).SubItems(6)
 TextBox8.Text = Me.ListView1.ListItems.Item(myindex).SubItems(7)
 TextBox9.Text = Me.ListView1.ListItems.Item(myindex).SubItems(8)
 TextBox10.Text = Me.ListView1.ListItems.Item(myindex).SubItems(9)


'Go get the selected line


Dim Base As Worksheet, GoodData As Worksheet
Dim Rng As Range

Set GoodData = Sheets("GoodDBData")
Set Base = Sheets("Data")
Set wb = Workbooks("Staffing LogV1.7.xlsm")
Set listview = wb.Sheets("ListView")
Set fromsearch = wb.Sheets("FromDB")
Set Rng = Base.Range("A20:A28")

    FilePath = CStr(wb.Sheets("Data").Cells(2, "A"))
    filename = "DB.xlsx"
    Application.ScreenUpdating = False
  Set DB = Workbooks.Open(FilePath & "\" & filename)
   Application.ScreenUpdating = True


Rng.Copy
DB.Sheets("Search Criteria").Range("A2").PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

With DB.Sheets("DB")
    With .Rows(1)
    Selection.AutoFilter
    Selection.AutoFilter
    End With
End With


Dim rCrit1 As Range, rCrit2 As Range, rCrit3 As Range, rCrit4 As Range, rCrit5 As Range, rCrit6 As Range, rCrit7 As Range, rCrit8 As Range
Dim rRng1 As Range

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set rCrit1 = Sheets("Search Criteria").Range("A2")
Set rCrit2 = Sheets("Search Criteria").Range("B2")
Set rCrit3 = Sheets("Search Criteria").Range("C2")
Set rCrit4 = Sheets("Search Criteria").Range("D2")
Set rCrit5 = Sheets("Search Criteria").Range("E2")
Set rCrit6 = Sheets("Search Criteria").Range("F2")
Set rCrit7 = Sheets("Search Criteria").Range("G2")
Set rCrit8 = Sheets("Search Criteria").Range("H2")

Set rRng1 = Sheets("DB").Range("A1").CurrentRegion

With rRng1
If rCrit1.Value <> "" Then
    .AutoFilter field:=11, Criteria1:=rCrit1.Value, Operator:=xlOr
End If

If rCrit2.Value <> "" Then
.AutoFilter field:=7, Criteria1:=rCrit2.Value, Operator:=xlOr
End If

If rCrit3.Value <> "" Then
    .AutoFilter field:=13, Criteria1:=rCrit3.Value, Operator:=xlOr
End If

If rCrit4.Value <> "" Then
    .AutoFilter field:=14, Criteria1:=rCrit4.Value, Operator:=xlOr
End If

If rCrit5.Value <> "" Then
    .AutoFilter field:=16, Criteria1:=rCrit5.Value, Operator:=xlOr
End If

If rCrit6.Value <> "" Then
    .AutoFilter field:=30, Criteria1:=rCrit6.Value, Operator:=xlOr
End If

If rCrit7.Value <> "" Then
    .AutoFilter field:=32, Criteria1:=rCrit7.Value, Operator:=xlOr
End If

If rCrit8.Value <> "" Then
    .AutoFilter field:=37, Criteria1:=rCrit8.Value, Operator:=xlOr
End If

End With


Application.EnableEvents = True
Application.ScreenUpdating = True


End sub

The following still does not copy and paste the criteria to look for. For some reason it only copies blanks no data is entered in Searcriteria. rangeA2.

Rng.Copy
DB.Sheets("Search Criteria").Range("A2").PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

I'm at a lost and I'm looking for any help I could get. Thank you very much

refactor the code to [remove all selects](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and work directly with the objects. then see how it works :). @SiddharthRout has provided a nice example of this in his answer. — Scott Holtzman, Aug 07 '18 at 14:01
I agree with Scott. The code that I gave below, copies and then pastes immediately... Try it and let me know your observaitons — Siddharth Rout, Aug 07 '18 at 14:05
the code below worked once, but now it still doesn't copy the information anymore :-( — Val S, Aug 07 '18 at 14:58

score 2 · Accepted Answer · answered Aug 07 '18 at 14:02

2

Check this for me.

Replace your code

    Base.Select
    Base.Range("A7:A15").Select
    Selection.Copy

    FilePath = CStr(wb.Sheets("Data").Cells(2, "A"))
    FileName = "DB.xlsx"
    Application.ScreenUpdating = False
    Set Db = Workbooks.Open(FilePath & "\" & FileName)
    Application.ScreenUpdating = True

    Sheets("Search Criteria").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Sheets("DB").Select
    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter

With

Dim Rng As Range

Set Rng = Base.Range("A7:A15")

FilePath = CStr(wb.Sheets("Data").Cells(2, "A"))
FileName = "DB.xlsx"

Application.ScreenUpdating = False
Set Db = Workbooks.Open(FilePath & "\" & FileName)
Application.ScreenUpdating = True

Rng.Copy
Db.Sheets("Search Criteria").Range("A2").PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

With Db.Sheets("Search Criteria")
    With .Rows(1)
        '~~> REST OF THE CODE
    End With
End With

Now Try it?

answered Aug 07 '18 at 14:02

Siddharth Rout

147,039
17
206
250

Actually it worked the first time around and now it again doesn't copy anything. Any idea why? – Val S Aug 07 '18 at 14:30
Did you amend your code to remove `.Select` and work with Objects? – Siddharth Rout Aug 07 '18 at 15:39
can you update your question with the new code that you are using? – Siddharth Rout Aug 07 '18 at 19:08
Are you sure you pasted the right code? Your code will fail on the line `MsgBox Not “Record, vbCritical` – Siddharth Rout Aug 07 '18 at 20:00
I do nto see anything wrong with the code. If the code runs in F8 now then add one line `DoEvents` after `Rng.Copy` and try again? – Siddharth Rout Aug 08 '18 at 13:15

vba code runs in f8 but not in f5

1 Answers1