Exporting

[jabryantiii]

right now this code exports to another sheet, and only exports the active cell. I want to export to a new spreadsheet with a new name, and a given area instead of just the active cell.

Please Login or Register  to view this content.

Is it possible to have the new spreadsheet name preset in the code? the space i am looking to export is A4:C34, F4:F34, I4:Q34. I would also like to know if only the data that gets exported, gets exported if a "y" vs "n" is in place in fields F4:F34?

[davegugg]

Its a bit hard to decypher what you are asking for, but to me it seems like you want to copy certain cells in a row if the value in column F of that row is y. If that is true, this should do it for you:

Please Login or Register  to view this content.

If not, could you try explaining it again in some other way?

[jabryantiii]

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.

[jabryantiii]

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".

[jabryantiii]

the end result is to have a new sheet, outside of this workbook, created for each month and each sales person, from the data with the given cell range.

[davegugg]

Hmm, I can't understand why that error would appear if you don't have a sheet with that name already in that workbook. Can you try changing the name in the code and running again?

[jabryantiii]

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.

[davegugg]

Ok, it is easy to change the code to add a new blank sheet instead of copying the whole sheet:

Please Login or Register  to view this content.

However, I think we need to modify that sinse you will want to run it for each employee in that particular workbook.
Are all the sheet in the workbook one of the employee sheets, or do you have any summary or other info sheets?
On what cell is the name of the employee for each sheet? On what cell is the month and year for that set of data?

[jabryantiii]

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.

[jabryantiii]

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

[jabryantiii]

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.

[pr4t3ek]

try this:

Please Login or Register  to view this content.

[jabryantiii]

that works for what exactly? The thing i am trying to accomplish would take data from a specific cell range put it into a new sheet seperate from the current workbook.

Currently the code i am trying to get to work is either creating a blank new sheet or copying the active sheet to another sheet with a new name. Both are great attempts but niether are hitting th mark yet.

I know how to transfer data from a single cell using a button, I am just hoping to get a specific range instead and into a new sheet outside of the active workbook.


I see what it does now...thanks. But I want to be able to run it off a button and only for specific cell ranges. That copies the entire page outside of the workbook...which is nice.

[pr4t3ek]

try this:

select a range in your workbook and it will copy that range to another workbook..

is this it?

Please Login or Register  to view this content.

[davegugg]

The reason my code was creating a blank sheet was because in your sample workbook, you have capital Y and N in column F. Your instructions earlier said uncapitalized y and n. If you change the "y" to "Y" in the code, it will tranfer the data.

[jabryantiii]

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.

[jabryantiii]

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.

[davegugg]

Simple, just delete the page inside the workbook after you have copied it out of the workbook.

Please Login or Register  to view this content.

To turn the warning off, use:

Please Login or Register  to view this content.

[jabryantiii]

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?

[davegugg]

What specifically was the probelm with deleting the copy made inside the workbook?

[jabryantiii]

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

[davegugg]

so before you run the delete code, go back to the original workbook:

Please Login or Register  to view this content.

[jabryantiii]

ok...I need a cleaner way for this to work.
I have an idea but i am not sure how to implement it.
this code creates the sheet in it own workbook but not the range i want or with the delimiter based on the "y" vs "n". (i have tried the active cell highlighting and still end up with a reprduction of the entire page)

Please Login or Register  to view this content.

this code creates the data i want in the same workbook and the range i am looking for based on the "y" vs "n"

Please Login or Register  to view this content.

So some how i need to combine the above code(s) into one to not only grab just the data i want based on the "y" vs "n" but i also need to to be created outside of the original workbook.

is it possible to set the created data to a specific cell on the new page, so that when i code the button to Feb, Mar, Apr, Jun....that they are all displayed on the top of the workbook?

[davegugg]

Please Login or Register  to view this content.

[jabryantiii]

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).

[JBeaucaire]

To have this happen in realtime, the macro can watch column F for "y" entries being entered, and the macro can instantly copy the item to the appropriate sheets.

This macro will create the January/February/etc sheets if they do not already exist, so you don't have to worry about that either.

Lastly, the macro will check to make sure the item it is about to transfer has not already been transferred. It does this by checking the value in column B, the Account/Job Name field. If it is found, a message will pop to indicate so.

1) Right-click on the sheet-tab John Smith and select VIEW CODE
2) Remove the code in there currently
3) Paste in this new sheet event macro

Please Login or Register  to view this content.

6) Close the editor and save your sheet
7) fill in some rows and add "y" or "Y" to column F, they will transfer on their own. You will hear a "beep" letting you know it happened.
8) You can delete the button

NOTE: This macro will also work regardless of the capitalization of the y/Y, both are fine.

[jabryantiii]

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?

[jabryantiii]

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.

[JBeaucaire]

I wouldn't hard code it that way. You're just asking for headaches tweaking the code.

Let's just add the name into the empty column A for each pasted row, then sort all the data. Doing this each time we add something will keep each person's items together without having to maintain separate "sections".

Please Login or Register  to view this content.

[jabryantiii]

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.....

[JBeaucaire]

Post up your workbook with the macro already installed.

BTW, if this is supposed to occur in every worksheet, we can restructure this into a single workbook-event macro in ThisWorkbook so you won't have to maintain a bunch of copies of the same macro.

[jabryantiii]

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

[JBeaucaire]

Try zipping the file first.

[jabryantiii]

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

[JBeaucaire]

Try zipping the file first. There's a 1mb max on file sizes, I think.

[jabryantiii]

wierd...the file unzipped is only 855KB

[jabryantiii]

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....

[JBeaucaire]

1) Don't merge cells. There's no reason to merge columns S:Z to make wide cells when simply widening column S does the same thing. Merging cells on sheets you want to run macros on just makes more unnecesssary work.

I unmerged them and widened column S.

2) The unused cells in column A have to be empty for this to work properly. Right now, there is a hidden character in cell A5 that is messing it up. I'm sure it was accidental, but be aware.

3) Here's a regular macro version, goes in a standard module:

Please Login or Register  to view this content.

4) Open the FORMS toolbar and add a form button, assign the macro "Transfer" to it. Use the button to initiate the transfers.

[jabryantiii]

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

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

[jabryantiii]

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.

[JBeaucaire]

Add this one line:

Please Login or Register  to view this content.