Working with more than 1 workbook

[cowickjd]

I would like to be able to copy data from one workbook (one a user chooses) to another (the one the macro is ran from) I will not be copying all data so I would like to use Cells(r, c) to determine which ones to copy.

I currently have this... hopefully you can understand where I am trying to go with this.

'Description:   This macro will allow the user to select the file containing
'               the information that they want to append to the the raw data file.
'               Two new columns will be created for Subject and Timepoints and if
'               the type is unknown then the information from the logfile will be
'               inserted into the corresponding row on the raw data file.

'First allow the user to select the logfile that needs to be appended.

Dim wbName As String
Dim logFile As Workbook
Dim thisFile As Workbook
wbName = OpenOneFile()
'opening workbook
Set thisFile = Workbooks.Open(ThisWorkbook.FullName)
Set logFile = Workbooks.Open(wbName)
thisFile.Activate

'MsgBox v_logfile 'Just to see if the file selected is right.

'Find the header row in rawdata file by getting the number of columns in the row.
'First row with more than 5 columns will be header.
Dim v_lastRow As Integer
v_lastRow = Range("a65536").End(xlUp).Offset(1, 0).Select

' Get RowNum for header and column num of "Sample Name"
Columncheck = 7  'set  your number of columns to be exceeded, by whatever means means
NumRows = Range(Range("A1"), Range("A65535").End(xlUp)).Count
For MyRow = 1 To NumRows  'edit as required to suit your range
    myCol = Range("A" & MyRow).End(xlToRight).Column  'Get columns in row
    If myCol > Columncheck Then  'Check for first row > ColumnCheck
    
    'check if row contains text 'Name'
        Set Answer = Range("A" & MyRow).Resize(1, myCol).Find(What:="Name", LookAt:=xlWhole, LookIn:=xlValues)
        If Answer Is Nothing Then
            Set Answer = Range("A" & MyRow).Resize(1, myCol).Find(What:="Sample Name", LookAt:=xlWhole, LookIn:=xlValues)
        End If
        If Not Answer Is Nothing Then
        'if row does contain 'Name' then here are your row and column variables
            AnswerCol = Answer.Column
            
            Exit For
        End If
    End If
Next MyRow

'Append Subject and Timepoint columns to that row.
ThisWorkbook.Sheets(1).Cells(MyRow, myCol + 1).Value = "Subject"
ThisWorkbook.Sheets(1).Cells(MyRow, myCol + 2).Value = "Timepoint"
logFile.Sheets(1).Cells(1, 8).Value = "Test"
'Search each row after header row for the IIV or ISR types in the column we assigned
'as a variable.

For r = MyRow + 1 To NumRows
    cellValue = Cells(r, AnswerCol).Value
    If cellValue = "IIV" Then
    
    ElseIf cellValue = "ISR" Then
    
    End If

Next r

'When IIV or ISR is found append the corresponding data from the logfile to that row.

End Sub

Function OpenOneFile()
fileToOpen = Application _
    .GetOpenFilename("Excel Files, *.xl*")
If fileToOpen <> False Then
    OpenOneFile = fileToOpen
End If
End Function

 

[cowickjd]

I cleaned up the code some to hopefully help anyone understand it a little more.

Sub Subject_Timepoints()
'
'Description:   This macro will allow the user to select the file containing
'               the information that they want to append to the the raw data file.
'               Two new columns will be created for Subject and Timepoints and if
'               the type is unknown then the information from the logfile will be
'               inserted into the corresponding row on the raw data file.
'
'First allow the user to select the logfile that needs to be appended.

Dim wbName As String
Dim logFile As Workbook
Dim thisFile As Workbook
'wbName = OpenOneFile()
'opening workbook
'Set thisFile = Workbooks.Open(ThisWorkbook.FullName)
'Set logFile = Workbooks.Open(wbName)
'thisFile.Activate


' Get RowNum for header and column num of "Sample Name"
Columncheck = 7  'set  your number of columns to be exceeded, by whatever means means
NumRows = Range(Range("A1"), Range("A65535").End(xlUp)).Count
For MyRow = 1 To NumRows  'edit as required to suit your range
    myCol = Range("A" & MyRow).End(xlToRight).Column  'Get columns in row
    If myCol > Columncheck Then  'Check for first row > ColumnCheck
    
    'check if row contains text 'Name'
        Set Answer = Range("A" & MyRow).Resize(1, myCol).Find(What:="Name", LookAt:=xlWhole, LookIn:=xlValues)
        If Answer Is Nothing Then
            Set Answer = Range("A" & MyRow).Resize(1, myCol).Find(What:="Sample Name", LookAt:=xlWhole, LookIn:=xlValues)
        End If
        If Not Answer Is Nothing Then
        'if row does contain 'Name' then here are your row and column variables
            AnswerCol = Answer.Column
            MsgBox "Row = " & MyRow & "  Column = " & myCol & "  OR  " & AnswerCol & "  ???" 'Your row and column numbers - do with them as you will
            MsgBox Cells(MyRow, AnswerCol).Value
            Exit For
        End If
    End If
Next MyRow

'
'
'Append Subject and Timepoint columns to that row.

Cells(MyRow, myCol + 1).Value = "Subject"
Cells(MyRow, myCol + 2).Value = "Timepoint"
'logFile.Sheets("Sheet 1").Cells(1, 8).Value = "Test"
'Search each row after header row for the IIV or ISR types in the column we assigned
'as a variable.

For r = MyRow + 1 To NumRows
    cellValue = Cells(r, AnswerCol).Value
    If cellValue = "IIV" Then
    
    ElseIf cellValue = "ISR" Then
    
    End If

Next r

'When IIV or ISR is found append the corresponding data from the logfile to that row.

End Sub

 

[VoG]

So what is not working?

 

[cowickjd]

I don't know how to work with two different files at the same time. I want to search through each row in a specific column in the active workbook(the one running the macro) and if i find the values 'IIV' or 'ISR' in that cell get data from the workbook the user selected for the matching row on that sheet.

For example I search Cell(6, 4) and find IIV 2 in that column so I want to go to the other workbook and find the value IIV 2 and get the values of say Cell(3,5) and Cell(3,6) and add them to the appropriate row in the original sheet.