macro to copy and paste data to new sheet

**mypassion1** · 12-04-2009, 03:32 PM

Could someone please help me with this?

I know how to do a subtotal and add page break. I would like to “paste data to new sheet at each end of break" and also, I would like to paste the date to a specific cell in that new sheet. After that I would like to (Save, Export or Virtual print) each sheet as a (JPG, PNG or GIF) because Potomac server does not support PDF. at the end of the process I should have 30 to 31 images as shown in the attachment. Calendar.xlsx

03022009.jpg.

Thanks in advance

**davesexcel** · 12-05-2009, 07:37 PM

Here is you sample back, click the button and the sheets will generate, I have been trying for hours to get each sheet range to save as a .gif but I have run out of time trying to get them to work in a loop, check out these two sites they are close to what you want.

The Workbook is saved as XL'03

http://www.andypope.info/vba/gex.htm
http://www.mvps.org/dmcritchie/excel/xl2gif.htm

**davesexcel** · 12-06-2009, 11:26 AM

Here is a new workbook, a solution to autofit the Chart is still needed.
You will need a Folder Named "Excel" and a Sub folder named "Calendar"
"C:\Excel\Calendar\"

Run the code, once the code is finished, check the above folder for the .Gif files.

**Andy Pope** · 12-07-2009, 05:27 AM

This will resize the pasted shape according to the longest dimension.

Please Login or Register  to view this content.

**mypassion1** · 12-07-2009, 12:43 PM

Thank you so much Dave and Andy for your time, but when check the folder the macro create 32 blank image with no data on them.
Could you please help me with the attach worksheet that way I can keep process simple? I just need step 1-4.

1. I need to create a macro to transfer the data by date to each sheet.
2. On each sheet I want the date 3/01/09 to be on a specific cell and that cell will be the file name when saving as GIF.
3. The data on column B and C can be paste to any cells on each sheet
4. Save or export each print area as a GIF file to a specific folder.

Please help me distribute the Raw_Data to the 31 sheets by date and save the print area on each sheet as a GIF file.

From there I can do everything else; insert the background image, text box and format each text box to get exactly what I want. That way I can format each sheet with out touching the macro. Also, some time I might need to change the print base on the size of the TV.

Calendar.xlsx

**davesexcel** · 12-07-2009, 10:16 PM

mypassion1,

You have changed the workbook, and the example I have supplied works for your original workbook, because it looks for the word "Max".

The problem is, you can't just export a range in a worksheet as a .Gif. without additional help.
Andy Pope's Addin works well, Dave McRichie's code works as well (links supplied in previous post), to selecting a range and saving that range as a .Gif, but I could not automate it to work in a worksheet loop.

You can though, export a chart as a .Gif, and that is what I have been working on. The example supplied, copies each worksheet range as a .Gif and places it into a chart, the chart is then exported as a .Gif to the folder I originally mentioned in the previous post. Andy's revised code makes sure that the "Worksheet" picture fits in the chart, so that there is no missing information outside of the chart before it gets exported.

Why the .Gif's come out as blank is probable a windows issue, you should exit Excel and then open the workbook, sometimes if you open a workbook with the same name as a previous open workbook, the previous version is what is opened, especially if you are downloading it from the internet.

Thanks Andy for the revised code.

**Andy Pope** · 12-08-2009, 05:13 AM

Another option for exporting images would be to check Stephen Bullen's PastePicture example.
http://www.oaltd.co.uk/Excel/Default.htm

**mypassion1** · 12-08-2009, 10:02 AM

Thanks the file work but I need another favor. if it is possible, could you please help me to add a background image and take out the word MAX in the file name after publish? for example, the file name is 12 01 2009 MAX could it just 12 01 2009.

.Interior.ColorIndex = image "C:\image\bg.jpg"

Thank you