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.

Sheets("Lead Worksheet").Select
    Rows("11:11").Select
    Selection.Insert Shift:=xlDown
    Range("B12").Select
    Sheets("Paste in this sheet").Select
    Range("L4").Select
    Selection.Copy
    Sheets("Lead Worksheet").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    Range("Q4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    Range("C12").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    ActiveWindow.LargeScroll ToRight:=-1
    Range("H4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    Range("E12").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    Range("N4:O4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    Range("F12").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    ActiveWindow.LargeScroll ToRight:=1
    Range("U4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    ActiveWindow.LargeScroll ToRight:=1
    Range("I12").Select
    ActiveSheet.Paste
    ActiveWindow.LargeScroll ToRight:=-1
    Rows("11:11").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    Range("B12").Select
    Sheets("Paste in this sheet").Select
    ActiveWindow.LargeScroll ToRight:=-1
    Range("L5").Select
    Selection.Copy
    Sheets("Lead Worksheet").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    Range("Q5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    Range("C12").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    ActiveWindow.LargeScroll ToRight:=-1
    Range("H5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    Range("E12").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    Range("N5:O5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    Range("F12").Select
    ActiveSheet.Paste
    Sheets("Paste in this sheet").Select
    ActiveWindow.LargeScroll ToRight:=1
    Range("U5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Lead Worksheet").Select
    ActiveWindow.LargeScroll ToRight:=1
    Range("I12").Select
    ActiveSheet.Paste
End Sub

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