Automating Data Population

[hitdave85]

Hello,

I am trying to populate some data by relying on a table I have named Area_Table. Here is what the table looks like:

<tbody>
[TD="class: xl65"]Unique ID[/TD]
[TD="class: xl66, width: 64"]Area 1[/TD]
[TD="class: xl66, width: 64"]Area 2[/TD]
[TD="class: xl66, width: 64"]Area 3[/TD]
[TD="class: xl66, width: 64"]Area 4[/TD]
[TD="class: xl66, width: 64"]Area 5[/TD]

[TD="class: xl66"]1001[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]N[/TD]
[TD="class: xl66"]N[/TD]
[TD="class: xl66"]N[/TD]
[TD="class: xl66"]Y[/TD]

[TD="class: xl66"]1002[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]N[/TD]
[TD="class: xl66"]N[/TD]
[TD="class: xl66"]N[/TD]
[TD="class: xl66"]N[/TD]

[TD="class: xl66"]1003[/TD]
[TD="class: xl66"]N[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]N[/TD]

[TD="class: xl66"]1004[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]N[/TD]

[TD="class: xl66"]1005[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]Y[/TD]
[TD="class: xl66"]N[/TD]

</tbody>

Here is what I am trying to populate by using the Area_Table:

[TABLE="width: 205"]
<tbody>[TR]
[TD]Unique ID[/TD]
[TD]Area[/TD]
[TD]Age[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Area 2[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Area 2[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Area 2[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Area 2[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Area 2[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Area 2[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, for each unique ID in the Area_Table, I want to populate ages 20-25 for all of the 'Y' areas. The 'N' areas are NOT populated. The hope is that a simple click of a button will populate the data for me. I assume since I am relying on a table, that the range will become dynamic, which will allow me to add/remove unique IDs pretty easily. I'm hoping a simple macro can address this problem since I have a lot of data to populate. Any ideas?

Thanks so much for the help!

 

[mumps]

Try this macro. Change the sheet names to suit your needs. The result will be in Sheet2.

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long, area As Range, lCol As Long, x As Long
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = 2 To LastRow
        For Each area In Sheets("Sheet1").Range(Sheets("Sheet1").Cells(x, 2), Sheets("Sheet1").Cells(x, lCol))
            If area = "Y" Then
                With Sheets("Sheet2")
                    .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(6) = Sheets("Sheet1").Cells(x, 1)
                    .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Resize(6) = Sheets("Sheet1").Cells(1, area.Column)
                    With .Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0)
                        .Value = 20
                        .AutoFill .Resize(6, 1), xlFillSeries
                    End With
                End With
            End If
        Next area
    Next x
    Application.ScreenUpdating = True
End Sub

 

[hitdave85]

Very cool! What if I want to populate that third column with the following ages (mix of integers and strings):

<colgroup><col style="mso-width-source:userset;mso-width-alt:3986;width:82pt" width="109"> </colgroup><tbody>
[TD="class: xl73, width: 109"]0-14[/TD]

[TD="class: xl73"]15[/TD]

[TD="class: xl73"]16[/TD]

[TD="class: xl73"]17[/TD]

[TD="class: xl73"]18[/TD]

[TD="class: xl73"]19[/TD]

[TD="class: xl73"]20[/TD]

[TD="class: xl73"]21[/TD]

[TD="class: xl73"]22[/TD]

[TD="class: xl73"]23[/TD]

[TD="class: xl73"]24[/TD]

[TD="class: xl73"]25[/TD]

[TD="class: xl73"]26[/TD]

[TD="class: xl73"]27[/TD]

[TD="class: xl73"]28[/TD]

[TD="class: xl73"]29[/TD]

[TD="class: xl73"]30[/TD]

[TD="class: xl73"]31[/TD]

[TD="class: xl73"]32[/TD]

[TD="class: xl73"]33[/TD]

[TD="class: xl73"]34[/TD]

[TD="class: xl73"]35[/TD]

[TD="class: xl73"]36[/TD]

[TD="class: xl73"]37[/TD]

[TD="class: xl73"]38[/TD]

[TD="class: xl73"]39[/TD]

[TD="class: xl73"]40[/TD]

[TD="class: xl73"]41[/TD]

[TD="class: xl73"]42[/TD]

[TD="class: xl73"]43[/TD]

[TD="class: xl73"]44[/TD]

[TD="class: xl73"]45[/TD]

[TD="class: xl73"]46[/TD]

[TD="class: xl73"]47[/TD]

[TD="class: xl73"]48[/TD]

[TD="class: xl73"]49[/TD]

[TD="class: xl73"]50[/TD]

[TD="class: xl73"]51[/TD]

[TD="class: xl73"]52[/TD]

[TD="class: xl73"]53[/TD]

[TD="class: xl73"]54[/TD]

[TD="class: xl73"]55[/TD]

[TD="class: xl73"]56[/TD]

[TD="class: xl73"]57[/TD]

[TD="class: xl73"]58[/TD]

[TD="class: xl73"]59[/TD]

[TD="class: xl73"]60[/TD]

[TD="class: xl73"]61[/TD]

[TD="class: xl73"]62[/TD]

[TD="class: xl73"]63[/TD]

[TD="class: xl73"]64+[/TD]

</tbody>

Thanks again for the help!!!

 

[mumps]

Could you post a sample of what the end result will look like?

 

[hitdave85]

[TABLE="width: 276"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD]0-14[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]53[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]57[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]58[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]59[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]61[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]62[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 1[/TD]
[TD]64 and over[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD]0-14[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]53[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]57[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]58[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]59[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]61[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]62[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Area 5[/TD]
[TD]64 and over[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD]0-14[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]53[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]57[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]58[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]59[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]61[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]62[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Area 1[/TD]
[TD]64 and over[/TD]
[/TR]
</tbody>[/TABLE]

 

[hitdave85]

I also want the results to be pasted starting in cell A15 on Sheet2 if that's possible. Thanks!

 

[mumps]

Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long, LastRow2 As Long, area As Range, lCol As Long, x As Long
    LastRow2 = 15
    lCol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = 2 To LastRow
        For Each area In Sheets("Sheet1").Range(Sheets("Sheet1").Cells(x, 2), Sheets("Sheet1").Cells(x, lCol))
            If area = "Y" Then
                With Sheets("Sheet2")
                    .Cells(LastRow2, 1).Resize(51) = Sheets("Sheet1").Cells(x, 1)
                    .Cells(LastRow2, 2).Resize(51) = Sheets("Sheet1").Cells(1, area.Column)
                    .Cells(LastRow2, 3) = "0-14"
                    With .Cells(LastRow2 + 1, 3)
                        .Value = 15
                        .AutoFill .Resize(49, 1), xlFillSeries
                    End With
                    .Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0) = "64 and over"
                    LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
                End With
            End If
        Next area
    Next x
    Application.ScreenUpdating = True
End Sub

 

[hitdave85]

Works perfectly! Thanks so much - very impressive.

 

[hitdave85]

I tried to adapt your code by using a named range, but nothing seems to happen when I run the following code. Any thoughts?

Sub Test()
Application.ScreenUpdating = False
Dim LastRow As Long, LastRow2 As Long, area As Range, lCol As Long, x As Long
LastRow2 = 15
lCol = Sheets("Inputs").Range("Plan_IDs").Columns.Count
LastRow = Sheets("Inputs").Range("Plan_IDs").Rows.Count
For x = 2 To LastRow
For Each area In Sheets("Inputs").Range("Plan_IDs").Range(Sheets("Inputs").Range("Plan_IDs").Cells(x, 15), Sheets("Inputs").Range("Plan_IDs").Cells(x, lCol))
If area = "Y" Then
With Worksheets("Rate Development")
.Cells(LastRow2, 1).Resize(51) = Sheets("Inputs").Range("Plan_IDs").Cells(x, 1)
.Cells(LastRow2, 2).Resize(51) = Sheets("Inputs").Range("Plan_IDs").Cells(1, area.Column)
.Cells(LastRow2, 3) = "0-14"
With .Cells(LastRow2 + 1, 3)
.Value = 15
.AutoFill .Resize(49, 1), xlFillSeries
End With
.Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0) = "64 and over"
LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
End If
Next area
Next x
Application.ScreenUpdating = True
End Sub

 

[mumps]

The macro loops through each row of your data so a named range won't work because you would need a different named range for each row. A named range would be OK if you were looping through only one range but in your case you would need a different named range for each Unique ID.