Need macro to copy data from sheet 1 to multiple pages on sheet 2 in correct cells

I have attached a 97-2003 .xls file with data for multiple store locations on sheet 1, and the desired result on sheet 2. I am actually using excel 2007, but I dont think I need any special features that it provides.


I will try to explain the issue here without opening the attachment.

Here is an example of the Data on Sheet1

Please Login or Register  to view this content.

Here is the desired result on Sheet2, with each store being on a separate page. I need borders around cells with values, but I can always do that with conditional formatting. I also need the data to paste to sheet2 with the number formats from sheet1. Some cells have dates and some part #s will start with a zero. The empty cells between stores can be eliminated if necessary (row 6, 13, 20 in this sample). Numbers of Parts will vary each week, and there will be up to 100 stores with up to 20 part #s per store. In my workbook that sorts the data columns to create sheet1, I allow for data from row 2-2000, but allowing for infinite rows would be great too.

Please Login or Register  to view this content.

Thank you for any help you can provide
-Dan

Originally Posted by rylo

Hi

See how this goes. It doesn't do any of the formatting, but it should move your data to the right places.

rylo

I ran this and it overwrote Sheet 1. Everything was in the right place, but because Column D was originally formatted for dates, the UPC numbers showed up as #####################.

Originally Posted by StephenR

I've skimped on the borders too, but not difficult to do, just a bit fiddly.

This is the solution I was looking for. Thank you very much. Ive marked the post as solved.

-Dan

Edit: Actually Sheet 2 wasn't split up into different pages like I wanted, but Ill try to figure that out on my own, and if I cant, Ill create a new post specifically for that issue.

Thanks again.

Originally Posted by StephenR

This will put each store on a new sheet.

I actually want them all on the same Sheet, but when I print them, I want each one to print on a different page. The purpose being that my customer is giving me 1 big PO which will ship to multiple stores. This format is their required format for the packing list, and I need 1 packing list for each store.

Its late now, so I wont have a chance to try these out till tomorrow, but thanks for all the hard work!

-Dan

Originally Posted by StephenR

This will put each store on a new sheet.

When I run this some of them work, then I get an error message that says 400.

Originally Posted by iturnrocks

When I run this some of them work, then I get an error message that says 400.

I just noticed a problem with my data that may be causing the problem. I will let you know when I figure it out.

Originally Posted by StephenR

This will put each store on a new sheet.

Ok, I got my data straightened out.

Your code works like you intended with the sample data I provided.

For some reason, I get the 400 error when I use my actual data and I cant figure out why. The formatting is identical, the only difference is the actual values. Since I dont actually want the results on different sheets, I am abandoning trying to figure out why it doesn't work for me.

Thank you,

Dan

Originally Posted by Leith Ross

Hello iturnrocks,

This will format data with the borders added. I added a button on "Sheet2" to run the macro.

Sincerely,
Leith Ross

Your code provides the desired result except for the multiple page thing. Until I figure out how to make each set print on a separate page automatically, I can just manually set the page breaks.

Thank you,

Dan

Originally Posted by Leith Ross

Hello iturnrocks,

I missed that piece about the pages. I have modified the macro to add a page break after each PO. There is a system limit of 1026 page breaks per sheet. I didn't include any error checking for this, but it can be added if needed. The macro has been added to theattached workbook.

Sincerely,
Leith Ross

That works great! Thank you very much. There wont ever be more than 200 pages so that wont be an issue. This will save me a lot of time in the future. This issue is truly solved now.

Thanks again,

Dan