Exporting

I reset the name to AA_Test and i get a debug

"run time error 1004
cannot rename a sheet to the same name as another sheet....etc"

I do not have another AA_Test sheet any where on my pc or network...I have triple checked.

Please Login or Register  to view this content.

to try and explain my idea and question a little better....here it goes..

I have a workbook that is used to keep track of sales made and commssions to be paid or not paid based on a "y" for yes to pay or "n" for not to pay.

within this workbook, i now have an accounting department that has decieded a year after the fact that they can not read the information the way they want to any longer, there fore instead of rewirting the entire work book i just want to export specific data from it and only that data within a given area based on the "y" vs "n"

the data i want to export is the same for each spreadsheet with in the workbook as far as placement goes, cells A4:Q34 for January, but i only want to export cells A4:C34, F4:F34, and I4:Q34. To add to this i only want that data with those cells to be exported if there is a "y" in the row of cells F4:F34.

actually i dont care if there is "y" vs "n"....i just want to export the data from those cells requrdless of a "y" or "n".

changed the name, same result, and then i noticed why...i made the grand leap into thinking that the new sheet would be created outside of the workbook but it was creating them within the same workbook. I feel kinda dumb as well i should for not paying attention to my enviroment.

the other thing i noticed is that it is creating the entire spreadsheet and not the cell range of that sheet. this in turn is a nice feature when i need to create new sheets based off of a master sheet. something i will definitly hold onto for future use.

in the mean time i am still stuck on exporting just the cell range of A4:Q34..not the entire sheet.

durning lunch i wa trying to work it out in my head, so that my explanation would make more sense. food helps the brain work it turns out....

so this is what i have come up with for an explanation, i have 16 (+/-) sales people per year, each sales person has their own sheet in the workbook. each sheet is broken down veritically by month, starting with January, each month takes up 30 rows. January, for each person on each sheet starts on cell A4 and stretches all the way to cell Q34.

each sheet has column F, in January F4-F34, in which a "Y' or "N" is input, demming yes or no for commission payout.

information that i want exported in within cells A4:Q34, but i dont need all of the cells. I only need A4:C34 - F4:F34 - I4-Q34. Once the code is in place for one, it should be easliy modified for all cells and sheets.

With in the code i want to be able to create unique sheet names, so that when the export is done, "John Smith January 2010" (as an example) is created outside of the current workbook and only containing the cell ranges, and not the entire sheet.

this whole process is to help avoid having to rewrite an entire workbook, that is working perfectly just to get a snip of information in a different way.

All of the sheets are in the same workbook and all the employee names are in the same cell on each sheet (cell B2), where as cell A2 has the word Name: in it.
Name: John Smith

What i was thinking is that if the code has to be repoduced 12 times per employee that is fine, once for each month. Then all i have to do is set a call function to a button, click it and export the data to a new blank sheet, with just the specific cell data being transfered. To have that occur out side of the active workbook would be even better.

just tried the new code. it creates the blank page really well, but didnt transfer the cells....

the following attachment is my workbook cut way down, with the code and button in place. when you click the [export] button you will see that it creates a new blank sheet in the same workbook and none of the data transfers.

I see what you mean. the capital Y vs lowercase y, made all the difference. Thank you very much for pointing that out...I was not paying attention to that at all. I have a code set up that uses "y" in it and it never cared about case sensitivity before or at least i didnt notice it being case sensitive before.

now i need to figure out how to get that now-not-blank sheet to create outside of the workbook.

ok as wierd as this is I have combined the two codes to make a button that calls both subs. It creates the new sheet inside of the current workbook with the information i am looking for based on the "y" and "n" in column F, then it copies that page, err creates that page outside of the workbook. It works great, now i just need a way to do that without creating a copy inside the active workbook.

well that didnt work the way i was hoping.

so now i am back to square 4, still need a way to create the data outside of the workbook.

Dave,
the code you came up with works great, thank you. I thought my crazy idea of combining the other code with yours to create the data outside of the workbook would work and it did, until i add the delete idea. Any ideas on how to step the code so that it creates the new sheet outside of the workbook instead of inside the active workbook?

it is deleting the copy inside the new workbook...or trying to at least.

is there any way to do the exporting one line at a time and have it export to a specific sheet for formatting purposes, instead of cutting and pasting?

the original code i posted has the ability to post one active cell at a time, instead i would like to export one line at a time based on the F4 "y" vs "n". I would also like to know if it is possible to verify that an exported row, is not double exported to the same sheet.

what they would like is a January, February, etc sheet in the same workbook that just has the data exported when needed and only the "y" not the "n". so I figure it would be easier to export one line at a time and ensure that the data is not double exported. Hope that makes sense?!?
as an example i would just like to export (A4:C4, F4,I4:Q4) to a sheet named January, and have it fall into corresponding cells without the spaces in between them, so (A4:M4).

so for each sales person a new sheet will be created? Is there a way to combine sales people on the same January sheet ie to a specific formatted area as to avoid seperate January sheets?

I see what had happened after I asked my previous I, I had added/copied the John Smith sheet and renamed it and then tried to transfer the data.

I still would like to know about seperating the sales people into specific areas on the January sheet.

so John Smith would be in the first 15 blocks as an example designated by a header with his name, and then the next sales person entered would be in say rows 20-35 under the same type of formating.

So do I need to copy the code into each sheet? The reason I ask is because i created a test workbook created three sheets with in it, copied the code into each sheet and everytime i enter data in to any cell on the sheet(s) i get a complier error. F4 is blank at this point and still the compiler error shows up. Not sure why this is occuring.....

tried posting...keep running into a database error from the site, as soon as i can i will post.

tried to post again...but the site is still giving me a database error

wierd...the file unzipped is only 855KB

i know i am asking a dumbkid question, but is there a way to code this to a button? the reason for this is because in order for the data to transfer the F4 with the "y" has to be the last field input in order to export the data. Retraining an old habit out of someone is combersome, because the person filing the sheet out knows ahead of time on wether the "y" or "n" goes in place and is attempting to input that field before the rest of the data. therefore it is not transfering all the data, just the data prior to the "y". With a button or or if you have a better solition all the data on the row will be transfered based on the "y" reguardless of the order the data is input....

that works to perfection...I thank you very much for all of the amazing help.

is it ok to say i spoke to soon?
I was testing the macro in a larger scale and noticed something minor. When a new month is exported to March for example, the header in the formatting for the month on the actual sheet dispalys January. To double check this I checked June, August and December, all with the same results.