Solved: Code help for pulling-up data from another worksheet

[U_Shrestha]

Hello all,

I have attached a sample workbook. Basically, I have 3-sheets, viz.: DataPullUpPage, AuditIssues, StationInfo.

DataPullUpPage is the page in which a user enters a Station No. in B2. As Station # is entered, B3:B10 is filled with formula, which pulls station addresses from StationInfo Page.

Now, the audit issues page has all the itemized audit issues. A peculiar thing about this is that the table data looks like an MS Access table. Please note the Station Number, the issues belong to a particular Station Number.

To view audit issues for each Station, there are two buttons in DataPullUpPage and both has VB code. Now, the form's button pulls up data from AuditIssues Page but the ActiveX fails to do so. I even set the TakeFocusOnClick to false.

The ideal solution for me would be, as soon as a user enters a Station Number in B2 of DataPullUpPage and presses ENTER, the audit issues should fill in below A13:K13.

If this is not possible, then I would like to use ActiveX command button to have this result. Thanks.

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const DestSheet As String = "DataPullUpPage"
Const SourceSheet As String = "AuditIssues"
Dim StationNo As Long
Dim FoundRow As Double
Dim DestRow As Double
Dim iSource As Double, iDest As Double, LastRow As Double

On Error GoTo GetIssues_Error

Application.ScreenUpdating = False
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("B2")) Is Nothing Then

' Clear out old data
Me.Range("A13:A" & Me.Rows.Count).EntireRow.Delete

iSource = 2
iDest = 13
LastRow = Sheets(SourceSheet).Range("A" & Me.Rows.Count).End(xlUp).Row

' Find the first occurance of station number on the issues page.
While Sheets(SourceSheet).Cells(iSource, 1) <> Target.Value And iSource <= LastRow
iSource = iSource + 1
Wend

' If the station isn't found, exit
If iSource >= LastRow Then
MsgBox "Station Number does not exist"

Else

' Copy selected cells to the destination sheet
' Copy selected cells to the destination sheet
While Worksheets(SourceSheet).Cells(iSource, 1) = Target.Value
Sheets(SourceSheet).Range("C" & iSource & ":M" & iSource).Copy _
Me.Range("A" & iDest)
iSource = iSource + 1
iDest = iDest + 1
Wend
End If
End If

MyEnd:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

GetIssues_Error:
Resume MyEnd
End Sub

[U_Shrestha]

xld: That was really amazing! Wow. I had been looking for this type of data pull-up method since long time. Thank you so much.

[Bob Phillips]

It's mainly your code, I just tidied it up and added it to a worksheet change event.

[U_Shrestha]

xld: Instead of copying and pasting the exact data from "AuditIssues" to "DataPullUp", how can I just paste the values. I am getting circular reference error. I think the following lines need to be modified. Thanks.

' Copy selected cells to the destination sheet
While Worksheets(SourceSheet).Cells(iSource, 1) = Target.Value
Sheets(SourceSheet).Range("C" & iSource & ":M" & iSource).Copy _
Me.Range("A" & iDest)
iSource = iSource + 1
iDest = iDest + 1
Wend
End If

End If