Copy Rows Code

[aftabn10]

hi, i have come across the following code that will copy data from Sheet L6, and then paste this within the Summary Sheet.

Please Login or Register  to view this content.

What i would like to find out is that if there is anyway when pasting the data within the Summary Sheet that this could be done via the "Insert Copied Cells" so that if required Cells can be Shifted Down.

I am guessing that the following part of the code would need to be amended:

Please Login or Register  to view this content.

If somebody could please help, would really appreciate it. Thanks in advance.

[JBeaucaire]

First suggestion, you're testing each cell over and over again, one at a time, for the same value from the Summary D6 cell. That's definitely going the long way.

Excel has an AutoFilter designed to do this in a single step for you. Have you ever used it? Based on your code, it appears you have titles in row1 of your sheet L6, which is required for an AutoFilter.

So to answer your question, yes, you can do an insert at the top of your current data. Where exactly on your Summary did you want to put it? I notice you're clearing cells on the Summary before you start...does that need to change, too?

The insertion itself can occur to copy and insert all the matching rows all at once, should be significantly faster.

[aftabn10]

Thanks for your response JBeaucaire. The value in D6 is a name that will change in accordance to the user, so this will not be the same value.

In regards to the AutoFilter, i am aware of how this works and I guess I could build a macro that would match the the name in D6 and then filter for that.

The problem I have is within the Summary Sheet I have another section of data from Row 14 onwards but if the name "Mickey" has 7 rows of data then this will overwrite the section I have on row 14, so i thought that once data has been copied then it might be a good idea to "Insert Copied Cells" so that the required Cells can be Shifted Down meaning the section of data on row 14 would now move down to row 17.

Hope that makes sense. i have attached a sample workbook, with the above example.

Thanks once again.

[JBeaucaire]

Your macro should go something like this:

Please Login or Register  to view this content.

I've also shown how to use a dynamic name range to list the manager names in D6. See attached...

[aftabn10]

JBeaucaire thanks for that, works brilliantly!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[aftabn10]

JBeaucaire will do that, thanks. Just had one question in regards to the separate section. Above the Section 2 Row their is a blank row highlighted in blank, when this is removed then whenever I change the name it overwrites that section, why is this?

Lastly, trying to understand the code you wrote, but the following part is something I am exist, just wondering if you could explain in brief what this does:

Please Login or Register  to view this content.

Thanks for all your help once again

[JBeaucaire]

I took advantage of your blank row since it allowed me to do so. The inclusion of the blank row lets us spot all the rows in the first section with a simple .CurrentRegion, which refers to "this group of adjacent cells". Removing the blank row inbetween causes both sections to become one big section.

We could deal with that, too, but why bother replacing one snippet of code with a bunch of other code when your original design makes it simple? So, don't remove that blank black row. You can squeeze its height down if you wish to make it smaller, almost non-visible.

==========
You can press F1 and read up on the SUBTOTAL function in detail. I used it here to count the number of rows because SUBTOTAL is one of those unique functions that can be told to "only do your functions on the visible rows". So if I put a true SUBTOTAL of a column adding all the values, it would change results in realtime as I filtered the data and rows went hidden.

The SUBTOTAL function I used is a "count of visible cells" so it tells me there are 5 rows I'm going to copy and puts that number into the variable Rws.

Then I use that variable to resize the number of rows to insert at row 9 every time.

The macro does this, basically:
1) Uses CurrentRegion to delete the entire existing first section.
2) Autofilters the 2nd sheet based on the name you entered
3) Counts how many rows are visible on 2nd sheet
4) Inserts that many rows back into sheet1
5) Copies the filtered results into the rows inserted
6) Turns off the filter on 2nd sheet.