Turning off screen updating deletes source data

First off, my disclaimer: I dont know anything about vba or macros. Ive written some of this from scratch, copied and pasted bits and pieces of functions Ive found online, and done some by recording input. So, Im sure there is a much better way to write this and Im sure its not as efficient as it could be, but before you ask why I did something the way I did it, my answer is "because I have no clue what Im doing and it seemed to work" lol.

What Ive pasted below works and does exactly what I want it to do as long as screen updating is on, but it takes a while and looks weird when the screen updates. As soon as I turn it off, I lose all my data and formating of the "AllActionsItems spreadsheet, and it becomes a blank sheet. It doesnt seem to matter where I put the appliaction.screenupdating=false/true in the macro.


Can someone tell me why turning off screen updating deletes the data of the sheet I am copying from? Ive googled and searched this site several times and cant find anything.

Thanks for any help you can provide!

I tried to include code without any screenupdating commands, but I got an error message saying that the text was too long, so I attached it as a txt file.

The thing is with the screen updating that if I dont turn it off, no data from the source sheet is removed, the macro works perfect. When I add in applications.screenupdating=false anywhere in the macro no matter where I turn it back on, I lose that source sheet data, it gets deleted. I will attach a workbook. I have to make a copy and scrub some data out. I'll post something tonight.

Ok here is a duplicate of the workbook I use with the same macro (which is also associated to the button at the top of the table on the All Action Items tab. All data is generic, but dates and status, etc are all the same format.

The macro functions exactly as I would like it too now. If you go into the macro and set screen updating off anywhere in the macro (doesnt seem to matter where you turn it back on either) the entire table and formatting is cleard from the All Action Items sheet, which is the source sheet. That sheet has some data filtered and remaining data copied to another spreadsheet, but no data should be modified in the All Action Items sheet. Again, this works fine as long as I dont put screenupdating off.



The file does save a new workbook to the same directory as this workbook with just the data created in the SDLActionItems sheet.

Any help you could provide on the screen updating issue would be great.

Originally Posted by TMShucks

OK, I'm struggling here. What data is missing?

I stepped through the code and it worked fine, if not very efficiently. That said, it was quick enough.

I then added Application.ScreenUpdating=False at the beginning of the code and then ran it unmonitored.

I ran it a third time having assigned the macro to the button. Again, no problem.

What cell is selected after you have run the code?

Have you tried pressing Ctrl-Home to get back to the top of the sheet?

Looks fine to me. Not sure what else to do with it ;-(

Regards

OMFG!!! I cannot believe how retarded I am. Countless hours spent trying to figure this out and I didnt notice that the damn scroll bar had moved to the right. Ive spent hours trying to figure this out when I should have been trying to figure out how to export to other formats as requested by my group. I want to go bang my head against something hard now.

Any idea how I get that sheet to stay so that cell A3 is visible without making that sheet active at the end? I want the new spreadsheet to be the active sheet once the macro runs.

Originally Posted by TMShucks

When I said the code wasn't very efficient, it was because of all the .Select ... Selection pairs.

You should avoid these ... and, if you do, the active cell won't move.

I'll try to spend a little time on it later.

Regards

I pieced it together through probably 20 different small modules so I could evaluate what worked and what didnt. I grabbed some lines of code from the web and took some from recording macros, it really is a mess. Im just so happy it works though.

I started another thread on a slightly different topic, but it involves the same macro. If you wouldnt mind, could you take a look. I just want to know if what I want to do is possible (within reason).

Thanks.

http://www.excelforum.com/2473624-post1.html

Originally Posted by TMShucks

The new requirement is possible but beyond my immediate capability. It would take me too long to work out how to do it and present a solution (in a reasonable timescale).

As an alternative, perhaps short term solution, why not save as a pdf file? You could do all the formatting in Excel and provide a small, tamper-proof, easily transported and viewed document.

How does the customer feel about it. In a recent job, my preference was to recieve Excel files because I could then manipulate them for my own analysis.

Regards

Actually, the customer is quite happy with the excel files that my macro currently produces. Its our own departments management, one of whom requires bullet points. The bulleted list is actually copied and pasted into our corporate gmail and sent to the manager. If I could figure out how to export to notepad or word or save as prn in a space or dash delimited form using columns C, F, G, and I for items in the open section and C, F, G, and J for items in the closed section that would be great. That way I could format all items in column F and G to be red, that would save a ton of time going down each entry and highlighting names at the end of the day. I could then open the file and past all items in the open section to the open items bullet point section in word and the same with closed items. Then I could copy and paste it all into our gmail application (the manager doesnt like attachments). Ive tried a few times to export some stuff to prn and no matter how I do it, it just looks horribly messy. Im sure there is a better way, but I havent figured it out.

The changes you made are great. Much easier to read this way. The only problem Im having is that it copies the entire workbook to a new workbook and not just the SDLActionItems sheet. I tried adding the section I highlighted in red, but it doesnt do anything. The old macro did just copy the single sheet, but Im not sure how to write it in the consolidating format youve used wit SDL as an object.

Please Login or Register  to view this content.

Originally Posted by TMShucks

This version has been updated to create a Word Document in the same folder as the original workbook.

A sample Word document is attached. It's probably going to be simplest to do all the formatting in Excel and then create the Word document.

Anyway, starter for ten.

Regards

Thanks. I actually went that route already and hit a dead end. Once word saw it as table / cell formatted, it was impossible to work with. The only way I could figure to get something into bullet points is to export it space delimited with the top section open items and bottom section closed. What havent been able to do is get the export in space delimited to allow some extra lines between the last open item and the closed item title.

I also kept getting spaces before the first exported row and had trouble specifying the range (Im still not quit clear on the whole concept of where the ranges stop and start.

Basically, I could take the worksheet that my macro produces (either the one created in the existing workbook or the new worbook) and have that exported as space delimited in the same order of open and closed items. That would be about as close to ideal as I could get I think. Id need to drop a few columns from the exported information and if at all possible (not sure it is) Id want to color the names red, but I think when its exported it looses all font and color formatting right?


BTW, I added the code tags. Thanks.

Originally Posted by TMShucks

The word document after the table has been converted to text.

Regards

Thanks so much for you help. Ive attached the template required in word format. The field names are the fields from the All Action Items tab. Ive colored the two fields in red that should be highlighted in red in word.

I cant run the macro for some reason. As soon as it gets down to wordtest it errors on wordApp.

The export that I need is far less complex than what you created. Even if I cant get it to actually put bullet points in, just having it in that format Im sure I can highlight them manually and make them bulleted.

Thanks.

Originally Posted by TMShucks

It shouldn't get anywhere near the Wordtest Macro. That's not working code, just some examples that I thought might be handy.

I think it says in the CreateSDLWordDoc macro that you need a reference to the Microsoft Word (12.0) Object Library. Look for that under Tools | References.

Regards

Ok, got it. I'll take a look. What did you use to get that example word output that wasnt in table form you had posted?

Originally Posted by TMShucks

I just used Convert to Text from the Table Ribbon.

I'm thinking that if the data is formatted in Excel and output to Word, if it IS in Table format it can be converted to text. I don't know how straightforward that will be yet ...

Regards

I cant find that function? Is that something I have to install? I see the table ribbon, but nothing is there about converting to text.

Originally Posted by TMShucks

No, nothing to install.

In the word document, click somewhere in the table. The ribbon will be headed "Table Tools".
Click on the layout tab and, next but one to the right hand end, you'll see "Convert to Text".

Select that and you'll see the options for conversion: paragraph, tab, comma or other (default to "-"). A space works.

Regards

Oh, I gotcha. I thought that you mean it was in excel. So, can I write the whole macro in excel and have it open a word file and execute the changes, and save the file? Are there any specific requirements for a macro that uses another application other than specifying that its an app and using a dim statement so excel knows you are referencing that app?

Originally Posted by TMShucks

This is nearly there. Some fine tweaking still required but as I'm going to be on a plane to Norway at 06:00 GMT, and I need to pack, etc., I probably won't have much time to play.

Hope this helps.

Regards

Thanks so much, I will take a look at it this weekend and try and work my way through it. Sorry for the delayed responses, I had to take my dog for this therapy dog test this morning, so weve been busy practicing.

I'll let you know how I make out with the macro. Thanks so much again, especially with your busy schedule.

I want you to know that Im not ignoring all your hard work. Ive had a few issues incorporating the word portion, but I havent had time to really sit down and make sure Im not missing any commented out lines in the existing Macro. Im hoping to make some time this week to go through it and see if I can make it work. If I cant make it work right, Im sure that its me and not you lol. I'll report back.

Thanks again.

Originally Posted by TMShucks

I think the attached workbook does everything ... except insert the bullets in the Word document.

This works end to end wth your test data. The main module extracts the data and creates the new workbook. A secondary module creates the Word document.

You should be able to copy the code in this workbook and COMPLETELY replace any previous code.

Regards

I will try this today, thanks so much! I'll let you know tonight how it works. Thanks again for all of your time!

Sorry I took so long to get back to you, work has been insane lately (I havent slept for 40 hours lol) and I havent had a chance to dedicate any time to this until now since I need to use it this week.

So the macro works perfect, except one new minor requirement was introduced today with a project change. I may need to have more than one action item sheets in each workbook. I tried using a wild card to call the All Action Items sheet "All Action Items*", but Ive had no luck. I know this is already a loop statement, but I need to figure out a way that the button will work on the spreadsheet regardless of what the name is and even if there are multiple sheets in the same workbook. The spreadsheet name can either have a format of Project Name Action Items or Action Items - Project Name or any thing similar to that. So its perfect in the format and I would still like separate files created for each spreadsheet. The only thing that needs to change is that it has some flexibility in the originating spreadsheet name.

Any ideas? Im not sure if I need another if statement in there i

something like:
if ws.name like "All Action Items*"
Then.....

Anyway, again Im really sorry to take so long to reply to you. I know you worked hard on this and I really appreciate it. I definitely was not ignoring your efforts, I just have literally not had time to breath the last few weeks.

Thanks again for any advice if your still following this thread.