Can't seem to get the automatic selection of data present down!

I need help.
I am trying to utilize a macro that will be able to take the data from one worksheet and paste selected cells into another that is formatted. The problem for me comes from not knowing enough about macros to put the proper code in that will help me be able to put different amounts of rows of data into my first worksheet and have the macro stop after it hits a cell with no data in it.
I recorded the macro as I want to see it and have tested it. I only utilized two rows worth of data to show you, but my hope is to be able to automate it so it knows when there is no more data to move. Any ideas? You have all been very helpful in the past and make me look like a star. Your assistance would be greatly appreciated.

Here is my code.

Please Login or Register  to view this content.

Hi,

Can you attach the workbook so we can see the requirement in context? It's always easier to find a solution when we can eyeball the workbook.

Rgds

Richard,

Thanks, I am attempting to attach the workbook for you. Any help you can give would be really appreciated.

Chris

Originally Posted by wantucce

Richard,

Thanks, I am attempting to attach the workbook for you. Any help you can give would be really appreciated.

Chris

Hi,

Which columns on the Lead worksheet translate to which columns on the Paste in this sheet. e.g. Col F; City --> Col N

There are 31 columns on the 'Paste In..' sheet but only 16 on the 'Lead' sheet, where does the extra data come from?

It seems that you are collecting Data on the 'Lead sheet' and then simply updating it on the Paste In sheet. Isn't this just duplication? Are users filling in just one line on the Lead sheet and then updating the 'Paste in' sheet?

What do you want the macro to do. Just copy data from one sheet to the other.

Rgds

I notice in your Directions to Import notes that there is obviously a degree of pre-processing of data from the 'Install Base' workbook. Wouldn't it be better to build this into any updating process?

First of all Richard, thank you for your continued efforts.

One thing that I forgot to tell you last time was that in the worksheet if you run macro1 it gives an example of what the macro is meant to do.

What I am trying to accomplish is this; my engineers will have spreadsheets of the same type of date but one may have 20 accounts that he needs and another may have only ten. The macro is intended to take the raw data, from the past in this sheet and transfer certain cells over to the Lead Worksheet, thus eliminating the need to perform these tasks manually. As they will need to do this on a regular basis, I have made the macro so that it inserts a row, copies the data over and then inserts another row. The engineer will then use the Lead Worksheet as a guide for customer calls, fill in the other information and then send on to me as proof of their effortss.

What I can't figure out is how to write the macro so that is stops the process when it runs out of data. That is what I am asking for help with. If it was as easy as having only ten customers each time, then I could use the macro recorder to accomplish this. However, as the amount of data varies, I need some help in telling the macro when to stop running.

In particular, the macro takes data from Paste in this sheet and puts it in the Lead Worksheet in the following order.
Column L into Site Name
Column Q into Contact Name and Phone Number
Column H into Equipment Description
Column N & O into City and State respectively
Column U inot Current Agreement End Date

Then it should repeat the steps until all the data is moved. The engineer can then add more date to the Paste in this sheet tab and run the macro again if he needs to.

Not sure if this helps, but if you should need to call me, if that is allowed, is there a way for me to get my number to you in a secure fashion? Again, thanks for any and all help with this.

Regards,

Chris

Hi Chris,

I think I've got the general idea. A few questions.

1. Is there any reason why all the rows and the relevant cells on the Paste sheet can't be copied to the Lead sheet in one go rather than adding one row at a time? I would have thought not but your recorded macro seems to be doing that. It would certainly be easier and quicker to update the whole block in one shot.

2. On the Lead sheet, is data ever deleted? i.e. Does the lead sheet just grow in size as new rows are added with earlier rows never being deleted? I guess so since you're adding new rows at the top each time but please confirm.

3. On the Paste sheet, when the engineer adds new data does he first erase the rows already there? If so would you want the rows to be automatically deleted once the relevant data had been copied over, or does the Paste sheet just grow and grow in size too? I guess it's deleted since otherwise every time you ran your macro it would keep adding the same data again.

4. On the Lead sheet it appears that the new data is always added at the top of the list, shifting everything down. Is this your preferred location, or do you want new data added at the bottom?

Other than that it's a fairly simple macro to write. If you answer the Qs. above I'll put something together for you.

Rgds

Richard,

I have answered below in bold italics.

Can you recemend a book or class to get a better understanding of this programming language? I am intrigued and find that once I see how it is done, the language makes sense, however without seeing it, the idea of the program is not very intuitive.

1. Is there any reason why all the rows and the relevant cells on the Paste sheet can't be copied to the Lead sheet in one go rather than adding one row at a time? I would have thought not but your recorded macro seems to be doing that. It would certainly be easier and quicker to update the whole block in one shot. I do not have an issue with that. I just thought it might be harder as the list is growing. Which will answer your question below>

2. On the Lead sheet, is data ever deleted? i.e. Does the lead sheet just grow in size as new rows are added with earlier rows never being deleted? I guess so since you're adding new rows at the top each time but please confirm. The intention is to have it continue to grow. As the manager, I may manually delete items that are no longer relevant, but I also want this as a record as to what my engineers are doing to try to increase revenue. They do get points for effort, not just success.

3. On the Paste sheet, when the engineer adds new data does he first erase the rows already there? If so would you want the rows to be automatically deleted once the relevant data had been copied over, or does the Paste sheet just grow and grow in size too? I guess it's deleted since otherwise every time you ran your macro it would keep adding the same data again. Yes he erases the previous data. I guess as a layman, I see the Paste sheet as the input and the Lead Worksheet as the out put. No need to see the input again.

4. On the Lead sheet it appears that the new data is always added at the top of the list, shifting everything down. Is this your preferred location, or do you want new data added at the bottom? I guess that it really does not make a difference. That is the way that I did it based on what I saw from another similar macro that someone developed and I use. The only difference on that macro is that the data size ( selected area) is always the same. I guess in hind sight it would make more sense to have the newer information at the bottom.

Other than that it's a fairly simple macro to write. If you answer the Qs. above I'll put something together for you. That would be fantastic.

Hi,

I'm reattaching the workbook with a new 'CopyData' macro. I've attached it to a button on the 'Paste in this sheet' sheet. Click the button to run it. After it's run it will delete the data on the 'Paste...' sheet.

I've added an additional sheet which contains the original data which I used when testing so that I could copy it back again. You can of course delete this.

There are several books about Excel VBA on the shelves. The ones I used and found most useful were:

Writing Excel Macros, by Steven Roman
http://oreilly.com/catalog/9781565925878/

Excel 2002 VBA - but it may have been updated since to 2007, although the basics won't have changed of course.
http://www.amazon.com/Excel-2002-VBA.../dp/0764543717

HTH

Regards

Richard

Richard,

I will take a look shortly and let you know. Thank you for your efforts.

Chris

Richard,

Things look really good overall but I did find one issue and have one other request.
The issue is that the data does not erase or clear itself as you stated it would. It is always there when I go back to the paste in this sheet tab. That is not a big deal and I do not need it fixed.
The request is that I was hoping to add one more column over to the "lead worksheet" tab and I added it in column J. It is called Current Coverage Type and the data that needs to go in here is from the paste in this sheet tab, column R. If this is possible, I would be most appreciative. I have added the column J in version 1.3 attached below.

Thank you again. Your assistance has been invaluable.

Hi,

See the attached. I've made the alteration.
On the first file I'd temporarily commented out the bit of code that cleared the data whilst I tested it, and forgot to reinstate it.

Rgds

Richard,

That is great, however, I think that you did not attach the document. Sorry to be such a pest.

rgds,

Chris

Oops, sorry Chris. Now attached.

Incidentally if you ever need to add any other columns, the operative bit of the code is the following two lines. They are two arrays 'arrPcol' and 'arrLcol'. You can see their are 7 elements in each array indicated by the index suffix in brackets. The values, 12, 17, 8 etc in the first array are the column numbers on the Paste Sheet that you want to copy (that's the meaning of the 'P' in the array name.

Similarly the other array arrLcol contains the same indexes and the values are the column numbers on the Lead Worksheet to where the values from the equivalent index in the first array should be pasted.

Please Login or Register  to view this content.

Rgds

Richard,

I was able to present my worksheet, with your macros, and it was a hit. Thank you very much for all your assistance. When things slow down, I will be looking at the macro a bit more to see if I can reverse engineer it and get a better idea of how you do it. I am intrigued.

Thank you again.

Rgds,

Chris

Chris,

Many thanks for the feedback, and I'm glad it was of some help.

Kind regards,

Richard