Loop

[Amjtoo]

Hi all,

I have an excel sheet where there are data with empty rows in between.
My aim is to cut a range of data based on a cell value in column A and paste the data in a new worksheet by creating one.
Eg- if there is the word "name" in any row in col A then from that cell it will select xltoright and xldown , cut the entire data n paste in a new worksheet in the same file.
Again it will come back to the original sheet n scout through the rows to find cell with "name" and do the same operation but only paste in another new worksheet.
The data is not continuous so I can't run do until isempty loop

Kindly suggest if it's possible to achieve it by coding n if yes,how?

 

[DanteAmor]

Hi and welcome to MrExcel.

So if you have 100 rows with the word "Name" do you want to create 100 new sheets?

You could put a sample with generic data of how your data is and which rows should be copied to a new sheet.
Use XL2BB tool.
I imagine your data is like this, so I suppose you want block 1 on a new sheet and block 3 on a new sheet.

Dante Amor
	A	B	C	D	E	F
1	Head1	Head2	Head3	Head4	Head5	Head6
2	Name	data	data	data	data	data
3	x	data	Block 1			data
4	y	data				data
5	z	data	data	data	data	data
6
7	Other	data	data	data	data	data
8	x	data	Block 2			data
9	y	data				data
10	z	data	data	data	data	data
11
12	Name	data	data	data	data	data
13	x	data	Block 3			data
14	y	data				data
15	z	data	data	data	data	data
16
17	Other	data	data	data	data	data
18	x	data	Block 4			data
19	y	data				data
20	z	data	data	data	data	data
Hoja1

 

[Amjtoo]

Hi and welcome to MrExcel.

So if you have 100 rows with the word "Name" do you want to create 100 new sheets?

You could put a sample with generic data of how your data is and which rows should be copied to a new sheet.
Use XL2BB tool.
I imagine your data is like this, so I suppose you want block 1 on a new sheet and block 3 on a new sheet.

Dante Amor
	A	B	C	D	E	F
1	Head1	Head2	Head3	Head4	Head5	Head6
2	Name	data	data	data	data	data
3	x	data	Block 1			data
4	y	data				data
5	z	data	data	data	data	data
6
7	Other	data	data	data	data	data
8	x	data	Block 2			data
9	y	data				data
10	z	data	data	data	data	data
11
12	Name	data	data	data	data	data
13	x	data	Block 3			data
14	y	data				data
15	z	data	data	data	data	data
16
17	Other	data	data	data	data	data
18	x	data	Block 4			data
19	y	data				data
20	z	data	data	data	data	data
Hoja1

Hi amor,
My data looks precisely like what you shared and also the macro can be designed to run 4 times

 

[Amjtoo]

Hi amor,
My data looks precisely like what you shared and also the macro can be designed to run 4 times

I want to cut block 1 paste in a new tab and again come back to the main sheet n cut block 2 n paste in another tab.. total 4 times I need to run this operation

 

[DanteAmor]

I want to cut block 1 paste in a new tab and again come back to the main sheet n cut block 2 n paste in another tab.. total 4 times I need to run this operation

if there is the word "name" in any row in col A then from that cell it will select xltoright and xldown

Do you no longer want to check if the word "Name" exists in column A?

 

[Amjtoo]

Do you no longer want to check if the word "Name" exists in column A?

I didn't get your question...
I want to implement a vba code containing if loop that will run 4 times n everytime it encounters the word Name in col A ( which will appear 4 times ) it will take the entire range (xltoright n xldown ) n cut paste in a new worksheet

 

[DanteAmor]

I didn't get your question...
I want to implement a vba code containing if loop that will run 4 times n everytime it encounters the word Name in col A ( which will appear 4 times ) it will take the entire range (xltoright n xldown ) n cut paste in a new worksheet

Now I don't understand what you need.
You can put an example of your data with the XL2BB tool and another example of the result that you need.

 

[Amjtoo]

 

[Amjtoo]

So my objective is to copy those 4 blocks dynamically and paste in 4 different tabs

 

[DanteAmor]

Are those cells, in the image above, highlighted in red, always empty?