Data not pulling from secondary workbook

D

DanSMT

Board Regular
Joined
Sep 13, 2019
Messages
203
Office Version
  1. 2013
Platform
  1. Windows
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?

VBA Code: 
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
 
Sort by date Sort by votes

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
My apologies. Your correct. That has fixed the problem. Now I just need to pile in the remainder of information I need to transfer between sheets. Only spot I may struggle is when I have to adjust data over based on if a cell already is loaded with data.
 
Upvote 0
So you are getting data now from the target workbook?
 
Upvote 0
Yes. But I am going to run in to a new issue.

If the code is trying to put information into F9 but it already has data how can I place data into the cell and move to next column if it already has data.

IE:


If ActiveSheet.Range("F9").Value = "" Then ActiveSheet.Range("F9").Value = targetsheet.Cells(j, 26).Value

IF F9 full then place in G9
IF G9 full then place in H9
etc..
etc..
All the way to placing into O9
 
Upvote 0
I've also found that it is only bring the last first found line of information to the sheet and then stops.
 
Upvote 0
Something like below

VBA Code: 
Private Sub testcb_Click()

Workbooks("PPAP Template.xlsm").Activate

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 = target.Sheets("RI Log")

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

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

            'A
            If ActiveSheet.Range("A9").Value = "" Then _
            ActiveSheet.Range("A9").Value = "A"
            If ActiveSheet.Range("B9").Value = "" Then _
            ActiveSheet.Range("B9").Value = targetsheet.Cells(j, 90).Value
            If ActiveSheet.Range("C9").Value = "" Then _
            ActiveSheet.Range("C9").Value = targetsheet.Cells(j, 91).Value
            If ActiveSheet.Range("D9").Value = "" Then _
            ActiveSheet.Range("D9").Value = targetsheet.Cells(j, 89).Value
            If ActiveSheet.Range("F9").Value = "" Then _
            ActiveSheet.Range("F9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("G9").Value <> "" Then _
            ActiveSheet.Range("G9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("H9").Value <> "" Then _
            ActiveSheet.Range("H9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("I9").Value <> "" Then _
            ActiveSheet.Range("I9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("J9").Value <> "" Then _
            ActiveSheet.Range("J9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("K9").Value <> "" Then _
            ActiveSheet.Range("K9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("L9").Value <> "" Then _
            ActiveSheet.Range("L9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("M9").Value <> "" Then _
            ActiveSheet.Range("M9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("N9").Value <> "" Then _
            ActiveSheet.Range("N9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("O9").Value <> "" Then _
            ActiveSheet.Range("O9").Value = targetsheet.Cells(j, 26).Value
            'B
            If ActiveSheet.Range("A10").Value = "" Then _
            ActiveSheet.Range("A10").Value = "B"
            If ActiveSheet.Range("B10").Value = "" Then _
            ActiveSheet.Range("B10").Value = targetsheet.Cells(j, 93).Value
            If ActiveSheet.Range("C10").Value = "" Then _
            ActiveSheet.Range("C10").Value = targetsheet.Cells(j, 94).Value
            If ActiveSheet.Range("D10").Value = "" Then _
            ActiveSheet.Range("D10").Value = targetsheet.Cells(j, 92).Value
            If ActiveSheet.Range("F10").Value = "" Then _
            ActiveSheet.Range("F10").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("G10").Value <> "" Then _
            ActiveSheet.Range("G10").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("H10").Value <> "" Then _
            ActiveSheet.Range("H10").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("I10").Value <> "" Then _
            ActiveSheet.Range("I10").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("J10").Value <> "" Then _
            ActiveSheet.Range("J10").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("K10").Value <> "" Then _
            ActiveSheet.Range("K10").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("L10").Value <> "" Then _
            ActiveSheet.Range("L10").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("M10").Value <> "" Then _
            ActiveSheet.Range("M10").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("N10").Value <> "" Then _
            ActiveSheet.Range("N10").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("O10").Value <> "" Then _
            ActiveSheet.Range("O10").Value = targetsheet.Cells(j, 26).Value

        
        End If
    Next j

End If
Next

End Sub
 
Upvote 0
Is this close?

VBA Code: 
            'A
            If ActiveSheet.Range("A9").Value = "" Then _
            ActiveSheet.Range("A9").Value = "A"
            If ActiveSheet.Range("B9").Value = "" Then _
            ActiveSheet.Range("B9").Value = targetsheet.Cells(j, 90).Value
            If ActiveSheet.Range("C9").Value = "" Then _
            ActiveSheet.Range("C9").Value = targetsheet.Cells(j, 91).Value
            If ActiveSheet.Range("D9").Value = "" Then _
            ActiveSheet.Range("D9").Value = targetsheet.Cells(j, 89).Value
            If ActiveSheet.Range("F9").Value = "" Then _
            ActiveSheet.Range("F9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("F9").Value <> "" Then If ActiveSheet.Range("G9").Value = "" Then _
            ActiveSheet.Range("G9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("G9").Value <> "" Then If ActiveSheet.Range("H9").Value = "" Then _
            ActiveSheet.Range("H9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("H9").Value <> "" Then If ActiveSheet.Range("I9").Value = "" Then _
            ActiveSheet.Range("I9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("I9").Value <> "" Then If ActiveSheet.Range("J9").Value = "" Then _
            ActiveSheet.Range("J9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("J9").Value <> "" Then If ActiveSheet.Range("K9").Value = "" Then _
            ActiveSheet.Range("K9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("K9").Value <> "" Then If ActiveSheet.Range("L9").Value = "" Then _
            ActiveSheet.Range("L9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("L9").Value <> "" Then If ActiveSheet.Range("M9").Value = "" Then _
            ActiveSheet.Range("M9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("M9").Value <> "" Then If ActiveSheet.Range("N9").Value = "" Then _
            ActiveSheet.Range("N9").Value = targetsheet.Cells(j, 26).Value
            If ActiveSheet.Range("N9").Value <> "" Then If ActiveSheet.Range("O9").Value = "" Then _
            ActiveSheet.Range("O9").Value = targetsheet.Cells(j, 26).Value
 
Upvote 0
This doesn't feel right to me but it is based on the code you just posted:

VBA Code: 
    For j = 2 To targetlastrow
        If targetsheet.Range("B" & j).Value = ActiveSheet.Range("c4").Value Then
'
'            'A
            If ActiveSheet.Range("A9").Value = "" Then
                ActiveSheet.Range("A9").Value = "A"
                ActiveSheet.Range("B9").Value = targetsheet.Cells(j, 90).Value
                ActiveSheet.Range("C9").Value = targetsheet.Cells(j, 91).Value
                ActiveSheet.Range("D9").Value = targetsheet.Cells(j, 89).Value
             End If
'
'
            For i = 6 To 15                                                         ' Loop through columns F:O to find first blank cell in that range of row 9
                If LenB(WorksheetFunction.Trim(Cells(9, i))) = 0 Then
                    EmptyColumnCell = i
                    Exit For
                End If
            Next
'
            ActiveSheet.Cells(9, EmptyColumnCell).Value = targetsheet.Cells(j, 26).Value
'
'-----------------------------------------------------------------------------------
'
'            'B
            If ActiveSheet.Range("A10").Value = "" Then
                ActiveSheet.Range("A10").Value = "B"
                ActiveSheet.Range("B10").Value = targetsheet.Cells(j, 93).Value
                ActiveSheet.Range("C10").Value = targetsheet.Cells(j, 94).Value
                ActiveSheet.Range("D10").Value = targetsheet.Cells(j, 92).Value
             End If
'
            For i = 6 To 15                                                         ' Loop through columns F:O to find first blank cell in that range of row 10
                If LenB(WorksheetFunction.Trim(Cells(10, i))) = 0 Then
                    EmptyColumnCell = i
                    Exit For
                End If
            Next
'
            ActiveSheet.Cells(10, EmptyColumnCell).Value = targetsheet.Cells(j, 26).Value
'
'-----------------------------------------------------------------------------------
'
        End If
    Next j
 
Upvote 0
Solution
It works with some minor adjustments!

ActiveSheet.Cells(10, EmptyColumnCell).Value = targetsheet.Cells(j, 26).Value

just needed to be

ActiveSheet.Cells(10, EmptyColumnCell).Value = targetsheet.Cells(j, 27).Value

THank you so much for your help!

Just need to figure out a way to ignore case sensitivity from a textbox to a spreadsheet lookup then I should be good to go! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
input data from another workbook
Replies
1
Views
407
atditiljazi
A
A
  • Question
Importing cell values from multiple workbooks in one folder
Replies
2
Views
770
Albertu95
A
D
  • Solved
Pulling data from sheet to table on another sheet with excel VBA
Replies
1
Views
755
DanSMT
D
T
  • Question
Insert/Delete Columns issue via VBA
Replies
1
Views
657
Tdorman
T
muhammad susanto
  • Question
VBA: Pull Data From Multiple Sheet With Specific Sheet & Cell
Replies
0
Views
2K
muhammad susanto
muhammad susanto

Forum statistics

Threads
1,214,985
Messages
6,122,605
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top