Repeat blocks of rows based in criteria

[crocco]

Hi all,

I have a tab called ‘VBAinputs’. On this tab starting on cell A4 and descending are any number of text strings. Also on this tab cell C3 contains a number (this will be a starting copy row indicator) and cell D3 contains a number (this will be the number of rows in a copy block),

I have another tab called ‘SVPulls’ containing rows of data.

I would like some VBA to do the following with entire rows of data on the ‘SVPulls’ tab:

Starting at the row number designated in cell C3 of ‘VBAinputs’ (eg. 25) create a selection range that includes the whole row and a total number of rows designated in cell D3 of ‘VBAinputs’ (eg. 10). The range then will be copied as many times as there are text strings (starting in A4) in column A of ‘VBAinputs’. As each block is being copied the top left cell of the block needs to be the text string.

So let’s say in the ‘VBAinputs’ tab I have cells A4 to A7 as apples, pears, oranges, plums. Cell C3=25 and cell D3=10, the following will happen on the ‘SVpulls’ tab:

Starting at row 25 a block of 10 rows will be copied, this will be pasted repeating 4 times and in each paste block the top-right most cells will contain apples, pears, oranges, plums. No gaps required between paste blocks.

Thanks!

 

[DanteAmor]

Continuing with your example.
The first block in which cell must be pasted.
The second in which cell.
The third in which cell.
The 4th in which cell.

 

[crocco]

The blocks are pasted down the sheet based on the start row and the individual block size.

So in my example on the ‘SVPulls’ tab a block of ten rows is copied from row 25, and pasted at row 35.

The number of blocks is determined by the number of text strings in column A of ‘VBAinputs’.

Cell A35 will have ‘apples’ in it and cell A45 will have ‘pears’ and so on until there is a block for every text string.

In my original post I accidentally wrote ‘top-right’ when I meant ‘top-left’ cell for the text string. Apologies for the confusion.

Thanks

 

[DanteAmor]

Now I'm more confused.
You could put 3 images.
Image 1: sheet VBAinputs before execution
Image 2: sheet SVPulls
Image 3: sheet VBAinputs after execution

You could upload a copy of your file or image file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[crocco]

A block of rows is copied from ‘SVPulls’, it starts on a specified row (25) and is a certain row size (10 rows). These two numbers are inputs on ‘VBAinputs’ tab and should be changeable, for example if I want the start row to be 50 and the the number of rows copied to be 15. These inputs can go anywhere I don’t really mind.

That block is pasted down the page in the ‘SVpulls’ tab a number of times decided by a list of text strings. In my example there are four text strings, so the block of ten rows is pasted four times. But if there were ten text strings then it should paste ten times.

As each block is pasted the top-left cell of the block changes to the text string.

So using the example above, rows 25 to 34 are copied, and pasted to rows 35, 45, 55 and 65. As a block is copied the top-left cell is changed to the text string so A35=apples, A45=Pears, A55=oranges and A65=plums.

All the copy paste activity happens on ‘SVpulls’.

‘VBAinputs’ provides the list of text strings, the row size of the copy block and the start row of the paste.

Read my first post again, it does make sense. Ignore the ‘top-right’ though it should be too-left.

 

[crocco]

Sorry! VBAinputs does not have the start row of the paste!

I meant the start row of the copy, from which you can work out the start row of the paste which is the first row after the copy block.

 

[DanteAmor]

I understood, you want to copy rows, start in a row and a number of rows.
What I do not understand is where to paste.

So using the example above, rows 25 to 34 are copied, and pasted to rows 35, 45, 55 and 65. As a block is copied the top-left cell is changed to the text string so A35=apples, A45=Pears, A55=oranges and A65=plums.

This does not make sense to me.
If on the "SVpulls" sheet you have 100 rows with data, you copy rows 25 to 34 and you paste them on row 35, then you would lose the information from rows 35 down.
You want that?

 

[crocco]

Yes,

Because the ‘SVPulls’ starting position will be one block of a certain row size to be copied and pasted.

After that the code is done.

Also I already figured it out myself but thanks for you responses.

 

[DanteAmor]

Yes,

Because the ‘SVPulls’ starting position will be one block of a certain row size to be copied and pasted.

After that the code is done.

Also I already figured it out myself but thanks for you responses.

Then, you have it solved. perfect! Let me know if you have any questions.