Loop command until blank row

[Welshcontingent]

Hi All,
Tried my best to get the code myself but nearly smashed the computer , then been searching for hours for the answer and still no luck, so last resort, to what I bet is an easy solution.

I'm building a spreadsheet and want to create a macro that will copy a row to another spreadsheet, and continue repeating this until it reaches a blank row and then stops the macro. For example:-

Row 1 contains Anna
Row 2 contains Ben
Row 3 contains Clive
Row 4 contains nothing

I want excel to copy rows 1, 2 and 3 to another workbook but once it reaches row 4 it realises it's reached the end of the list. (The list won't always be three rows but will be continous)

Is anyone able to help with this, as it's starting to drive me nuts

Thanks in advance

Welsh Contingent

[mudraker]

I prefer to use For Type Loops than Do type loops

The following code does not copy cell formating ont the values.

Change worksheet names as required

Please Login or Register  to view this content.

You said you wanted to copy from spreadsheet to spreadsheet whaich is what I have coded. If you meant from workbook to another workbook then change the code in red to include the workbook name

example

Please Login or Register  to view this content.

[Welshcontingent]

Hi Mudraker,
Thank you so much for the reply and it is doing just about what I wanted. And yes I did mean workbooks....see how crazed I was going ............ but that's neither here nor there.

With the below code when going to the sheet I want to copy to it copies it to row 1. I quickly tried a few different quick options to get it to copy the contents to....lets say.... row 9 wihtout much luck. Do I need to insert a "select row"? And I'm guessing it would be in the

"ws2.Rows(iToRow).Value = wS1.Rows(iCopyRow).Value"

Also, if I wanted it to first insert a row (and so move previously information down a row) would I be able to using this code?

Sorry should have mentioned those bits in my first mail.

Once again thanks for the help already

Cheers

The Welsh Contingent

[Carim]

Hi,

The code Mudraker has proposed is working fine ...
Do you have additional requirements, in addition to copying data, ...your comments are not very clear ...

[Welshcontingent]

Hi,

The code Mudraker has proposed is working fine ...
Do you have additional requirements, in addition to copying data, ...your comments are not very clear ...

Hi Carim,
I'm not saying the code doesn't work, as it very much does but I need it to do more, and my apologies for not being clear on this........ it sounds good in my mind, but unable to type it correctly

The code proposed copies the data from "worksheet 1" row 1 to "Worksheet 2" Row 1. As my Worksheet 2 has headings and fancy bits on there (which are required) I need the data copied to start at row 9.

Just to make it more confusing. "Worksheet 2" has other data on it which I need kept on there, and so I was hoping/ praying that before pasting the copied data from "Worksheet 1" Excel would insert a blank row (or even insert the data to row 9, such as what happens when you "Cut" a row and "insert cut rows") instead of overwriting contents in the rows.

Does this make it any more clearer??

Hmmmm.....OK for example:-

Worksheet 2 already contains data relating to

Dianne
Edward
Frances

Worksheet 1 has finally been completed (by useless colleagues) with data on

Anna
Ben
Clive

I now need to copy data on Anna, Ben and Clive from "Worksheet 1" to "Worksheet 2" but also keep my fancy headings and data previously copied about Dianne, Edward and Frances



Hope this helps to explain what I'm after.

Cheers

The Welsh Contingent

[Carim]

Hi,

If I understand correctly ...just insert the following instruction, after the variables declaration ...

Please Login or Register  to view this content.

[Welshcontingent]

Hi,

If I understand correctly ...just insert the following instruction, after the variables declaration ...

Please Login or Register  to view this content.

Hi Carim,
OK how stupid am I??

Just knew it would be something simple.

Now that I have that bit working, my next problem (and I do think this will be the last) is that when copying over to Worksheet 2 and data already on that sheet is overwritten.

What I need Excel to do is either move already saved data down a row each time before pasting every row, or insert the rows (as done with the manual Edit, cut, edit, Insert Cut rows). I tried the simple one of changing the "iCopyRow" to "iInsertRow" to no avail. Tried the "Insert Row" without required result. Any ideas??

Thanks for your help. It is greatly appreciated

[Carim]

Hi again,

Why don't you upload a zipped copy of your workbook ...?

[mudraker]

Insert row then populate row

Please Login or Register  to view this content.

As for starting at a different row number do as Carim suggested or to start at the next vacant row use the following code before the start of the For loop

Please Login or Register  to view this content.

[TCollin6]

I'm working on a project along the same lines, am using the above code but running into a few difficulties. I am moving rows of data based on a drop-down selection in colum L. For example, if someone selects "Success" in column L, the row would be moved to the tab titled "Success" when the macro is run. Below is what I have, based on previous code, so far. I ran the below script testing just the Success and Separated options with no luck. Any assistance would be greatly appreciated!!

Please Login or Register  to view this content.

~~ TCollin6

[mudraker]

What sheet are you copying from?

This part of the code will need to be modified to accept different worksheets.
At the moment it is fixed to copy from ws1 to ws2

Please Login or Register  to view this content.

It would be best if you could post a copy of your workbook.

[TCollin6]

Ok, that makes sense now. I'm going to change a few things really quick. I that doesn't work i'll post a copy of my workbook for you to take a look at it.

Thanks mudraker, you rock!

~~ TCollin6

[boylejob]

Mudraker indicated "I prefer to use For Type Loops than Do type loops"

Is there any particular reason why? Do they run faster or are they less prone to encounter errors.

I have always used the Do type loop, but if the efficiency and or accuracy of my programs can be improved by using a For type loop then I might want to rethink some of my programs.

I am learning so much from this list! It is great!

Jeff

[TCollin6]

Ok, still no luck most likely to an error on my part in communicating what i'm after. I attached a copy of the work-sheet I'm working with. I need to move the information to the sheet selected in column L. There will be multiple moves. I don't know if it can be done, but if anyone can figure this one out it's you guys!

~~ TCollin6

[Welshcontingent]

Insert row then populate row

Please Login or Register  to view this content.

As for starting at a different row number do as Carim suggested or to start at the next vacant row use the following code before the start of the For loop

Please Login or Register  to view this content.

Guys,
Thanks so much with this, it's working perfectly how I want it to, and makes life so much easier.

I should really keep up with the play on these things (Coding) and then I wouldn't forget it in the first place, and mix n match it as I need it.

Once again thanks it is greatly appreciated, and hopefully one day I'll be able to return the favour somehow.

The Welsh Contingent

[mudraker]

Welshcontingent

Glad to hear all is ok for you


boylejob

I prefer to use For loops beacause as part of setting up the for loop you program how many loops to do and it makes it easy to refer to the For loop counter when refering to row or column numbers

With Do Loops you need to add code to test something on each loop like testing to see if a cell is blank to know when to break out of the loop.

Example
you have 1000 rows of data to loop through

In a Do loop you need to test 1000 cells values to know when to stop. This is the way alot of people code from observing code posted on this site.

In a For loop you use the last used row number - with the way I code to get the lastrow number this is usually 2 action in 1 line of code.

During the Do loop you need to program a counter to add 1 to itself

Whilst you could get the last used row number & then use a counter within the Do loop I beleive it is still not as efficient as using the For loop counter


To me for these reasons the For loop appears more efficient.

[mudraker]

One thing I did not mention in my previous post

In a For loop once the start and end numbers are set a macro will run much quicker than a Do loop that has to access a worksheet to check if a cell is blank in order to know when to break out of the loop is a lot slower than checking a variable setting which is in memory