Data not pulling from secondary workbook

[DanSMT]

Hey all,

Im trying to pull data from a secondary workbook based on the information entered into an array of textboxes on a userform. Everything else appears to be working except when trying to pull the information over from the secondary workbook. Any ideas?

Private Sub testcb_Click()

Dim i2 As Long
For i2 = 1 To 20
If Me.Controls("RITB" & i2).Text = vbNullString Then
Else
Worksheets("fai").Copy after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Me.Controls("RITB" & i2).Text & " FAI"
ActiveSheet.Range("c4").Value = Me.Controls("RITB" & i2).Text
Dim source, target As Worksheet
Set target = Workbooks("New RI Form rev9 - Develop Mode - DO NOT USE.xlsb").Sheets("RI Log")

targetlastrow = target.Range("B" & target.Rows.Count).End(xlUp).Row

    For j = 2 To targetlastrow
        If target.Range("B" & j).Value = ActiveSheet.Range("c4").Value Then

        ActiveSheet.Range("A9").Value = "A"
        ActiveSheet.Range("B9").Value = target.Cells(rng.Row, 90).Value
        ActiveSheet.Range("C9").Value = target.Cells(rng.Row, 91).Value
        ActiveSheet.Range("D9").Value = target.Cells(rng.Row, 89).Value

        End If
    Next j

End If
Next

End Sub

 

[DanSMT]

You didn't make all of the changes I mentioned in post #2.

Thanks. Missed that last part

 

[DanSMT]

Think I screwed something up.

Private Sub testcb_Click()

Dim i2 As Long
For i2 = 1 To 20
If Me.Controls("RITB" & i2).Text = vbNullString Then
Else
Worksheets("fai").Copy after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Me.Controls("RITB" & i2).Text & " FAI"
ActiveSheet.Range("c4").Value = Me.Controls("RITB" & i2).Text

Dim target As Workbook
Set target = Workbooks("New RI Form rev9 - Develop Mode - DO NOT USE.xlsb")

Dim targetsheet As Worksheet
Set targetsheet = Sheets("RI Log")

targetlastrow = target.targetsheet.Range("B" & targetsheet.Rows.Count).End(xlUp).Row

    For j = 2 To targetlastrow
        If targetsheet.Range("B" & j).Value = ActiveSheet.Range("c4").Value Then

        ActiveSheet.Range("A9").Value = "A"
        ActiveSheet.Range("B9").Value = targetsheet.Cells(rng.Row, 90).Value
        ActiveSheet.Range("C9").Value = targetsheet.Cells(rng.Row, 91).Value
        ActiveSheet.Range("D9").Value = targetsheet.Cells(rng.Row, 89).Value

        End If
    Next j

End If
Next

End Sub

 

[johnnyL]

All of the places that you have targetsheet, you will need it to be target.targetsheet

From the targetlastrow = line on down.

 

[DanSMT]

All of the places that you have targetsheet, you will need it to be target.targetsheet

From the targetlastrow = line on down.

Fixed all of that.

Set targetsheet = Sheets("RI Log")

Error Subscript out of range again.

 

[DanSMT]

Private Sub testcb_Click()

Dim i2 As Long
For i2 = 1 To 20
If Me.Controls("RITB" & i2).Text = vbNullString Then
Else
Worksheets("fai").Copy after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Me.Controls("RITB" & i2).Text & " FAI"
ActiveSheet.Range("c4").Value = Me.Controls("RITB" & i2).Text

Dim target As Workbook
Set target = Workbooks("New RI Form rev9 - Develop Mode - DO NOT USE.xlsb")

Dim targetsheet As Worksheet
Set targetsheet = Sheets("RI Log")

targetlastrow = target.targetsheet.Range("B" & target.targetsheet.Rows.Count).End(xlUp).Row

    For j = 2 To targetlastrow
        If target.targetsheet.Range("B" & j).Value = ActiveSheet.Range("c4").Value Then

        ActiveSheet.Range("A9").Value = "A"
        ActiveSheet.Range("B9").Value = target.targetsheet.Cells(rng.Row, 90).Value
        ActiveSheet.Range("C9").Value = target.targetsheet.Cells(rng.Row, 91).Value
        ActiveSheet.Range("D9").Value = target.targetsheet.Cells(rng.Row, 89).Value

        End If
    Next j

End If
Next

End Sub

 

[johnnyL]

Are you positive there is a sheet called RI Log in the target workbook?

 

[johnnyL]

if so: Try changing:

Set targetsheet = Sheets("RI Log")

to:

Set targetsheet = target.Sheets("RI Log")

 

[DanSMT]

Are you positive there is a sheet called RI Log in the target workbook?

yes

 

[DanSMT]

new error now..

targetlastrow = target.targetsheet.Range("B" & target.targetsheet.Rows.Count).End(xlUp).Row

Object doesn't support this property or method

 

[johnnyL]

Since you made the last change, remove the target. from there down. so you just want to use targetsheet instead of target.targetsheet