0

The VBA as seems to be glitchy (screen view jumps back and forth) and does not end back on the userform. Is there a way to refine the VBA to work better? and is there another way to place the row in a specific row on a worksheet?

Excel VBA - using userform and datasheet (workbook A). After inputting a number in up to 8 different textboxes on a userform. The textbox is linked to a datasheet. the vba gets the number from the datasheet and searches another workbook (Workbook B) for the number in Column A. After found it will copy and paste to a row on the sheet on (workbook a). the sequence will continue for the next textbox and the next etc.

Private Sub CommandButton83_Click()
Dim LastRow As Integer, i As Integer, erow As Integer

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow

If Cells(i, 1).Value = TextBox192.Value Then
Range(Cells(i, 1), Cells(i, 25)).Select
Selection.Copy
Workbooks.Open Filename:="C:location"

Workbooks("MainWorkBook").Worksheets("PatientData").Activate
Worksheets("PatientData").Select

ActiveSheet.Cells(3, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If
Next i

Workbooks("Workbook2").Worksheets("Roll Call").Activate

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 1).Value = TextBox193.Value Then
Range(Cells(i, 1), Cells(i, 25)).Select
Selection.Copy
Workbooks.Open Filename:="C:location"

Workbooks("MainWorkBook").Worksheets("PatientData").Activate
Worksheets("PatientData").Select
ActiveSheet.Cells(5, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
Application.CutCopyMode = False
End If
Next i


Workbooks("Workbook2").Worksheets("Roll Call").Activate

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 1).Value = TextBox194.Value Then
 Range(Cells(i, 1), Cells(i, 25)).Select
 Selection.Copy
 Workbooks.Open Filename:="C:location"

Workbooks("MainWorkBook").Worksheets("PatientData").Activate
 Worksheets("PatientData").Select
 ActiveSheet.Cells(7, 1).Select
 ActiveSheet.Paste
 ActiveWorkbook.Save
 Application.CutCopyMode = False
 End If
 Next i

 Workbooks("Workbook2").Worksheets("Roll Call").Activate

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 1).Value = TextBox195.Value Then
Range(Cells(i, 1), Cells(i, 25)).Select
Selection.Copy
Workbooks.Open Filename:="C:\location"

Workbooks("MainWorkBook").Worksheets("PatientData").Activate
Worksheets("PatientData").Select
ActiveSheet.Cells(9, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
Application.CutCopyMode = False
End If
Next i

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 1).Value = TextBox196.Value Then

Range(Cells(i, 1), Cells(i, 25)).Select
Selection.Copy
Workbooks.Open Filename:="C:Location of file"

Workbooks("MainWorkBook").Worksheets("PatientData").Activate
Worksheets("PatientData").Select

ActiveSheet.Cells(11, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
Application.CutCopyMode = False
End If
Next i

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 1).Value = TextBox197.Value Then
Range(Cells(i, 1), Cells(i, 25)).Select
Selection.Copy
Workbooks.Open Filename:="C:Location"

Workbooks("MainWorkBook").Worksheets("PatientData").Activate
Worksheets("PatientData").Select

ActiveSheet.Cells(13, 1).Select
ActiveSheet.Paste
 ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If
Next i

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 1).Value = TextBox198.Value Then
Range(Cells(i, 1), Cells(i, 25)).Select
Selection.Copy
Workbooks.Open Filename:="C:location"

Workbooks("MainWorkBook").Worksheets("PatientData").Activate
Worksheets("PatientData").Select

ActiveSheet.Cells(15, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
Application.CutCopyMode = False
End If
Next i


If OptionButton65.Value = True Then
Workbooks("Workbook2").Worksheets("Roll Call").Activate

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 1).Value = TextBox199.Value Then
Range(Cells(i, 1), Cells(i, 25)).Select
Selection.Copy
Workbooks.Open Filename:="C:location"

Workbooks("MainWorkBook").Worksheets("PatientData").Activate
Worksheets("PatientData").Select

ActiveSheet.Cells(17, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
Application.CutCopyMode = False
End If
Next i


End Sub

'Being able to refine the VBA to single function of looking for number provided in a textbox (which will change on every use) on a closed/open workbook and copy the row that matches the number onto the userform workbook in a specific row. Only as many as eight rows will be copied per use but mor than likely three will be used. So not all textboxes will have data entered every time.

puggle76
  • 1
  • 1

2 Answers2

0

The VBA as seems to be glitchy (screen view jumps back and forth)

The immediate answer to removing the 'glitchy' behaviour is to avoid using Select and Activate. For example:

Also - indenting the code properly helps with readability, maintenance and bug finding.

Finally. Add Option Explicit to the top of the module containing code. Always.

AJD
  • 2,400
  • 2
  • 12
  • 22
  • New to VBA do you have any tips to properly indent code? – puggle76 Jul 03 '19 at 23:22
  • is the option Explicit added the private sub line? As such: Option Explicit Command Button_83 () – puggle76 Jul 03 '19 at 23:43
  • `Option Explicit` is added as the first line at the top of any module. – AJD Jul 03 '19 at 23:50
  • Thank you for your help. It took me a bit to figure out how to accomplish a refined code. Its not perfect but it works a lot better, the code has one flash before the done message box. I tried adding the option explicit but it gets pushed to the previous code. Below is the code I came up with. – puggle76 Jul 06 '19 at 02:50
  • The 'one flash' will be the screen updating as you turn screen updating back on - so to be expected. worth opening another question (perhaps in Code Review if your code works as expected - but read the help and guidance on how to ask carefully) as there is still room for improvement in your code. – AJD Jul 06 '19 at 21:35
  • Yes, `Option Explicit` will get pushed to the top of the module, that is where it belongs. Moving to the front of other code is perfectly fine. – AJD Jul 06 '19 at 21:36
0
Private Sub CommandButton83_Click()
Dim c As Range
Dim d As Range, u As Range, o As Range, p As Range, q As Range, r As Range, _
 s As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet
Dim Outcome As Worksheet


Application.Workbooks.Open Filename:="C:\Users\Desktop\Workbook1.xml"

Application.ScreenUpdating = False
Set Source = Application.Workbooks("WorkBook1").Worksheets("Sheet1")
Set Target = ThisWorkbook.Worksheets("DataSheet")
Set Outcome = ThisWorkbook.Worksheets("Data")

    For Each c In Source.Range("A3:A" & Source.Cells(Rows.Count, "A").End(xlUp).Row)
        j = Outcome.Cells(Rows.Count, 3).End(xlUp).Row + 1
        If c = TextBox192.Value Then Outcome.Rows(j).Value = Source.Rows(c.Row).Value
    Next c

    For Each d In Source.Range("A1:A" & Source.Cells(Rows.Count, "A").End(xlUp).Row)
        j = Outcome.Cells(Rows.Count, 3).End(xlUp).Row + 1
        If d = TextBox193.Value Then Outcome.Rows(j).Value = Source.Rows(d.Row).Value
    Next d

    For Each n In Source.Range("A1:A" & Source.Cells(Rows.Count, "A").End(xlUp).Row)
        j = Outcome.Cells(Rows.Count, 3).End(xlUp).Row + 1
        If n = TextBox194.Value Then Outcome.Rows(j).Value = Source.Rows(n.Row).Value
    Next n

    For Each o In Source.Range("A1:A" & Source.Cells(Rows.Count, "A").End(xlUp).Row)
         j = Outcome.Cells(Rows.Count, 3).End(xlUp).Row + 1
         If o = TextBox195.Value Then Outcome.Rows(j).Value = Source.Rows _  
         (o.Row).Value
    Next o

    For Each p In Source.Range("A1:A" & Source.Cells(Rows.Count, "A").End(xlUp).Row)
         j = Outcome.Cells(Rows.Count, 3).End(xlUp).Row + 1
         If p = TextBox196.Value Then Outcome.Rows(j).Value = Source.Rows _   
         (p.Row).Value
     Next p

    For Each q In Source.Range("A1:A" & Source.Cells(Rows.Count, "A").End(xlUp).Row)
          j = Outcome.Cells(Rows.Count, 3).End(xlUp).Row + 1
         If q = TextBox197.Value Then Outcome.Rows(j).Value = Source.Rows _
         (q.Row).Value
    Next q

     For Each r In Source.Range("A1:A" & Source.Cells(Rows.Count, "A").End(xlUp).Row)
         j = Outcome.Cells(Rows.Count, 3).End(xlUp).Row + 1
         If r = TextBox198.Value Then Outcome.Rows(j).Value = Source.Rows _
         (r.Row).Value
     Next r

     For Each s In Source.Range("A1:A" & Source.Cells(Rows.Count, "A").End(xlUp).Row)
          j = Outcome.Cells(Rows.Count, 3).End(xlUp).Row + 1
         If s = TextBox199.Value Then Outcome.Rows(j).Value = Source.Rows _  
         (s.Row).Value
     Next s

    Application.ScreenUpdating = True

    Workbooks("WorkBook1").Close

    MsgBox "done!"

End Sub
puggle76
  • 1
  • 1