error message when Automating copy/paste worksheets

[curbster]

Hello all,
I have been working on this macro all day and no matter how many different ways I write it I can't seem to get it to finish correctly without giving me an error message. The error messages are different depending on how I write the macro, so what the message says is not important.

I have attached a sample of my work which contains the macro, but I'll post the code below as well. There are two tabs in the workbook. One contains the data("download") and the other is the template("Blank") per say. I need the macro to create a new worksheet using the template for each line of the data ("download")until it gets to the "finalrow." It names the worksheets the value of D3 currently, but I'd like to figure out how to name the worksheets by C3. c3 contains the names of customers, and is duplicated for each product they have purchased. I'd just like to attach a number after the name if it is a duplicate. i.e. Adam, Adam1, Adam2, Adam3 for all "Adam"s.

Look into the code and see if that makes sense. if not, feel free to ask questions. I appreciate the time you take to help me resolve this issue!

thanks

Please Login or Register  to view this content.

[D_Rennie]

im not sure though i think this may do what you want

Please Login or Register  to view this content.

you can do the sort
by adding a filter to the range

[curbster]

Hi, I still am receiving the same error once the macro has created about 60 tabs. the error is:

"Run-time error '1004':

The sheet you are copying has cells tha contain more than 255 characters. When you copy the entire sheet, only the first 255 characters in each cell are copied.

To copy all of the characters, copy the cells to a new sheet instead of copying the entire sheet."



D_Rennie, I appreciate your help, your code gives me the same result as my code. Any idea on this error message what to do?

Thanks!

P.S.~ I've attached a copy of the error message with this post.

[D_Rennie]

Hello Curbster.

try

Please Login or Register  to view this content.

sheets are based on customer name (C3). if sheets y exist it adds 1 to the num then 2......
Is the intent to have one page with only one line of variable data.
If so can i ask why it seams that you are ending up with lots of sheet doing things like this (192 for the sample data alone). I would end up getting lost with that many sheets.

anyhow
hope this helps/

[royUK]

Why would you want one sheet for each line?

[curbster]

D_Rennie,
Thanks again for the help, it is still returning the same error. I think I will need to change this procedure to save a seperate workBOOK instead of workSHEET.

A little background as to why I am creating these new sheets for each line: for security reasons I have deleted a template below the line that I copy into each sheet. It contains a page of information which plugs in the criteria into its respective line (i.e. customer name, account number, address, etc). That is one reason why I'm getting a different error message than I do with the sample excel file I posted to the forum. However, the error comes at the same time (after it has created about 58 sheets), it just is completely different error.

The reason why we wanted to have one sheet per customer is because we print them off and organize them with paper records we keep. We do not constantly flip through the tabs (I would get lost too if that were the case) but we did want to save them all together.

This sample has more lines than we normally have, but this one branch I'm working on has as many lines as the sample, which is why I'm running into the errors. Is excel limited on the number of worksheets it contains within one workbook?

If anyone could help me to save these into seperate workbooks instead of new tabs that would be great! The same rules would apply for duplicates, just by adding a number after a customer name if it has already been saved.

When saving into a new workbook, I will only need the contents of the "blank" tab as the workbook and not the data "download" tab. In other words, if the newly created workbook files could exlude the "download" tab that is preferred.

thanks again for previous and future help on this!

[royUK]

Do yo ant a sheet for each unique name in Column C?

[curbster]

The code as it stands is correct. So the criteria in the code would determine which lines I need new workbooks for. i.e.

Please Login or Register  to view this content.

Basically as long as both values in columns 11 and 12 are greater than zero, I'll need a new worksheet for that customer.

does that make sense? I hope I understood the question.

thanks!

[curbster]

So I have to change my procedures and felt it was a worthy of a new thread. After I was locked out of the newer (more clearly stated thread) I decided to repost here on my original thread.

Here is the followup of my situation: thanks in advance to anyone who can help, this is a duesy!


"Hi all,

I have a sample of code below and I need help figuring out how to create and save a new workbook for every line of data with certain criteria. I'll detail it below:

1. if data in cells (I row) and columns 11 and 12 are both negative numbers, skip row.
2. if data in those cells are positive numbers, copy entire row and paste into new workbook "Template" in cell A3.
3. save filename as the value of range("C3")
-if file exists with that name, add a number to the end (i.e. John, John1, John2)
4. repeat steps 1-4 until final row.
5. if data in cells (I row) and columns 11 and 12 are both negative numbers:
- if I.Row and column 27 (column "AA") is equal to ANY row, column 4 ("D") then:
- copy I.Row
- select "Template" workbook
- insert new row under "C3" and insert contents.
- repeat until final row.

Much of this is already in the code, but I've had to make changes because I kept running into an error that would not let me proceed. The code as it stands now copies the I.row into a new worksheet within the same workbook. I've had to change the process and now I need to create new workbooks instead of sheets. I still consider myself in the beginning stages of excel VBA program writing so please help where you can!"

here is what it looks like when I record it.

Please Login or Register  to view this content.

Thanks,
Curbster

[D_Rennie]

Hello Curbster.

I realy dont understand what you are doing. (Well i understand what the request is though i cant see why).

You say that the reason that you wanted the sheets to be created like this is that you want to print each sheet off to keep a paper record.
Now you want to create a new workbook for each record *Same thing lots of workbook 192*

Also are you wanting to create a new Sheet/Workbook for each customer again with the +1.

How did the code i provided last go????????
you can add a print in the loop to print the pages.
let me know what the code isnt doing and go from there.
Cheers Dave

if you just want what the last code provides but with saving the sheets as a new workbook. You can run this sub to save each sheet as a new workbook.
Copied From Here

Please Login or Register  to view this content.

change the xpathname to a diffrent path if you want
note this will save the two sheets "template" and "download" You may want to manually remove them from the folder.

Is excel limited on the number of worksheets it contains within one workbook?

In this case the amount of sheets that you can have in one workbook is only limited to the amount of Ram.
Actually for 2003 it could be 256, Though im thinking that 2003 is still allowed to max the ram.
though it seams that either way you are staying under the threashold.

knowing that this is sample data.
If the cell that is used for the sheet name contains more that 256 charters it will error as 256, Though i cant see that the cell should contain more than this.

[curbster]

Hello again Dave,

Thanks again for the time you are taking to help with this! I get that you don't understand why I need this in the way I do, the reason is that I have deleted sensitive information from the sheet. Basically the row that I copy and paste into a new workbook will fill in a spreadsheet below it.

You bring up a good point though as I will have to add this later. Basically the workbook has two sheets. the sheet named "blank" is the sheet that will recieve the pasted row. Underneath that newly pasted row is a series of rows that will pull from the pasted row. This does not need to be included in the macro because it can be done in excel as a template.

The entire sheet "blank" will need to be copied into a new workbook and saved as its own original workbook. I do not want the "download" sheet copied into the new workbook, I only want the "blank" sheet in the new workbook.

As the macro works, it will find that there are duplicate filenames and it will run into an error. If the duplicate file name "Adam" already exists, I'd like the new filename to be "Adam2." If "Adam" and "Adam2" exists, the next would be "Adam3"


To answer your question about the code you provided last, (which I thought I already answered, so I apologize if I had not) your code gave me the same error as I was getting with my code at the same place. It was however working better than my original code by naming the sheets as I needed in the paragraph above. I couldn't figure out how to manipulate the code to give me the proper results though (due to my limited VBA knowledge).

I think my previous macro must have been a problem due to the amount of memory in my computer then. It would always stop after about the 58th sheet it created.

I would like to "add a print command" to the code but first I need to see if the code works right. So if you wouldn't mind putting that in the code with single quotes so I can see it and activate it when I'm ready that would be AWESOME!

Ok I added the code above to my code and it gives me interesting results. It creates a new folder for each new workbook that it saves, and then in each folder it has created 3 seperate sheets. 1 sheet is titled correctly and is exactly the file that I need but there are two other sheets in the folder named "Sheets2" and "Sheets3" which I don't need. How do I get just the named workbooks to appear in the same folder?

Also, this extra code is leaving 192 open workbooks when it is finished and it took about 5 minutes to run the macro.

Is that enough/too much info? let me know.
Thanks!

Please Login or Register  to view this content.

[D_Rennie]

Ok do you want a new folder with "Linesheet Drop Folder" & dtimestamp " created for each workbook, That seams even more confising. We can easley get rig of any extra sheets.
We can just drop all the files into" Linesheet Drop Folder" if you like.

[curbster]

No I just want the sheets to save into the Linesheets drop folder as seperate workbooks. That would be great. I would love to eliminate any confusion I can!

Also, I don't need the time date stamp at all.
thanks Dave

[D_Rennie]

Hello ive redone the code, There is still a bug in there (it errors after a few sheets), I have to go out.

Please Login or Register  to view this content.

we will update to the correct folder once everythink is good.

[D_Rennie]

after steeping back for a min i see the problem may be in the fileexist loop

[D_Rennie]

I didnt carry through on the 1st if statement correctly,
also added a boolen

Please Login or Register  to view this content.

[curbster]

Dave,
thanks again for all the help! It looks like we're on the right track now! You mentioned we can save to the "right folder" once we get everything going right so you probably know that it is saving to my applications folder under Excel where everyone's "personal.xls" file is saved.

I had to change some code ("thisworkbook.name") to the actual file name I had open and I put that name in quotes.

i.e.
Workbooks("Linesheet Template revised_20091016_sample.xls").Worksheets("Download")

I figure I shouldn't have to do that but I could be wrong? Not sure. Once that was changed, the code ran fine. It was considerably slow but to be honest, as long as it runs right and gives me the desired results I'm not going to complain! So thank you for that.

I don't understand functions yet, so you lost me with that code. No worries, its only a matter of time. Strings still confuse me as well. Any pointers would be great or references I could research that would benefit my understanding would be welcomed!

Is there any way to add a little to bit to this code? If not, no worries, I can do this portion manually but I'm hoping that when we get bigger sets of data that I can avoid another grueling task. Here is what I would need:

If the (I) row font is Blue or contains a "P" in column 26, then in the same row, column 27 it will contain a number. I need to search all rows for this number and if this number exists (within column 27) then I need to copy the entire row (this row will have a font color red if that helps). In the "blank" worksheet I'll need to insert this row under the first (I) row that we put there. I need to insert all rows that match column 27 within the same worksheet (new workbook we've created).

A little history/explanation. P stands for parent. We are talking about loans in banking. So the parent loan is going to be the entire loan amount. Some portions of this loan are sold to other banks but we still keep that information on file. So if when a Parent loan is selected, we need to include all related loans with it. These loans which are sold to other banks will have the same "Parent" loan number attached to them within column 27.

I hope that makes sense.

I really appreciate all the help thus far!

[D_Rennie]

Yes i guess another loop or find could be used to see if any onther lines have the same number as the parent. Though say a line has been found that is related to the parent would that line still require its own workbook.

Workbooks("Linesheet Template revised_20091016_sample.xls").Worksheets("Download")

no thats fine normally the line should be fully qualified or

Please Login or Register  to view this content.

i take it you got the workbook going where you wanted. though this is what needed changing for this

Please Login or Register  to view this content.

Yeah the time it took to create all the workbooks, cant realy do much about that though try at the top of the code. Application.enableevents = false
and at the end application.enableevents = true. just out of intrest how long did it take to run.

[curbster]

Dave,
For each parent and its sold parts they (Parent and Sold) will have the same parent number in the parent column. By this column alone one can not decipher between them unless they can see color. The parent loans are always in blue font and the sold loans are always in red font. The column immediately to the left of the parent column labels the types of loans as P for parent and S for sold. So if we combine the two into saying that each parent will probably have some sold parts, we need to combine them onto the new workbook as one loan.

In short, the parent loan will always start in "A3" (on the "blank" tab before being copied into the new workbook) and any sold loans would insert new rows underneath the "A3" row. So if there were 2 sold loans, the sold loans would have their own rows; "A4" and "A5" respectively. The data would be pushed down as many rows as need be depending on how many participations there are. I have seen up to 10 participations but that is rare. Typically they have 2-4 but the code should work at least up to 10 or more. In the future this number could be greater.

I found out why the macro created a folder in the personal.xls application file, it was because I was running the macro from my personal.xls spreadsheet. my fault.

I tried the "enableevents" hint you gave at the end of your reply and it took the same amount of time. The macro went through 203 lines and took 3 min, 21 seconds. I didn't know how long it took so I did it again and timed it.

Thanks,

[D_Rennie]

contains a "P" in column 26, then in the same row, column 27 it will contain a number.

can you please upload a new sample file as the one you have in this thread i can only see the letter P in column 30, so im gussing the search is then based on column 31. i would rather use the letters and numbers over the color of the rows. as color can be dirrent to code based on how the color got there "CF" and allows for a greater range of human error.

we dont need a whole heap of rows fulled in just make sure that the data is in the correct place.

under 4 min to create a couple humdred workbook IMO isnt bad.

[D_Rennie]

I played with this, To test place a new worksheet in the uploaded workbook call it Test,
this deals with only the 1st line of data in the workbook, see if the results are as expected.

Please Login or Register  to view this content.

though i still think i need more accurate data.

[curbster]

Dave,
I am sending a new worksheet with correctly updated columns. Good eye! I hadn't noticed they had a different number of columns.

I am not clear why you wanted to add a "test" sheet but I did in this attachment I'm sending you. The "Blank" sheet has all the information in it that I need to use as a template so I am a little confused on the logic...

Also, this code will not allow me to test because it only does the first line (like you mention). In order to give an accurate test result, it would need to do at least 30 lines.

yeah I'm not complaining about this macro doing the work for me in under 4 min! Believe me I am ECSTATIC!

What does "IMO" stand for? Also, not sure what "CF" is.

Thanks

[D_Rennie]

i meant to open the workbook that you 1st uploaded and inster a page test to run the last code to see if it had the correct outcome of the 1st line of data.
anyhow ill put it all together and see what happens.

Im my opition, Conditional formatting.

[D_Rennie]

This should do it

Please Login or Register  to view this content.

You may have to adjust the part in the red as i put that in there becouse in the dataset there where letters P found in col 27 and no number in the col 28 so i couldnot match it with anythink.

And rember the output folder will need changing.

hope this helps.

[curbster]

Dave, take a look at the sample sheet when you run this code. I'll upload an updated one with notes in the "blank" tab. After the macro runs, open one of the spreadsheets that has sold parts and see where it is adding the additional lines. I'll attach an example with this reply.

Thanks again for your help, this is a dousy.

[D_Rennie]

How you going bud,

well i must say ive now has the crap confused out of me.

from what i understood if col 26 had a P col 27 had the unique number that would match any sold under the parent.

you cannot rem out this line

Please Login or Register  to view this content.

if you dont want to delete the range use

Please Login or Register  to view this content.

If i run the code exaclly how i posted it i end up with a workbook for every line that passes

Please Login or Register  to view this content.

and for every row that contains the parent in col 26 the a workbook for that customer will contain the parent and assocated sold.

i did notice that in the data set i looks as if the columns have been sorted with column 3 name and column 16 im guessing that this is the payoff date.
becouse the 1st payoff date falls before the parent loan payoff date. hence the 1st row of data may not be the parent load for that customer. so Adam3 may be the 1st parent loan since 1-2 are aready created.

try sorting by col 26 and col 3.

i take it that the two files you posted are the incorrect out comes of not deleting the cells for each time the loop is run. and not the desired outcome of what you want the code to do.

if this does not help put together a sample file with about 6 rows of data for 3 diffrent people 2 with a partent loan linked to a sold. and the others unique.
and create a worksheet for each line of data 6 sheets.

.

[curbster]

Dave,
Thanks again for your help, I hear you on being newly confused on this! It must be monday!

from what i understood if col 26 had a P col 27 had the unique number that would match any sold under the parent.

That is right.

you cannot rem out this line

what does "rem" mean?

ThisWorkbook.Sheets("Blank").Range("A3:AH1000").clearcontents

I'm confused why you believe I would want to delete this selection? I need A4 through G161 because that is the area where my worksheet autofills as soon as the row of data is added into the "blank" worksheet.

If i run the code exaclly how i posted it i end up with a workbook for every line that passes

Please Login or Register  to view this content.

This is right for each line. The real set of data will never contain negative numbers in these two columns for any sold parts.

i did notice that in the data set i looks as if the columns have been sorted with column 3 name and column 16 im guessing that this is the payoff date.
becouse the 1st payoff date falls before the parent loan payoff date. hence the 1st row of data may not be the parent load for that customer. so Adam3 may be the 1st parent loan since 1-2 are aready created.

try sorting by col 26 and col 3.

that works, we can sort by 26 and 3.

i take it that the two files you posted are the incorrect out comes of not deleting the cells for each time the loop is run. and not the desired outcome of what you want the code to do.

Yes, the two files I posted were the incorrect files. I guess it will help if I posted how they SHOULD look. I'll do that in this reply.

if this does not help put together a sample file with about 6 rows of data for 3 diffrent people 2 with a partent loan linked to a sold. and the others unique.
and create a worksheet for each line of data 6 sheets.

If I understand right, lets say there is one person with two parent loans and 4 sold parts for each of these 2 parents. In the data sheet ("Download") lets say this one person took up 10 rows when sorted correctly. This person would get only two workbooks because he/she has two parent loans. All we are concerned with are the parent loans but we have to subtract the sold parts (negative numbers as explained above) before we have our own accurate data.

I really hope that makes sense. its hard to explain without sharing our actual worksheet with the forum, which I believe would be considered "sensitive information" and not kosher.

Let me know if that doesn't make sense!

thanks again!

P.S.~ see the samplesheet01 attached to this reply. Also, as the macro goes through it will need to delete all the red rows before it goes to the next name. Nothing should appear in the grey'ed out area.

[D_Rennie]

ok

what you need is, You dont want a new workbook for each row that has a S in col26 (easley done when sorted in this manner.)

What i said about the clear(delete) of the rows on (blank) still stands becouse the template will need to be Reset before it can be used for a new line of data from the download. Else the end display of the data will be incorrect.

Is there information in below A4 in the original data. if not the clearing the cells shouldnot matter. Though if there is data in or below A4 )formulars whatever) the way i wrote the code it would not have placed the sold lines in the correct place in the 1st place.

maybe a new row needs to be inserted and only delete the rows that we insert to refresh the sheet.

that seams fairly simple though i dont want to go through and recode it only to find out that the method of codeing will not work becouse the uploaded data has not been accurate.

You can post the workbook to DRennie@Dodo.com.au. if you cant send to workbook i guess i can code to what i think needs to happen.

[D_Rennie]

try

Please Login or Register  to view this content.

[D_Rennie]

rember to sort the fields 1st

[curbster]

Dave,
again, thank you for all your help! I would not have been able to do this without your help! (especially since I still don't understand a portion of how its all tied together. :o)

I have made some minor adjustments and the code is running as desired now. However, I thought about how you said you need to know everything in advance in order to code properly. See explanation below the code as well.

Please Login or Register  to view this content.

There is one thing that this code doesn't do, I thought I would be able to just "add this part later" (famous last words of a procrastinater) but the way the code is written it will not recognize formulas when I insert/delete rows. In the "blank" worksheet, lines A72 - A74 would auto populate the sold loan # and in B72 - B74 the current balance. This is the formula I thought I could add there:

Please Login or Register  to view this content.

The only solution I can think of is to create an extra 10 blank rows within the "blank" tab right under C3. Then the macro, instead of running its course by deleting one by one, can sweep the 10 rows and go to the next "Download" sheet's row. This might even save a bit of time, but probably un noticable.

Thoughts? Will this work or do you have a better idea?

thanks!

[D_Rennie]

glad to hear that very think is ending up in tht correct workbook.
this will protect the sheet layout

Please Login or Register  to view this content.

the referencing formulars you could add the 10 odd rows and clear their contents.
Though it seams at 1st look that the formulars could be added in the correct loaction for each line of data at the time of inputing that data line.
This should allow for the amount of rows needed below A72 to expand with the new line count. this way you would not have to have 10 lines in this section as well.

also i think you are referencing the incorrect cells in

=IF($AA$4>=0,$C$4,"") it will automatically change to =IF($AA$5>=0,$C$5,"") if I insert a row.

Let me have a think about it for a while.

[curbster]

here is what I have with the new idea so far:

Please Login or Register  to view this content.

the only problem is that it does not do the sold parts correctly. I've left empty rows within the template rows 3 - 10 will be input areas (lines copied from "download" worksheet) and rows 80 - 87 will be the Loan # and Balance on the worksheet I sent you.

I'm leaving work now, looks like you're about 17 hours ahead of my time (USA, Arizona). I'll be back in about 14 hours from now.

thanks again Dave.

[D_Rennie]

yeah no probs i understand what needs to happen here though the only thing that worries me is only allowing 7 lines. something will be worked out.

Ill post the workbbok to your email.

[D_Rennie]

The original question has been solved as have the amendments.

Curbster/ Workbook in your email.

Please Login or Register  to view this content.

Thankyou

[broro183]

hi all,

Dave, good perseverance to provide the solution here

I've glanced at the full thread but haven't paid close attention to the details & thought I may add my 2 cents worth - for what it's worth...

I have modified your code from post " 35 slightly by using more With statements to group references to/actions on the same object and tried to prefix my changes with triple hash comments ("'###...").
This code may not be noticeably faster at all (I haven't tested it for speed or correctness) - but it may be if there are enough workbooks being created.

The next "big" speed increase that I can think of would be to pull the relevant ranges into memory (arrays), process them in memory & then perform the actions on the sheet. This is because each time information is drawn from the spreadsheet there is a cumulative performance impact.

Please Login or Register  to view this content.

hth
Rob