Search, Copy Paste Macro

Could someone please help me ... I need a macro to search column Q on a sheet with an infinty number of rows... if column q contains that ID (8832341) copy that row to the next blank row on sheet 2 ... when I run this macro I would like for it to open a search box ... eventually I would like to add a button to trigger the macro ....


Thanks,
Allen

Can you attach a sample data. Thanks.

I dont have a macro at all.... I can post the excel file and show the data i want to copy if that would help?

Thanks ALOT ... you dont know how much eaiser that made my life... this will let me make a great advacne .... I see how it is done now I can modify and add to it ... I really appreciate it ... this worked exactly as I needed and done a great job! Thanks

So I am back at it again ... sorry for all the dumb question but I am new to this ... i want to add to that excellent example that was givin earlier... what I want to do is when it it search finds a copies the row... i want it to subtract one (-1) from the variable in column H... also I eventually plan to move all this macros and buttons to sheet three but I want it to continue searching sheet one... is this possible?

Originally Posted by MarvinP

Hey crazyAMP

Add this line just before the "End With" Line

.Cells(Last2Row + 1, "H") = .Cells(Last2Row + 1, "H") - 1

This wokrs great to subtract one to the info it copies... but if possible I would like for it to subtract from the source (where it copeies the data frokm table 1) instead if possible Thanks ALOT

Originally Posted by MarvinP

Hey crazyAMP,

Instead of a .Copy use a .Cut when you put the stuff into the clipboard. See if that is all it takes.

Your sort of lost me ... what exactly do u mean?

Originally Posted by crazyAMP

Your sort of lost me ... what exactly do u mean?

It wokred but only for the data it copied to sheet 2... i want it to subtract one from the original source Column H on sheet 1

Originally Posted by crazyAMP

It wokred but only for the data it copied to sheet 2... i want it to subtract one from the original source Column H on sheet 1

Also is it possioble to have the button on sheet 3 but the macro still search sheet one and copy to sheet 2 ? Thanks in advance and for all your help its greatly appreciated

Originally Posted by MarvinP

Hi,

I guess I need a sample file to see where you are going with this.

Awesome its on its way now

Here is my sample file this is very very trimmed down ... but I am sure you will get the idea

Originally Posted by MarvinP

Try this code

Sub CopyToSheet2()

Dim LookFor As Variant
Dim LastRow As Double
Dim RowCtr As Double
Dim WS2 As Worksheet
Dim Last2Row As Double

Set WS2 = ActiveWorkbook.Worksheets("Sheet2")
LookFor = InputBox("What are you looking for?")
LastRow = Cells(Rows.Count, "Q").End(xlUp).Row

    For RowCtr = 1 To LastRow
        If Cells(RowCtr, "Q").Text = LookFor Then
            Cells(RowCtr, "Q").EntireRow.Copy
            With WS2
                Last2Row = .Cells(Rows.Count, "Q").End(xlUp).Row
                .Cells(Last2Row + 1, "A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            End With
            Cells(RowCtr, "H") = Cells(RowCtr, "H") - 1 ' Subtract 1 from On-Hand Column
        End If
    Next RowCtr
    
Application.CutCopyMode = False
End Sub

That has the subtraction issue knocked out .. it works great .... is there a way i can run that macro on sheet three instead of sheet 1 and it do the exact same thing?

Originally Posted by MarvinP

Simply run the code while starting on Sheet3.

My signature line is "One Test is worth a Thousand Opinions". Try it and see what happens.
Learn how to set a breakpoint in the code and step through it line by line so you can understand what each line does.
http://www.cpearson.com/excel/Debug.htm

Im tryin to learn this and I have added a few things to what you have givin but some of it has been limites success lol ... one of them being getting the code to work from within sheet3 (cause my info is on sheet 1) im sure im over looking this ... I greatly appreciate all your help this is all so new to me but im trying to learn lol