MrExcel Publishing
Your One Stop for Excel Tips & Solutions

copy and paste with v basic code


Posted by Ian M on August 27, 2001 8:11 AM

is there a code i can impliment in my program in excel that will allow me to search for a certain number in one worksheet, find that number on another worksheet, and then paste the information that is on the row that the searched number is located on in a new worksheet, assuming that there are already 4 worksheets available on the one file?


Posted by Barrie Davidson on August 27, 2001 11:22 AM

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

Regards,
Barrie

Posted by Ian M on August 27, 2001 11:39 AM

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

Posted by Barrie Davidson on August 27, 2001 11:45 AM

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

Posted by Ian on August 28, 2001 7:18 AM

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

Posted by Barrie Davidson on August 28, 2001 7:49 AM

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

Posted by Ian on August 30, 2001 12:58 PM

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

Posted by Barrie Davidson on August 30, 2001 1:47 PM

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

Posted by Ian on August 31, 2001 6:50 AM

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


Posted by Barrie Davidson on August 31, 2001 7:36 AM


What is the error message you are receiving?

Posted by Ian on August 31, 2001 7:59 AM

it says :

Run-time error '6':
overflow

Posted by Barrie Davidson on August 31, 2001 8:17 AM

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

to
Dim rowNumber As Long

This should solve your problem.

Barrie


Posted by Ian on August 31, 2001 8:51 AM

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