Announcement

Collapse
No announcement yet.

Find, Copy & Paste Between Workbooks

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    Re: Use Find, Copy, And Paste Between Different Workbooks

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

    LinkedIn: Will Riley

    Comment


    • #3
      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?

      Comment


      • #4
        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

        LinkedIn: Will Riley

        Comment


        • #5
          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.

          Comment


          • #6
            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....

            Code:
            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

            LinkedIn: Will Riley

            Comment


            • #7
              Re: Use Find, Copy, And Paste Between Different Workbooks

              The following is the code I am using right now:

              Code:
               
                  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, 03:21. Reason: Auto Merged Doublepost

              Comment


              • #8
                Re: Use Find, Copy, And Paste Between Different Workbooks

                The autolinks are your friends, in this case FIND

                Comment


                • #9


                  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:

                  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.

                  Comment

                  Working...
                  X