Formula or vba code to copy entire column based on text found in entire cell range

[Rahulkr]

Dear All masters, I am having some data like below:- Is there any formula or Vba code to fetch or copy entire column based on text found. Many many thanks in advance.

Column	A	B	C	D	E	F	G
Row 1	Days	D1	D2	D3	D4	D5	D6
Row 2	type	PH1	PH2	PH3	PH4	PH5	PH6
Row 3					jk
Row 4					lm
Row 5
Row 6					no
Row 7
Row 8					08:30 Ande
Row 9
Row 10					789pq
Row 11					89yy

From this data I need to copy only that entire column in which the word "ANDE" is found and paste the entire row as below in another sheet:-

It it fixed that the word which I have to search will be unique, but it is not fixed that the word which I will search will be in same column, every time it will be changing the position from cell to cell and column to column.
But the given range of data will be fixed.

Many many thanks in advance. if any one can help on this.

 

[Joe4]

Did you try the new code I posted in post 9?
You might have missed it, as I posted it just before your reply.

 

[Rahulkr]

Did you try the new code I posted in post 9?
You might have missed it, as I posted it just before your reply.

Yes joe, but still it is throwing the same error as subscript out of range

 

[Joe4]

On which line?
Did you place this in the "Sheet1" module?
Are you sheets REALLY named Sheet1 and Sheet2, or something else?
Are there any locked or merged cells on either sheet?

 

[Rahulkr]

On which line?
Did you place this in the "Sheet1" module?
Are you sheets REALLY named Sheet1 and Sheet2, or something else?
Are there any locked or merged cells on either sheet?

it is not showing error on any line, but when I am shifting the Ande from one column to another it is throwing the error.
Joe if have not changed any sheet names.
There is no locked or merged cell
joe this is the link for downloading my file you can see it.
demo.xlsm

 

[Rahulkr]

it is not showing error on any line, but when I am shifting the Ande from one column to another it is throwing the error.
Joe if have not changed any sheet names.
There is no locked or merged cell
joe this is the link for downloading my file you can see it.
demo.xlsm

I have not changed anything Joe. How you are asking me I am doing like that only.

 

[Joe4]

I cannot download files at this moment (my workplace security team strictly forbids it), but will have access to my how computer in a little while.
Can you explain exactly what you are doing when you get this error?
How exactly are you "shifting" this value over?

 

[Rahulkr]

Joe if your are not able to download then please see my original file data as below.

Drivers	driver 1	driver 2	driver 3	driver 4	driver 5	driver 6
ph no	PH1	PH2	PH3	PH4	PH5	PH6
pickup area	9111	811	6111	3558	2254	2255
pickup time	08:00 Ande		08:30 Max		09:10 Mark
Drop

Below is the full instructions what I got to build this schedule preparation.

Assume this is the pick and drop schedule for a day for Ande then the entire column B1:B11 should be copied and paste in Sheet2

Assume this is the pick and drop schedule for a day for Max then the entire column D1:D11 should be copied and paste in Sheet3

Assume this is the pick and drop schedule for a day for Mark then the entire column F1:F11 should be copied and paste in Sheet4

It is not sure that on next day either Ande, Max, or Mark will be assigned to Same driver, then can be assigned to any driver (ie, driver2 or driver 6 or so on)

if Ande, Max, or Mark were manipulated from any of the entire Driver column then that entier Driver column should be copied and paste as per their respective sheets assigned

if there is no such word as Ande, Max, or Mark found in entire data then there will be no data in sheet 2,3, or 4

 

[Rahulkr]

I cannot download files at this moment (my workplace security team strictly forbids it), but will have access to my how computer in a little while.
Can you explain exactly what you are doing when you get this error?
How exactly are you "shifting" this value over?

Joe thanks from my bottom of heart, you are doing much help to me.

As per your instruction I am doing all as what you are instructing me. but still it showing the same error " Subscript out of range error"

 

[Joe4]

As per your instruction I am doing all as what you are instructing me. but still it showing the same error " Subscript out of range error"

You haven't answered the question I am trying to ask you.
Tell me the EXACT steps you are doing when this error occurs.
In order for me to recreate it, I want to do the exact same thing you are doing that causes this error to happen.

For example, I am looking for you to tell me something like:
"I am typing Ande in cell B4".
- or -
"I am copying and pasting cell C3 to C4".

 

[Fluff]

Are you sure it's a "Subscript out of range" error? I don't get that using Joe's code.
However you may get a different error depending on which version of Excel you are using.
Can you please update your account details to show your actual version of Xl rather than all the options.