Command button from one sheet

[jcrusade]

I have spent a ton of time trying to get this scenario to work. I have a main page in Excel which has a slew of command buttons we will call this sheet ("Home") and one of the buttons I just cannot get to work the way I need it to.

Scenario:
I click a command button which needs to copy data from another worksheet based on the user input that was being searched on and then copy that data to a third worksheet.

Here is the code I have now:

Private Sub CommandButtonSvr_Click()
Dim LastRow, i
Sheets ("UIS-Domain").Select
Sheets("UIS-Domain").Range ("A:IV")
LastRow = Range("C" & Rows.Count).End(xlUp).Row
Sheets("SheetTmp").Columns("A:IV").ClearContents
objectClass = InputBox("Please enter the objectClass:")
If objectClass = "" Then
Exit Sub
End If
For i = 1 To LastRow
If Cells(i, "C").Value = objectClass Then
Cells(i, "C").EntireRow.Copy Destination:= _
Sheets("SheetTmp").Range("A" & Rows.Count).End(xlUp). _
Offset(1, 0)
ctr = ctr + 1
End If
Next
If ctr = 0 Then
MsgBox "There was no match for " & objectClass, vbOKOnly, "Not Found"
Else
Sheets("SheetTmp").Columns("A:IV").AutoFit
Sheets("SheetTmp").Select
End If
End Sub

The line put in orange is the line not working I have tried so many different ways I just cannot get it to work. If I run this code without
Sheets ("UIS-Domain").Select
Sheets("UIS-Domain").Range ("A:IV")
on the worksheet where the data is instead of the worksheet ("Home") it works. Please advise.

Thank you

 

[p45cal]

Well, that orange line is just a reference to some cells, the line doesn't do anything.
What were you hoping it would do?

 

[jcrusade]

I was hoping it would reference the correct worksheet to do the query on. Basically I have lets say 3 worksheets Home, UIS-Domain, sheetTmp. All the data resides in the UIS-Domain worksheet the command button lives on the Home worksheet I need to click the button on the home worksheet have it look at the UIS-Domain worksheet run the query which is an input box which searches column c on the objectClass that was entered into the input box take that data and copy it to SheetTmp.

THis all works if I run it from the UIS-Domian worksheet. I need to run it from the home worksheet.

I hope that makes sense.

Thanks

 

[p45cal]

Try:

Private Sub CommandButtonSvr_Click()
Dim LastRow, i
[COLOR=Red]With Sheets("UIS-Domain")[/COLOR]
  LastRow = [SIZE=6][COLOR=Red][B].[/B][/COLOR][/SIZE]Range("C" & [SIZE=6][COLOR=Red][B].[/B][/COLOR][/SIZE]Rows.Count).End(xlUp).Row
  Sheets("SheetTmp").Columns("A:IV").ClearContents
  objectClass = InputBox("Please enter the objectClass:")
  If objectClass = "" Then
    Exit Sub
  End If
  For i = 1 To LastRow
    If [SIZE=6][COLOR=Red][B].[/B][/COLOR][/SIZE]Cells(i, "C").Value = objectClass Then
      [SIZE=6][COLOR=Red][B].[/B][/COLOR][/SIZE]Cells(i, "C").EntireRow.Copy Destination:= _
                                    Sheets("SheetTmp").Range("A" & Rows.Count).End(xlUp). _
                                    Offset(1, 0)
      ctr = ctr + 1
    End If
  Next
  If ctr = 0 Then
    MsgBox "There was no match for " & objectClass, vbOKOnly, "Not Found"
  Else
    Sheets("SheetTmp").Columns("A:IV").AutoFit
    Sheets("SheetTmp").Select
  End If
[COLOR=Red]End With[/COLOR]
End Sub

 

[jcrusade]

Thank you so much, it worked perfectly! Very much appreciated I wish I would have posted sooner.

Thanks again!

Jennifer