Excel VBA

[computerman29651]

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 the data, and paste it in the opened/active workbook.

I have been banging my head on the wall for several days trying to figure this out, and I am no closer. I am just hoping someone could give me some kind of guidance, or let me know if I am wasting my time.

[HanneSThEGreaT]

Have you tried Recording a Macreo, then do what you want, then stopping the macro.
By doing that, it should give you the VBA behind what you want to do.

[computerman29651]

Yes, I have tried using the recorder. However, the recorder records the specific cell ranges you copy. So, each time you use that macro, it is going to copy the same cell ranges.

I was hoping to find a way to search for a particular criteria, copy, and paste in the active workbook without using a specific cell range because it may not be in the same cell in every workbook.

[lbargers]

Computerman - This is possible through VBA, if you want to send me a copy of your file, i can get you started in the right direction.

Several questions though, how do you want to open the addional workbook? Would you like it opened from a form or from a static path in vba?

Would you like a user to enter the find criteria in a text box? If so when a cell matching that criteria is found, the column/row data can we stored in variables and passed back into the active workbook.

[email protected]

[computerman29651]

Here is what I have in my code now:

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:="Customer ID", 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:="Product ID", 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

I believe I have the find, copy, and paste working, unless someone can simplify what I have. However, what I would like to do now is determine a way to not only copy the data spefified in the find, bu also copy some other data that is inthe same row without having to specify a cell or range.

Can this be done? If so, how?