PDA

View Full Version : cut and paste a range to a new worksheet



cdub50
03-08-2007, 03:56 PM
I have this macro I created to look at column B(customer) and check if the selected value in cell B2 is equal to B3 if it is to selects the next cell below and keeps checking to see if the vaule is the same. If they are not the same the macro inserts a blank row and then sums the rows above for that customer and puts the total below look the attatchment


What I need is a macro that will take all of the information for each customer and cut and paste it into a new worksheet depending on the Total value. customers that have Totals that are 0-99 Gigs would have all rows including the total row cut and pasted in a new worksheet called 0-99. for totals that are 100-199 they would be cut and pasted into go into the 100-199 worksheet. I think a case statement would work but not sure how to select the rows. I attached a sample xls file with my macro so you can see what I have done so far. I hope I made sense and Thanks for any help on this I have been at this all day with no success

geekgirlau
03-08-2007, 10:14 PM
Hi cdub, and welcome to the Board!

I've created a slightly different macro for you in the attached example. It does work on a couple of assumptions:

There is a named range for the data
There is a named range for the data headings
The last column in the data is a SUMIF formula that calculates the customer total. This allows you to use an AutoFilter to find matching data.I've also added another level covering 200-299 - you can cut this out if you don't think it's necessary.

cdub50
03-09-2007, 12:57 PM
Thank you for the quick reply and the help. I ended up putting the data into SQL express and made my life much easier. The scope of what they wanted kept changing and I thought a DB would be easier so I just used a query to Select distinct customer from Vaults then sum the Original Size GB then I used the Having clause to seperate into groups much easier. Thank you for all the help I will definetly post any VBA questions to this forum.

Thanks Again,
:beerchug:
Chris W

mdmackillop
03-09-2007, 01:33 PM
Hi Chris
If this is solved, you can mark it so using the Thread Tools dropdown.
Regards
MD