Make Code work from Splash Page "Line Update"

[Nlhicks]

My code currently only works if I go to Sheet2 but I really want it to work from "Line Update". Not sure how to make this happen I am open to suggestions.
Sub TryUpdate()

Dim RngRange01 As Range

rngRang01 = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:P" & rngRang01).SpecialCells(xlCellTypeVisible).Select


If Worksheets("Line Update").Range("C11") <> Worksheets("Line Update").Range("F11") Then
Worksheets("Sheet2").Range("B2:B" & rngRang01).Value = Worksheets("Line Update").Range("F11").Value

End If

If Worksheets("Line Update").Range("C12") <> Worksheets("Line Update").Range("F12") Then
Worksheets("Sheet2").Range("C2:C" & rngRang01).Value = Worksheets("Line Update").Range("F12").Value

End If

If Worksheets("Line Update").Range("C13") <> Worksheets("Line Update").Range("F13") Then
Worksheets("Sheet2").Range("D2:D" & rngRang01).Value = Worksheets("Line Update").Range("F13").Value

End If

If Worksheets("Line Update").Range("C14") <> Worksheets("Line Update").Range("F14") Then
Worksheets("Sheet2").Range("E2:E" & rngRang01).Value = Worksheets("Line Update").Range("F14").Value

End If

If Worksheets("Line Update").Range("C15") <> Worksheets("Line Update").Range("F15") Then
Worksheets("Sheet2").Range("F2:F" & rngRang01).Value = Worksheets("Line Update").Range("F15").Value

End If

If Worksheets("Line Update").Range("C16") <> Worksheets("Line Update").Range("F16") Then
Worksheets("Sheet2").Range("G2:G" & rngRang01).Value = Worksheets("Line Update").Range("F16").Value

End If

If Worksheets("Line Update").Range("C17") <> Worksheets("Line Update").Range("F17") Then
Worksheets("Sheet2").Range("H2:H" & rngRang01).Value = Worksheets("Line Update").Range("F17").Value

End If

If Worksheets("Line Update").Range("C18") <> Worksheets("Line Update").Range("F18") Then
Worksheets("Sheet2").Range("I2:I" & rngRang01).Value = Worksheets("Line Update").Range("F18").Value

End If

I had a shorter version of this created and it worked for a little while but it quit working so I am not sure it was the right one.

 

[Fluff]

If this works

Okay I added Worksheets("Sheet2").Activate

Then you are not running the code I supplied.

 

[Nlhicks]

 

[Fluff]

That image does not really tell me anything.
In what way is the code not working?

 

[Nlhicks]

Okay I got it to work called Sh as Object and then set LstSh = to Sh and at the end of my code I put in LstSht.Activate. Now it takes the values from Line Update opens Sheet 2 makes the changes and then returns to Line Update. Thank you for all of you help

 

[Nlhicks]

"your"

 

[Fluff]

If you actually said what the problem was, then we can help, there is no need to activate any sheet for the code I posted to work.

 

[Nlhicks]

I rewrote your code again Fluff and made sure all variables were declared correctly and it does work from line update and does it perfectly. Sry, I got it messed up somehow and it was not working but once I did it again it was fine. Thank you so much

 

[Fluff]

Glad it's sorted & thanks for the feedback.

 

[Nlhicks]

Any chance you can help me sort out this:

There are two problems
1) If the data is on the last row of sheet 2, the filter will work but my Line Update sheet does not get populated with the data in the filtered row
2) The pop-up window asking for the bus number happens even if the first condition is met and completed. I only want it to ask for a bus number if after the first three filters are applied there is more than one line of data. Then the fourth filter should be applied after the user enters the TO Bus number. Otherwise the code works.



Sub FindRightCell()

Dim LineUpdate As Worksheet, Sheet2 As Worksheet
Dim x As Long
Dim y As Range, z As Range
Dim c As Range
Dim resp As VbMsgBoxResult
Dim ans As Variant

Set LineUpdate = Worksheets("Line Update")
Set Sheet2 = Worksheets("Sheet2")

With Sheet2.Range("A1")

If LineUpdate.Range("D5").Value <> "" Then .AutoFilter field:=10, Criteria1:="*" & LineUpdate.Range("D5") & "*"
If LineUpdate.Range("D6").Value <> "" Then .AutoFilter field:=11, Criteria1:="*" & LineUpdate.Range("D6") & "*"
If LineUpdate.Range("D7").Value <> "" Then .AutoFilter field:=12, Criteria1:=LineUpdate.Range("D7")

With Sheet2.UsedRange
For Each y In .Rows
If Application.CountA = 1 Then

Worksheets("Line Update").Range("C11").Value = Worksheets("Sheet2").Range("B2:B685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C12").Value = Worksheets("Sheet2").Range("C2:C685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C13").Value = Worksheets("Sheet2").Range("D2:D685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C14").Value = Worksheets("Sheet2").Range("E2:E685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C15").Value = Worksheets("Sheet2").Range("F2:F685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C16").Value = Worksheets("Sheet2").Range("G2:G685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C17").Value = Worksheets("Sheet2").Range("H2:H685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C18").Value = Worksheets("Sheet2").Range("I2:I685").SpecialCells(xlCellTypeVisible)
End If

If Application.CountA > 1 Then GoSub Item_Open

If LineUpdate.Range("H6").Value <> "" Then .AutoFilter field:=13, Criteria1:=LineUpdate.Range("H6")

Worksheets("Line Update").Range("C11").Value = Worksheets("Sheet2").Range("B2:B685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C12").Value = Worksheets("Sheet2").Range("C2:C685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C13").Value = Worksheets("Sheet2").Range("D2:D685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C14").Value = Worksheets("Sheet2").Range("E2:E685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C15").Value = Worksheets("Sheet2").Range("F2:F685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C16").Value = Worksheets("Sheet2").Range("G2:G685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C17").Value = Worksheets("Sheet2").Range("H2:H685").SpecialCells(xlCellTypeVisible)
Worksheets("Line Update").Range("C18").Value = Worksheets("Sheet2").Range("I2:I685").SpecialCells(xlCellTypeVisible)

Exit For
Next
End With

End With

Item_Open:
Dim sValue As String

sValue = Application.InputBox("Enter the TO: Bus Number here, Thank you.")
Worksheets("Line Update").Range("H6").Value = sValue

Debug.Print sValue

End Sub

 

[Fluff]

As you already have a thread for this question, you need to stick to it.

Also when posting code please use code tags, it makes it a lot easier to read How to Post Your VBA Code