Set Range Name

[gheyman]

I have code that I will admit someone helped me with by writting almost all of it

Its running down a row and when it equals Yes it copys a section (11 row) on another tabs
and pastes it below it in the next availale row. It does this perfectly. What I would like to do is when it hits a Yes on the tab titled "WBS" copy the value in the next cell over and then paste it in the new selection thats being copied down in the second cell over.

'Pull over WBS numbers that have BOE sheets
Dim wsDst As Worksheet
Dim wsYesNo As Worksheet
Dim rngDst As Range
Dim rngSrc As Range
Dim clYesNo As Range
Set wsDst = Worksheets("BOE Summary")
'
Set rngDst = wsDst.Range("A" & Rows.Count).End(xlUp).Offset(1)
'
Set rngSrc = wsDst.Range("12:22")
'
Set wsYesNo = Worksheets("WBS")
For Each clYesNo In wsYesNo.Range("B10:B200").Cells
If clYesNo.Value = "Yes" Then
rngSrc.Copy rngDst

HERES WHERE I WANT IT TO COPY ONE CELL OVER FROM WHEN VALUE="YES" AN DCOPY IT INTO COLUMN B OF THE rngDst. First ROW

Set rngDst = rngDst.Offset(11)
End If
Next clYesNo

 

[Simon Lloyd]

It may be that you want to change this

Set rngDst = wsDst.Range("A" & Rows.Count).End(xlUp).Offset(1)

for this

Set rngDst = wsDst.Range("A" & Rows.Count).End(xlUp).Offset(1,1)

 

[AlphaFrog]

Sub Macro2()
    'Pull over WBS numbers that have BOE sheets
    Dim wsDst As Worksheet
    Dim wsYesNo As Worksheet
    Dim rngDst As Range
    Dim rngSrc As Range
    Dim clYesNo As Range
    
    Set wsDst = Worksheets("BOE Summary")
    '
    Set rngDst = wsDst.Range("A" & Rows.Count).End(xlUp).Offset(1)
    '
    Set rngSrc = wsDst.Range("12:22")
    '
    Set wsYesNo = Worksheets("WBS")
    For Each clYesNo In wsYesNo.Range("B10:B200").Cells
    If clYesNo.Value = "Yes" Then
    rngSrc.Copy rngDst
    
    [COLOR="Green"]'HERES WHERE I WANT IT TO COPY ONE CELL OVER FROM WHEN VALUE="YES" AN DCOPY IT INTO COLUMN B OF THE rngDst. First ROW[/COLOR]
    [COLOR="Red"]clYesNo.Offset(, 1).Copy Destination:=rngDst(1).Offset(, 1)[/COLOR]
    
    Set rngDst = rngDst.Offset(11)
    End If
    Next clYesNo
End Sub