Copying a variable range of data from one worksheet to another.

[AllyB]

I have a workbook with two worksheets. The first contains a listing of
activities and deliverables that comprise everything in a delivery process.
Not all are applicable to each process, so I use an indicator of "y/n" in
column C to indicate if that task will be completed. If column C is "Y", I
want to copy the contents of column D to the next worksheet. If it's "N", I
want to skip to the next row and check column C again. And so on, until the
end of my data range (which is set, so at least I know I only have to parse
rows 32 to 123).

Any help is much appreciated. If anything above is not clear I'll be happy
to clarify.

 

[GSnyder]

To solve this problem, I wrote some code. You can go into <Alt>-<F11>,
insert a module, and then paste this code into it.

I assumed that your two sheets were named "All Deliverables" where you keep
the entire list and the Y/N flag in column D and the deliverable name in
column C. I assumed the sheet you wanted to copy the "Y" deliverables to is
called "Deliverables to Complete". You can certainly change the names of the
sheets in the code to meet your needs. Further, I assumed that the column
header in the "Deliverables to Complete" sheet is in C1 and you want all of
the appropriate deliverables copied down from C2:C???.

This should work regardless of how many rows of deliverables you have (up to
5,000)

Sub CopyDeliverables()
Dim rngCopyFrom As Range
Dim rngCopyTo As Range

Sheets("Deliverables to Complete").Select
Set rngCopyTo = Range("C2")

Sheets("All Deliverables").Select

Range("C2").Select
For Each rngCopyFrom In Range("C2:C" & Range("C5000").End(xlUp).Row)
If UCase(rngCopyFrom.Offset(0, 1)) = "Y" Then
rngCopyTo = rngCopyFrom
Set rngCopyTo = rngCopyTo.Offset(1, 0)
End If
Next

End Sub


Let me know how it works!

 

[AllyB]

Thank you so much! This worked perfectly (okay, had to adjust for sheet
names, and locations but the main logic from you was perfect!)

You've saved me countless hours (not to mention countless aggravation!).

Alison

 

[GSnyder]

My pleasure! If you get a chance, could you click the Yes at the bottom of
the post? That will mark the problem as answered.

Thanks and have a great day!