## Extract Names from different sheet into one sheet

### Extract Names from different sheet into one sheet

Hello Experts,

i have data of names sitting in sheet 2 to sheet 4 in the attached. is it
possible to have a formula to extract all these names into one column in
sheet 1. thanks

### Extract Names from different sheet into one sheet

Hi Hilary,

Sub extract_Sheetname()
Dim ws As Worksheet
Dim i As Integer
Dim j As Integer
Set ws = ActiveWorkbook.Sheets(1)
For i = 1 To Sheets.Count
j = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1
ws.Cells(j, "B").Value = Sheets(i).Name
Next i
End Sub

--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com

> Hello Experts,

> i have data of names sitting in sheet 2 to sheet 4 in the attached. is it
> possible to have a formula to extract all these names into one column in
> sheet 1. thanks

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

### Extract Names from different sheet into one sheet

See attached sheet.

> Hi Hilary,

> Sub extract_Sheetname()
> Dim ws As Worksheet
> Dim i As Integer
> Dim j As Integer
> Set ws = ActiveWorkbook.Sheets(1)
> For i = 1 To Sheets.Count
> j = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1
> ws.Cells(j, "B").Value = Sheets(i).Name
> Next i
> End Sub

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com

>> Hello Experts,

>> i have data of names sitting in sheet 2 to sheet 4 in the attached. is it
>> possible to have a formula to extract all these names into one column in
>> sheet 1. thanks

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------

>> To unsubscribe, send a blank email to

--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com

### Extract Names from different sheet into one sheet

Why not use a macro to gather all and then do a unique on the gathered list?

Don Guillett
Microsoft MVP Excel
SalesAid Software

From: hilary lomotey
Sent: Wednesday, June 06, 2012 10:39 AM
To: excel-macros
Subject: \$\$Excel-Macros\$\$ Extract Names from different sheet into one sheet

Hello Experts,

i have data of names sitting in sheet 2 to sheet 4 in the attached. is it possible to have a formula to extract all these names into one column in sheet 1. thanks
--
FORUM RULES (986+ members already BANNED for violation)

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------

### Extract Names from different sheet into one sheet

Thanks, Noorain, sorry if i didnt explain my requirement well, i dont want
to extract the sheet name i want the data in the sheet name ie items in col
B. Is it possible with an excel formula ? that is preferable however if its
not possible, i wouldnt mind the code, but i only need a unique list not
repetitive anytime you activate ie if the code is used.

Don thanks but i dont know VBA thats i prefer formulas

>   Why not use a macro to gather all and then do a unique on the gathered
> list?

> Don Guillett
> Microsoft MVP Excel
> SalesAid Software

> *Sent:* Wednesday, June 06, 2012 10:39 AM

> *Subject:* \$\$Excel-Macros\$\$ Extract Names from different sheet into one
> sheet

> Hello Experts,

> i have data of names sitting in sheet 2 to sheet 4 in the attached. is it
> possible to have a formula to extract all these names into one column in
> sheet 1. thanks
> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

### Extract Names from different sheet into one sheet

check the attachment . see if it helps.

>   Why not use a macro to gather all and then do a unique on the gathered
> list?

> Don Guillett
> Microsoft MVP Excel
> SalesAid Software

> *Sent:* Wednesday, June 06, 2012 10:39 AM

> *Subject:* \$\$Excel-Macros\$\$ Extract Names from different sheet into one
> sheet

> Hello Experts,

> i have data of names sitting in sheet 2 to sheet 4 in the attached. is it
> possible to have a formula to extract all these names into one column in
> sheet 1. thanks
> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

--
*Regards*
* *
*Ashish Koul*
*http://www.excelvbamacros.com/*
*http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

P Before printing, think about the environment.

### Extract Names from different sheet into one sheet

Hi hilary,

Sorry, I miss understood, Please use it

Sub extract_uniquelist()
Dim ws As Worksheet
Dim i As Integer
Dim j As Integer
Dim rng As Range
Set ws = ActiveWorkbook.Sheets(1)
For i = 2 To Sheets.Count
Set rng = Sheets(i).Range("B4:B" & Sheets(i).Range("B65536").End(xlUp).Row)
j = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1
rng.Copy ws.Cells(j, "B")
Next i
ws.Range("B4:B" & ws.Range("B65536").End(xlUp).Row).RemoveDuplicates 1
End Sub

--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com

>   Why not use a macro to gather all and then do a unique on the gathered
> list?

> Don Guillett
> Microsoft MVP Excel
> SalesAid Software

> *Sent:* Wednesday, June 06, 2012 10:39 AM

> *Subject:* \$\$Excel-Macros\$\$ Extract Names from different sheet into one
> sheet

> Hello Experts,

> i have data of names sitting in sheet 2 to sheet 4 in the attached. is it
> possible to have a formula to extract all these names into one column in
> sheet 1. thanks
> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

### Extract Names from different sheet into one sheet

please find the updated file as it will treat first country name as field

Thanks

> Hi hilary,

> Sorry, I miss understood, Please use it

> Sub extract_uniquelist()
> Dim ws As Worksheet
> Dim i As Integer
> Dim j As Integer
> Dim rng As Range
> Set ws = ActiveWorkbook.Sheets(1)
> For i = 2 To Sheets.Count
> Set rng = Sheets(i).Range("B4:B" & Sheets(i).Range("B65536").End(xlUp).Row)
> j = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1
> rng.Copy ws.Cells(j, "B")
> Next i
> ws.Range("B4:B" & ws.Range("B65536").End(xlUp).Row).RemoveDuplicates 1
> End Sub

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com

>>   Why not use a macro to gather all and then do a unique on the gathered
>> list?

>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software

>> *Sent:* Wednesday, June 06, 2012 10:39 AM

>> *Subject:* \$\$Excel-Macros\$\$ Extract Names from different sheet into one
>> sheet

>> Hello Experts,

>> i have data of names sitting in sheet 2 to sheet 4 in the attached. is it
>> possible to have a formula to extract all these names into one column in
>> sheet 1. thanks
>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------

>> To unsubscribe, send a blank email to

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------

>> To unsubscribe, send a blank email to

>  --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

--
*Regards*
* *
*Ashish Koul*
*http://www.excelvbamacros.com/*
*http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

P Before printing, think about the environment.

### Extract Names from different sheet into one sheet

Ash and Noorain, pls am having errors with all the work solutions provided

macro to extract, i get a debug error

i guess formula wont work for this type of problem. thanks

> please find the updated file as it will treat first country name as field

> Thanks

>> Hi hilary,

>> Sorry, I miss understood, Please use it

>> Sub extract_uniquelist()
>> Dim ws As Worksheet
>> Dim i As Integer
>> Dim j As Integer
>> Dim rng As Range
>> Set ws = ActiveWorkbook.Sheets(1)
>> For i = 2 To Sheets.Count
>> Set rng = Sheets(i).Range("B4:B" &
>> Sheets(i).Range("B65536").End(xlUp).Row)
>> j = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1
>> rng.Copy ws.Cells(j, "B")
>> Next i
>> ws.Range("B4:B" & ws.Range("B65536").End(xlUp).Row).RemoveDuplicates 1
>> End Sub

>> --
>> Thanks & regards,
>> Noorain Ansari
>> www.noorainansari.com
>> www.excelmacroworld.blogspot.com

>>>   Why not use a macro to gather all and then do a unique on the
>>> gathered list?

>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software

>>> *Sent:* Wednesday, June 06, 2012 10:39 AM

>>> *Subject:* \$\$Excel-Macros\$\$ Extract Names from different sheet into one
>>> sheet

>>> Hello Experts,

>>> i have data of names sitting in sheet 2 to sheet 4 in the attached. is
>>> it possible to have a formula to extract all these names into one column in
>>> sheet 1. thanks
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)

>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.

>>> 2) Don't post a question in the thread of another member.

>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.

>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.

>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.

>>> ------------------------------------------------------------------------------------------------------

>>> To unsubscribe, send a blank email to

>>> --
>>> FORUM RULES (986+ members already BANNED for violation)

>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.

>>> 2) Don't post a question in the thread of another member.

>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.

>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.

>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.

>>> ------------------------------------------------------------------------------------------------------

>>> To unsubscribe, send a blank email to

>>  --
>> FORUM RULES (986+ members already BANNED for violation)

>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------

>> To unsubscribe, send a blank email to

> --
> *Regards*
> * *
> *Ashish Koul*
> *http://www.excelvbamacros.com/*
> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

> P Before printing, think about the environment.

>  --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

### Extract Names from different sheet into one sheet

try this update one  also can you tell me which version of excel r u using
....

> Ash and Noorain, pls am having errors with all the work solutions provided

> macro to extract, i get a debug error

> i guess formula wont work for this type of problem. thanks

>> please find the updated file as it will treat first country name as field

>> Thanks

>>> Hi hilary,

>>> Sorry, I miss understood, Please use it

>>> Sub extract_uniquelist()
>>> Dim ws As Worksheet
>>> Dim i As Integer
>>> Dim j As Integer
>>> Dim rng As Range
>>> Set ws = ActiveWorkbook.Sheets(1)
>>> For i = 2 To Sheets.Count
>>> Set rng = Sheets(i).Range("B4:B" &
>>> Sheets(i).Range("B65536").End(xlUp).Row)
>>> j = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1
>>> rng.Copy ws.Cells(j, "B")
>>> Next i
>>> ws.Range("B4:B" & ws.Range("B65536").End(xlUp).Row).RemoveDuplicates 1
>>> End Sub

>>> --
>>> Thanks & regards,
>>> Noorain Ansari
>>> www.noorainansari.com
>>> www.excelmacroworld.blogspot.com

>>>>   Why not use a macro to gather all and then do a unique on the
>>>> gathered list?

>>>> Don Guillett
>>>> Microsoft MVP Excel
>>>> SalesAid Software

>>>> *Sent:* Wednesday, June 06, 2012 10:39 AM

>>>> *Subject:* \$\$Excel-Macros\$\$ Extract Names from different sheet into
>>>> one sheet

>>>> Hello Experts,

>>>> i have data of names sitting in sheet 2 to sheet 4 in the attached. is
>>>> it possible to have a formula to extract all these names into one column in
>>>> sheet 1. thanks
>>>> --
>>>> FORUM RULES (986+ members already BANNED for violation)

>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>> will not get quick attention or may not be answered.

>>>> 2) Don't post a question in the thread of another member.

>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>> measure.

>>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>>> signatures are prohibited.

>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>> Forum owners and members are not responsible for any loss.

>>>> ------------------------------------------------------------------------------------------------------

>>>> To unsubscribe, send a blank email to

>>>> --
>>>> FORUM RULES (986+ members already BANNED for violation)

>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>> will not get quick attention or may not be answered.

>>>> 2) Don't post a question in the thread of another member.

>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>> measure.

>>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>>> signatures are prohibited.

>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>> Forum owners and members are not responsible for any loss.

>>>> ------------------------------------------------------------------------------------------------------

>>>> To unsubscribe, send a blank email to

>>>  --
>>> FORUM RULES (986+ members already BANNED for violation)

>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.

>>> 2) Don't post a question in the thread of another member.

>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.

>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.

>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.

>>> ------------------------------------------------------------------------------------------------------

>>> To unsubscribe, send a blank email to

>> --
>> *Regards*
>> * *
>> *Ashish Koul*
>> *http://www.excelvbamacros.com/*
>> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

>> P Before printing, think about the environment.

>>  --
>> FORUM RULES (986+ members already BANNED for violation)

>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------

>> To unsubscribe, send a blank email to

>  --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

--
*Regards*
* *
*Ashish Koul*
*http://www.excelvbamacros.com/*
*http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

P Before printing, think about the environment.

### Extract Names from different sheet into one sheet

Nice One

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]

On Behalf Of ashish koul
Sent: 06 June 2012 10:00

Subject: Re: \$\$Excel-Macros\$\$ Extract Names from different sheet into one
sheet

please find the updated file as it will treat first country name as field

Thanks

Hi hilary,

Sorry, I miss understood, Please use it

Sub extract_uniquelist()

Dim ws As Worksheet

Dim i As Integer

Dim j As Integer

Dim rng As Range

Set ws = ActiveWorkbook.Sheets(1)

For i = 2 To Sheets.Count

Set rng = Sheets(i).Range("B4:B" & Sheets(i).Range("B65536").End(xlUp).Row)

j = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1

rng.Copy ws.Cells(j, "B")

Next i

ws.Range("B4:B" & ws.Range("B65536").End(xlUp).Row).RemoveDuplicates 1

End Sub

--
Thanks & regards,
Noorain Ansari
www.noorainansari.com <http://www.noorainansari.com/>

<http://www.excelmacroworld.blogspot.com/> www.excelmacroworld.blogspot.com

Why not use a macro to gather all and then do a unique on the gathered list?

Don Guillett
Microsoft MVP Excel
SalesAid Software

Sent: Wednesday, June 06, 2012 10:39 AM

Subject: \$\$Excel-Macros\$\$ Extract Names from different sheet into one sheet

Hello Experts,

i have data of names sitting in sheet 2 to sheet 4 in the attached. is it
possible to have a formula to extract all these names into one column in
sheet 1. thanks

--
FORUM RULES (986+ members already BANNED for violation)

Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

--
FORUM RULES (986+ members already BANNED for violation)

Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

--
FORUM RULES (986+ members already BANNED for violation)

Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

--

Regards

Ashish Koul

http://www.excelvbamacros.com/

<http://www.accessvbamacros.com/> http://www.accessvbamacros.com/

P Before printing, think about the environment.

--
FORUM RULES (986+ members already BANNED for violation)

Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

### Extract Names from different sheet into one sheet

i use 2007, look at the error below, what am i doing wrong
[image: Inline image 1]

> Nice One****

> ** **

> * *

> *Regards*

> *Rajan verma*

> *+91 7838100659 [IM-Gtalk]*

> ** **

> *Sent:* 06 June 2012 10:00

> *Subject:* Re: \$\$Excel-Macros\$\$ Extract Names from different sheet into
> one sheet****

> ** **

> please find the updated file as it will treat first country name as field

> ** **

> Thanks ****

> ** **

> ** **

> ** **

> Hi hilary,****

> ** **

> Sorry, I miss understood, Please use it****

> ** **

> Sub extract_uniquelist()****

> Dim ws As Worksheet****

> Dim i As Integer****

> Dim j As Integer****

> Dim rng As Range****

> Set ws = ActiveWorkbook.Sheets(1)****

> For i = 2 To Sheets.Count****

> Set rng = Sheets(i).Range("B4:B" & Sheets(i).Range("B65536").End(xlUp).Row)
> ****

> j = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1****

> rng.Copy ws.Cells(j, "B")****

> Next i****

> ws.Range("B4:B" & ws.Range("B65536").End(xlUp).Row).RemoveDuplicates 1****

> End Sub****

> ** **

> ** **

> ** **

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com****

> www.excelmacroworld.blogspot.com****

> ** **

> **

> Why not use a macro to gather all and then do a unique on the gathered
> list?****

>  ****

> Don Guillett
> Microsoft MVP Excel
> SalesAid Software

>  ****

> *Sent:* Wednesday, June 06, 2012 10:39 AM****

> *Subject:* \$\$Excel-Macros\$\$ Extract Names from different sheet into one
> sheet****

>  ****

> Hello Experts, ****

>  ****

> i have data of names sitting in sheet 2 to sheet 4 in the attached. is it
> possible to have a formula to extract all these names into one column in
> sheet 1. thanks ****

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> ****

> ** **

> ****

> ** **

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> ****

> ** **

> -- ****

> *Regards*****

> * *****

> *Ashish Koul*****

> *http://www.excelvbamacros.com/*****

> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/> ****

>  ****

> P Before printing, think about the environment.****

>  ****

> ** **

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

### Extract Names from different sheet into one sheet

Hi Hilary,

--
Thanks & regards,
Noorain Ansari
www.noorainansari.com

www.excelmacroworld.blogspot.com

> Nice One****

> ** **

> * *

> *Regards*

> *Rajan verma*

> *+91 7838100659 [IM-Gtalk]*

> ** **

> *Sent:* 06 June 2012 10:00

> *Subject:* Re: \$\$Excel-Macros\$\$ Extract Names from different sheet into
> one sheet****

> ** **

> please find the updated file as it will treat first country name as field

> ** **

> Thanks ****

> ** **

> ** **

> ** **

> Hi hilary,****

> ** **

> Sorry, I miss understood, Please use it****

> ** **

> Sub extract_uniquelist()****

> Dim ws As Worksheet****

> Dim i As Integer****

> Dim j As Integer****

> Dim rng As Range****

> Set ws = ActiveWorkbook.Sheets(1)****

> For i = 2 To Sheets.Count****

> Set rng = Sheets(i).Range("B4:B" & Sheets(i).Range("B65536").End(xlUp).Row)
> ****

> j = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1****

> rng.Copy ws.Cells(j, "B")****

> Next i****

> ws.Range("B4:B" & ws.Range("B65536").End(xlUp).Row).RemoveDuplicates 1****

> End Sub****

> ** **

> ** **

> ** **

> **

> Why not use a macro to gather all and then do a unique on the gathered
> list?****

>  ****

> Don Guillett
> Microsoft MVP Excel
> SalesAid Software

>  ****

> *Sent:* Wednesday, June 06, 2012 10:39 AM****

> *Subject:* \$\$Excel-Macros\$\$ Extract Names from different sheet into one
> sheet****

>  ****

> Hello Experts, ****

>  ****

> i have data of names sitting in sheet 2 to sheet 4 in the attached. is it
> possible to have a formula to extract all these names into one column in
> sheet 1. thanks ****

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> ****

> ** **

> ****

> ** **

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> ****

> ** **

> -- ****

> *Regards*****

> * *****

> *Ashish Koul*****

> *http://www.excelvbamacros.com/*****

> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/> ****

>  ****

> P Before printing, think about the environment.****

>  ****

> ** **

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com

### Extract Names from different sheet into one sheet

Thanks Noorain, its working nicely. thanks

On Wed, Jun 6, 2012 at 4:56 PM, NOORAIN ANSARI <noorain.ans...@gmail.com>wrote:

> Hi Hilary,

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com

> www.excelmacroworld.blogspot.com

> On Wed, Jun 6, 2012 at 10:12 PM, Rajan_Verma <rajanverma1...@gmail.com>wrote:

>> Nice One****

>> ** **

>> * *

>> *Regards*

>> *Rajan verma*

>> *+91 7838100659 [IM-Gtalk]*

>> ** **

>> excel-macros@googlegroups.com] *On Behalf Of *ashish koul
>> *Sent:* 06 June 2012 10:00
>> *Subject:* Re: \$\$Excel-Macros\$\$ Extract Names from different sheet into
>> one sheet****

>> ** **

>> please find the updated file as it will treat first country name as field

>> ** **

>> Thanks ****

>> ** **

>> ** **

>> ** **

>> On Wed, Jun 6, 2012 at 9:43 PM, NOORAIN ANSARI <noorain.ans...@gmail.com>
>> wrote:****

>> Hi hilary,****

>> ** **

>> Sorry, I miss understood, Please use it****

>> ** **

>> Sub extract_uniquelist()****

>> Dim ws As Worksheet****

>> Dim i As Integer****

>> Dim j As Integer****

>> Dim rng As Range****

>> Set ws = ActiveWorkbook.Sheets(1)****

>> For i = 2 To Sheets.Count****

>> Set rng = Sheets(i).Range("B4:B" &
>> Sheets(i).Range("B65536").End(xlUp).Row)****

>> j = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1****

>> rng.Copy ws.Cells(j, "B")****

>> Next i****

>> ws.Range("B4:B" & ws.Range("B65536").End(xlUp).Row).RemoveDuplicates 1***
>> *

>> End Sub****

>> ** **

>> ** **

>> ** **

>> On Wed, Jun 6, 2012 at 9:26 PM, dguillett1 <dguille...@gmail.com> wrote:*
>> ***

>> Why not use a macro to gather all and then do a unique on the gathered
>> list?****

>>  ****

>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguille...@gmail.com****

>>  ****

>> *From:* hilary lomotey <resp...@gmail.com> ****

>> *Sent:* Wednesday, June 06, 2012 10:39 AM****

>> *Subject:* \$\$Excel-Macros\$\$ Extract Names from different sheet into one
>> sheet****

>>  ****

>> Hello Experts, ****

>>  ****

>> i have data of names sitting in sheet 2 to sheet 4 in the attached. is it
>> possible to have a formula to extract all these names into one column in
>> sheet 1. thanks ****

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to

>> ****

>> ** **

>> ****

>> ** **

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to

>> ****

>> ** **

>> -- ****

>> *Regards*****

>> * *****

>> *Ashish Koul*****

>> *http://www.excelvbamacros.com/*****

>> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/> ****

>>  ****

>> P Before printing, think about the environment.****

>>  ****

>> ** **

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com

>  --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com

> To unsubscribe, send a blank email to

Hello,

I have a small problem.  I have Lotus 5 for win 31.  I have a file that
has 60 sheets in it.  The sheets are monthly data and each month has 5
different sheets.

I need to know how to update one sheet and transfer those changes to the
other 11 sheets that are the same as the first one.  Group mode makes
changes to every sheet in a file and this messes my other report sheets
up.

I know I can copy/paste but the underlying problem is the creation of
new sheets of the same type.  When I copy/paste to a new sheet the
column widths don't get changed in the new file.  Is there a way to have
Lotus carry ALL the formatting over to a new sheet in a file?