Macro to copy data from one sheet to another with >1 variable

ghhmd - Aug 19, 2015 at 12:30 PM
I have been using macros to copy data from one sheet to another without issue, until my data has become a little more complex. I have sheet 1 with around 600 lines of data for which some if it needs to be copied to sheets 1-15 (each is different). I have created 15 separate macros because I can't seem to get only one to work, which is fine - all of the heavy lifting is over. But, my new problem is that one sheet requires data from sheet 1 with multiple variables. i.e.: Epidurals - I need it to copy both thoracic and lumbar but cannot find a way to do this. The macro I am using is:

Sub epidurals()
Set i = Sheets("Sheet1")
Set e = Sheets("Epidural")
Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(i.Range("O" & j))

If i.Range("O" & j) = "Yes-Thoracic" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value

End If
j = j + 1

End Sub

This copies thoracic perfectly but there is data in column "O" that says "Yes-Lumbar" which are not being copied. I have tried inserting Or "Yes-Lumbar" which has not worked. I have also tried coping the macro again but get errors.

I am obviously a novice - any help would be appreciated.

Thanks, Gavin