copy and paste with v basic code

Ian, can you post an example for me. I'm having a hard time understanding your requirements from the description.

Regards,
Barrie

sorry about that. ok here's what i have to do. I have a file with certain invoice numbers. I also have a larger file with all of the invoices for the year. What i would like to do is have a code(possibly in a button from VB) that will take an invoice number from the small file, and search for it on the large file. Once it is found i would like to copy the row that the invoice number is on and possibly paste it on to a brand new worksheet. is this possible? thanks

Ian

Ian, this is very possible. Couple of questions first.

1. What is the name of the small file?
2. Will this small file be open when you are running your macro?
3. Where will the invoice number be located in the small file (sheet name and cell address)?
4. What is the name of the large file?
5. Will this large file be open when you are running your macro?
6. What is the range to be searched in the large file? Is it a named range?

Regards,
Barrie

Answers to questions (thanks for being persistent on this one, very much appreciated)

1. sorted.xls
2. yes it will be open
3. sheet name: sheet1 Cell Address: G2 to G?
4. Name of large file: sears cap.xls (space between sears and cap)
5. It will also be open
6. The range to be searched in is all of column d

If I understand your requirements correctly, this code should work for you (let me know).

Sub Extract_Data_From_Column_D()
' Macro written by Barrie Davidson
Dim LookupFile As String
Dim NewFile As String
Dim SearchValue

On Error Resume Next
LookupFile = ActiveWorkbook.Name
Workbooks.Add
Range("A1").Select
NewFile = ActiveWorkbook.Name
Windows(LookupFile).Activate
Range("G2").Select
Do Until Selection.Value = ""
SearchValue = Selection.Value
Windows("sears cap.xls").Activate
Columns("D:D").Select
Selection.Find(What:=SearchValue, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.EntireRow.Select
Selection.Copy
Windows(NewFile).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
Windows(LookupFile).Activate
Selection.Offset(1, 0).Select
Loop

End Sub

Regards,
Barrie

Ok here's what i did to your code:

Sub Extract_Data_From_Column_D()
' Macro written by Barrie Davidson

Dim SearchValue
Windows("Book1.xls").Activate
Range("G1").Select
Do Until Selection.Value = ""
SearchValue = Selection.Value
Sheets("Sheet2").Select
Columns("D:D").Select
Selection.Find(What:=SearchValue, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.EntireRow.Select
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
Sheets("Sheet1").Select
Selection.Offset(1, 0).Select
Loop

End Sub

This works. Theres only one more thing i would like you to help me with here. Is there a line of code you can enter which will allow me to look up some invoice numbers that are duplicates, what i mean is that this code takes the invoice number from the small file and looks for it in the bug file, but sometimes that invoice number appears in the big file more than once, when the program pastes the searched data to the third worksheet, it leaves out the duplicate invoice numbers (only finds the one and assumes that is all thats there). im sure its only one line of code that needs to be entered. also the code looks so diffeent because i had to modify my program. you are still getting the credit here though. thanks a lot!! hope i can get that last line of code

ian

Dim SearchValue

Ian, I think this will work for you. If not, just let me know.

Sub Extract_Data_From_Column_D()
' Macro written by Barrie Davidson

Dim SearchValue
Dim rowNumber As Integer
Dim activeAddress As String
'Windows("Book1.xls").Activate
'I've commented the above line because it appears that you don't need it.
'If that is the case delete these comment lines.
Range("G1").Select
Do Until Selection.Value = ""
SearchValue = Selection.Value
Sheets("Sheet2").Select
Columns("D:D").Select
Selection.Find(What:=SearchValue, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select
rowNumber = 0
Do While rowNumber < ActiveCell.Row
rowNumber = ActiveCell.Row
ActiveCell.EntireRow.Select
Selection.Copy
activeAddress = ActiveCell.Offset(0, 3).Address
Sheets("Sheet3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
Sheets("Sheet2").Select
Columns("D:D").Select
Range(activeAddress).Activate
Selection.Find(What:=SearchValue, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select
Loop
Sheets("Sheet1").Select
Selection.Offset(1, 0).Select
Loop
End Sub

Regards,
Barrie

I seem to be getting an overflow error here. it happens on rowNumber = ActiveCell.Row

What is the error message you are receiving?

it says :

Run-time error '6':
overflow

Okay, it's in the variable declaration line. Change
Dim rowNumber As Integer

to
Dim rowNumber As Long

This should solve your problem.

Barrie

Thanks a lot man! great stuff here. Program now works like a charm. Many thanks and keep up the good work!