Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Find, Copy & Paste Between Workbooks

  1. #1
    Join Date
    21st August 2007
    Posts
    18

    Find, Copy & Paste Between Workbooks

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677

    Re: Use Find, Copy, And Paste Between Different Workbooks

    Quote Originally Posted by scgamecock
    I am trying to find out if it is possible to.....
    Sure it is possible.
    Kind Regards, Will Riley

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

  3. #3
    Join Date
    21st August 2007
    Posts
    18

    Re: Use Find, Copy, And Paste Between Different Workbooks

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677

    Re: Use Find, Copy, And Paste Between Different Workbooks

    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
    Kind Regards, Will Riley

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

  5. #5
    Join Date
    21st August 2007
    Posts
    18

    Re: Use Find, Copy, And Paste Between Different Workbooks

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677

    Re: Use Find, Copy, And Paste Between Different Workbooks

    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....

    VB:
    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 
    
    
    Kind Regards, Will Riley

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

  7. #7
    Join Date
    21st August 2007
    Posts
    18

    Re: Use Find, Copy, And Paste Between Different Workbooks

    The following is the code I am using right now:

    VB:
     
    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?
    Last edited by scgamecock; August 22nd, 2007 at 03:21. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,707

    Re: Use Find, Copy, And Paste Between Different Workbooks

    The autolinks are your friends, in this case FIND

  9. #9
    Join Date
    21st August 2007
    Posts
    18

    Re: Find, Copy & Paste Between Workbooks

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

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Copy And Paste Between Workbooks
    By asuevanj in forum EXCEL HELP
    Replies: 1
    Last Post: May 23rd, 2007, 08:49
  2. Macro - VBA Script - Find and then Find/Copy/Paste
    By JayMoore in forum EXCEL HELP
    Replies: 1
    Last Post: June 13th, 2006, 06:13
  3. Copy Paste in 38 WorkBooks
    By delig8 in forum EXCEL HELP
    Replies: 2
    Last Post: February 18th, 2006, 00:23
  4. copy & paste from different workbooks
    By Cleveland in forum EXCEL HELP
    Replies: 2
    Last Post: November 29th, 2004, 07:48

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno