Okay sorry for not being clear before.
Here is what I have now and it works!
Code:
Sub CopyAndPaste()
Dim myfile, wdApp As New Word.Application, wDoc As Word.Document
'select truck report file
ChDrive "E:\"
ChDir "E:\WG\TVAL\"
myfile = Application.GetOpenFilename(, , "Browse for Document")
Dim i As Integer
'searches for row with "avg" then selects column E(avg of temperature mean) of that row.
i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
'makes the file appear
wdApp.Visible = True
Set wDoc = wdApp.Documents.Open(myfile)
With wDoc
Dim oRow As Row
For Each oRow In wDoc.Tables(8).Rows
If Len(oRow.Range) = 16 Then
With oRow
.Cells(1).Range.Text = "Performance Review"
.Cells(2).Range.Text = ""
.Cells(3).Range.Text = Range("e" & i)
.Cells(4).Range.Text = ""
.Cells(5).Range.Text = ""
End With
Exit For
End If
Next
End With
wDoc.Save
End Sub
I will create 2 more identical macros to this so that I have a macro for each Cells(2),(3),(4).
All that is left that I would like to do is to change the background color if there is text in a row. I was thinking something like
Code:
If Len(oRow.Range) >= 16 Then
With oRow
Shading.Texture = wdTextureNone
Change.Shading.ForegroundPatternColor = wdColorAutomatic
Change.Shading.BackgroundPatternColor = -603914241
End If
End With
I got the code from recording a macro in word and changing the background color to white. I am not sure if this is compatible with the rest of my code however, because I keep getting "Block end if without if" even though there is an if.
All together it looks like this
Code:
Sub CopyAndPaste()
Dim myfile, wdApp As New Word.Application, wDoc As Word.Document
'select truck report file
ChDrive "E:\"
ChDir "E:\WG\TVAL\"
myfile = Application.GetOpenFilename(, , "Browse for Document")
Dim i As Integer
'searches for row with "avg" then selects column E(avg of temperature mean) of that row.
i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
Range("E" & i).Select
Selection.Copy
'makes the file appear
wdApp.Visible = True
Set wDoc = wdApp.Documents.Open(myfile)
With wDoc
Dim oRow As Row
For Each oRow In wDoc.Tables(8).Rows
If Len(oRow.Range) = 16 Then
With oRow
.Cells(1).Range.Text = "Performance Review"
.Cells(2).Range.Text = Range("e" & i)
.Cells(3).Range.Text = ""
.Cells(4).Range.Text = ""
.Cells(5).Range.Text = ""
End With
End If
Next
If Len(oRow.Range) >= 16 Then
With oRow
Shading.Texture = wdTextureNone
Change.Shading.ForegroundPatternColor = wdColorAutomatic
Change.Shading.BackgroundPatternColor = -603914241
End If
End With
wDoc.Save
End Sub
Again, all I need to do now is to change the background color to white and then I am set!