Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a variable range of data from one worksheet to another.
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a variable range of data from one worksheet to another.
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a variable range of data from one worksheet to another
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" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a variable range of data from one worksheet to another
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable named range in worksheet function | Excel Worksheet Functions | |||
Copying A Range of Cells to Another Worksheet | Excel Discussion (Misc queries) | |||
universal copying over worksheet range | Excel Discussion (Misc queries) | |||
defining a variable-size worksheet area for copying & pasting | Excel Worksheet Functions | |||
Looking up a variable in one worksheet and copying information from another column to another worksheet?? | Excel Discussion (Misc queries) |