Find, Copy & Paste Between Workbooks

[scgamecock]

I am trying to find out if it is possible to create a macro in an excel workbook that will open another workbook saved on the computer, perform a find, copy specific data, and paste it in the opened/active workbook.

[Will Riley]

I am trying to find out if it is possible to.....

Sure it is possible.

[scgamecock]

Will,

Thank you for the reply.

I have created a code where I am able to open another workbook and pull data by a specific cell range, but how can you do it by using find?

[Will Riley]

Sorry,

How do you know you need to use Find ?

Your very sparse description of the problem and assumption of the solution required mean that the options to try to help you are severely limited.

Why not try explaining the issue in detail so that people with no idea what you are trying to do have a chance to help you. Remember that we cannot see your workbooks or data so you either have to describe your issue in alot more detail or attach a small example workbook showing what you are trying to achieve.

My experience is that there are generally at least 3 or 4 ways to achieve a solution using Excel so try not to assume what the solution is. Just explain the issue well and you'll probably get more than one potential solution.

Explain it poorly and you'll just get smart-arsed comments like mine and people will stick your question in the "looks like pulling teeth" box

Oh, and welcome to Ozgrid - we're a pretty friendly bunch really

[scgamecock]

I have software that runs data and then generates CSV files. Each CSV file contains certain data that I would like to have located in one worksheet in excel. So, I started to create a VBA in excel that will pull the information from the CSV files. Basically what I need is a way to open the CSV files, search for specific data in the columns, copy that data, and paste it in excel. The data however, may not be stored in the same cell in the CSV files each time they are generated.

I have a vba written that allows the user to select the CSV file, and once the user has selected which CSV file he/she wants to open it will copy data that are in the cells specified in the code. Like I said above, the data that needs to be pulled may not be in the same cell each time the CSV files are generated. So, I would like some way for the user to continue selecting the CSV files, and then have a search performed for certain data, copy that data, and paste within excel.

Let me know if any further information is needed. I know that I my explaination is more complex than the actual soltuion.

[Will Riley]

OK, thanks

There are hundreds of threads around showing examples uf suing Find method, e.g. if you have specified your source & destination workbooks as variables wb1 and wb2, this could work....

wb1.Sheets("Sheet1").Cells.Find(What:="FindMe", After:=ActiveCell, LookIn:=xlFormulas, _ 
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
MatchCase:=False, SearchFormat:=False).Copy 
wb2.Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats 

[scgamecock]

The following is the code I am using right now:

 
Dim basebook As Workbook 
Dim mybook As Workbook 
Dim mybook2 As Workbook 
Dim sourceRange As Range 
Dim destrange As Range 
Dim SourceRcount As Long 
Dim N As Long 
Dim rnum As Long 
Dim rnum2 As Long 
Dim rnum3 As Long 
Dim MyPath As String 
Dim SaveDriveDir As String 
Dim FileName1 As Variant 
Dim FileName2 As Variant 
Dim FileName3 As Variant 
 
SaveDriveDir = CurDir 
MyPath = "C:\Documents and Settings\cgravlee\Desktop\Test" 
ChDrive MyPath 
ChDir MyPath 
 
FileName1 = Application.GetOpenFilename(filefilter:="Excel Files (*.csv), *.csv", _ 
MultiSelect:=True) 
 
If IsArray(FileName1) Then 
    Application.ScreenUpdating = False 
    Set basebook = ThisWorkbook 
    rnum = 16 'This tells what row
     
    For N = LBound(FileName1) To UBound(FileName1) 
        Set mybook = Workbooks.Open(FileName1(N)) 
        Set sourceRange = mybook.Worksheets(1).Range("B13:B100") 
        SourceRcount = sourceRange.Rows.Count 
        Set destrange = basebook.Worksheets(1).Cells(rnum, "A") 
         
        rnum = 5 'This tells what row
        basebook.Worksheets(1).Cells(rnum, "A").Value = "Sample ID: " + mybook.Name 
         
        sourceRange.Copy destrange 
         
        mybook.Close False 
        rnum = rnum + SourceRcount 
    Next 
End If 

---

Auto Merged Post;

With the example I posted above, I am able to open the directory the CSV files are stored, and then select which file to use. If I use the macro that is record, I have to specify the file name. However, the filename is going to be different each time. Is there a way to mix the code I provide above a long with the code Will provide above?

[Dave Hawley]

The autolinks are your friends, in this case FIND

[scgamecock]

Thanks to Will, I believe I have the find, copy, and paste working. Below is the copy of new code:

Dim basebook As Workbook 
Dim mybook As Workbook 
Dim mybook2 As Workbook 
Dim sourceRange As Range 
Dim sourceRange2 As Range 
Dim destrange As Range 
Dim SourceRcount As Long 
Dim N As Long 
Dim rnum As Long 
Dim rnum2 As Long 
Dim rnum3 As Long 
Dim MyPath As String 
Dim SaveDriveDir As String 
Dim FileName1 As Variant 
Dim FileName2 As Variant 
Dim FileName3 As Variant 
 
SaveDriveDir = CurDir 
MyPath = "C:\Documents and Settings\cgravlee\Desktop\Test" 
ChDrive MyPath 
ChDir MyPath 
 
FileName1 = Application.GetOpenFilename(filefilter:="Excel Files (*.csv), *.csv", _ 
MultiSelect:=True) 
 
If IsArray(FileName1) Then 
    Application.ScreenUpdating = False 
    Set basebook = ThisWorkbook 
    rnum = 16 'This tells what row
     
    For N = LBound(FileName1) To UBound(FileName1) 
        Set mybook = Workbooks.Open(FileName1(N)) 
        Set sourceRange = mybook.Worksheets(1).Cells.Find(What:="4-Bromofluorobenzene", After:=ActiveCell, LookIn:= _ 
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ 
        xlNext, MatchCase:=False, SearchFormat:=False) 
        SourceRcount = sourceRange.Rows.Count 
        Set destrange = basebook.Worksheets(1).Cells(rnum, "A") 
         
        rnum = 5 'This tells what row
        basebook.Worksheets(1).Cells(rnum, "A").Value = "Sample ID: " + mybook.Name 
         
        sourceRange.Copy destrange 
         
        mybook.Close False 
        rnum = rnum + SourceRcount 
    Next 
End If 
 
If IsArray(FileName1) Then 
    Application.ScreenUpdating = False 
    Set basebook = ThisWorkbook 
    rnum = 17 'This tells what row
     
    For N = LBound(FileName1) To UBound(FileName1) 
        Set mybook = Workbooks.Open(FileName1(N)) 
        Set sourceRange = mybook.Worksheets(1).Cells.Find(What:="1,2-Dichloroethane-d4", After:=ActiveCell, LookIn:= _ 
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ 
        xlNext, MatchCase:=False, SearchFormat:=False) 
        SourceRcount = sourceRange.Rows.Count 
        Set destrange = basebook.Worksheets(1).Cells(rnum, "A") 
         
        sourceRange.Copy destrange 
         
        mybook.Close False 
        rnum = rnum + SourceRcount 
    Next 
End If 

Is there a way to perform the find, and not only copy the data specified in the find, but also copy certain data in the same row without specifing a cell or range?

For example:

I use the find function to search for "4-Bromofluorobenzene", so the row looks like the following:

4-Bromofluorobenzene ABC GHI XYZ

Once the find locates 4-Bromofluorobenzene, then I would also like to copy XYZ and move it to the active/current excel spreadsheet. The data XYZ will be different each time the software creates the CSV files.

If any further information is needed please let me know.