Need Help - Repeat Function with User Input

jillkfs

New Member
Joined
Sep 2, 2006
Messages
6
I have been reading this message board for the last 48 hours and you are quite a helpful group! I'm hoping you can help me too.

I am not a programmer, but I have programmed basic scripts and edits before. I usually have to see it done, then I can manipulate what I need. But, I can't find anything similiar in this case..

I was working on something on Friday and figured there HAS to be an easier way. I found the macro, then found a way to add in the user interface, but now I'm stuck.

This is the entire thing I need to do...

I have a worksheet of Employee data. I need to move specifc employees and their data from that worksheet (called 'Query') to a different worksheet (called 'Cliqbook') in the same file.

So, I am first clicking on "Find", then I'm typing in the Associate ID number, then closing the "Find" screen. Then I'm copying the entire row found, then "Copy", then going to the Cliqbook worksheet and clicking on "Paste". I know there is a better way, but I can't figure it out!

Can you help?? Thank you so much for anything you can do!!

:)
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

northwolves

Well-known Member
Joined
Jun 21, 2006
Messages
1,122
Welcome jillkfs to the board!


ADO+SQL or advanced filter maybe your best options,try ether of them.


Best Regards
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Are you actually removing the data from Query and putting it on Cliqbook, or are you just taking a copy?

How do you nominate the Employees being actioned? Do you have a list somewhere??

Is a code solution acceptable, or do you want it done by formulas?

Tony
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

Is there any specific criteria for targeting each employee?

If so, AutoFilter might be an approach.

Can you give a shot of your data?

Smitty
 

jillkfs

New Member
Joined
Sep 2, 2006
Messages
6

ADVERTISEMENT

Thank you All!

I have a separate paper-based document identify who I need on the "Cliqbook" sheet. This process will get them automated.

The employee list has over 10,000 individuals. I need to pull out 2,000 of them.

I am "copying", not cutting.

I don't think I care how it's done, I just need to do it. Thank you!

:)
 

jillkfs

New Member
Joined
Sep 2, 2006
Messages
6
I can't do a screen shot of data because it contains private information... Sorry! :)
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

Hi

Can you list the IDs of the individual on the spreadsheet somewhere? Perhaps a separate sheet in say column A?


Tony
 

jillkfs

New Member
Joined
Sep 2, 2006
Messages
6
Tony, Do you mean just type in the list of Associate IDs that I need? What would I do then?
 

jillkfs

New Member
Joined
Sep 2, 2006
Messages
6
This is what I currently have (keep in mind I don't know what I'm doing yet):

Sub GetInput()
Dim MyInput
MyInput = InputBox("Enter Associate ID")




Cells.Select
Range("A697").Activate
ActiveWindow.SmallScroll ToRight:=10
Selection.Find(What:=MyInput, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Rows("713:713").Select
Selection.Copy
Sheets("To Cliqbook").Select
Range("A75").Select
ActiveSheet.Paste
End Sub


As you can see it's selecting only one row (713) and then pasting to one row (75). Is there an easy way to fix this?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
If you are using Find Method then
Try
Code:
Sub GetInput()
Dim MyInput, r As Range, ff As string   
MyInput = InputBox("Enter Associate ID")
    Set r = Cells.Find(myInput,,xlFormulas,xlPart)
    If Not r Is Nothing Then
          ff = r.Address
          Do
                r.EntireRow.Copy Sheets("To Cligbook").Range("a" & Rows.Count).End(xlUp).Offset(1)
                Set r = Cells.FindNext(r)
          Loop Unitl ff = r.Address
     Else
          MsgBox "Not Found"
     End If
End Sub
 

Forum statistics

Threads
1,141,842
Messages
5,708,919
Members
421,598
Latest member
NewHere

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
Top