copy data from dynamic range to static range

**barkarlo** · 09-12-2009, 03:06 PM

Please Login or Register  to view this content.

This code copy workhours of drivers from the sheet vozaci2009 to the sheet2 and work excellent if the list of drivers on sheet vozaci2009 range A5:A50 is the equal with list of driver on the sheet2 range A5:A50. In my case
list of drivers on the sheet vozaci2009 is dynamic and list of drivers on the sheet2 is static. If some driver leave from list on sheet vozaci2009 code will misinserted workhours of drivers on the sheet2.
How can I solve this problem.
Thanks,

**JBeaucaire** · 09-12-2009, 04:28 PM

The problem would easiest for us to visualize if you posted your workbook. Make sure you demonstrate not only the data and where it's transferred to (if it's not VERY clear), but also use the sheet to demonstrate the problem you've tried to describe with drivers being added/deleted.

Use multiple additional sheets if necessary to show some BEFORE/AFTER so we can see the goal.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**barkarlo** · 09-13-2009, 09:06 AM

I send attach... where you can see two sheets vozaci2009 and sheet2.
on the sheet "vozaci2009" is pivot table from which I want copy paste
workhours of drivers to the sheet2.

**JBeaucaire** · 09-13-2009, 10:45 AM

Your formula approach is great. There's no reason to change it.

1) The formulas on Sheet2 are regular formulas and you had them entered as array formulas, no need to do that.

2) When the numbers appear the way you want, highlight the column of data and Copy it, then select EDIT > PASTE SPECIAL > Values...now the numbers are permanent.

**barkarlo** · 09-14-2009, 02:16 PM

formula is good, but I would like to know how apply code who
will give same result.
I must add new line in my code "copyone" who will first compare range of drivers and then copy workhours in sheet2.
I don't know how to do it.

**JBeaucaire** · 09-14-2009, 02:34 PM

You're saying the range B5:B50 needs to by dynamic, the B50 moves downward?

If so, this will change the copy range to a dynamic range:

Please Login or Register  to view this content.

Now that I've looked at your macro, truth is, you don't need that whole Select Case section, you can do it with one line of code:

Please Login or Register  to view this content.

**barkarlo** · 09-15-2009, 02:57 PM

That's it.
Thank you very much.

**barkarlo** · 09-16-2009, 02:26 PM

Yesterday I wrote that you code solve my problem but
today I see that isn't it.
Problem which I have isfollowing:
List of drivers on the sheet2 is same all month in the year
but list of drivers on the sheet "vozaci2009" is not same and
changes from month to month.
In case when list of drivers isn't same on both sheet code
wrong insert data.
How write else one line in this code who will solved
this problem.
e.g. on the sheet2 in range A5:A49 enter 44 name of drivers and
this list hasn't changes all year.
If list of drivers on the sheet "vozaci2009" range A5:A30 has 30 name of drivers
code not compare that two range and insert data is wrong.