VBA: Get an unique list in a new sheet named "unique" in a column.

[icesanta]

VBA: Get an unique list in a new sheet named "unique" in a column.
Data is in multiple sheets in each cell.
Some sheets, rows, columns or cells may be empty.
VBA will not work in sheet named "data1" and "data2".
VBA will not override if data exists in "unique" sheet.

 

[Kamolga]

Hi,

I am not sure I fully understood what you are looking for but maybe this is a good start and easy to adapt to your needs (might have issues with empty cell, simply select the range that you need then)

Sub MacImportSheets()
  'Copy all sheets to Unique Sheet if different than data1 and data 2
    Sheets("Unique").Activate
    Dim Sht As Worksheet
        For Each Sht In ActiveWorkbook.Worksheets
            If (Sht.Name <> "Unique" And Sht.Name <> "data1" And Sht.Name <> "data2") Then
                Sht.Select
                Range("A1").CurrentRegion.Copy
                Sheets("Unique").Select
                Sheets("Unique").Activate
                Range("A65536").End(xlUp).Offset(1, 0).Select
                ActiveSheet.Paste
                Else
            End If
        Next Sht
    'Erase all duplicates in column A
    Sheets("unique").Select
    ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

 

[icesanta]

hmm..It is not picking data from multiple columns or rows [VBA is considering only the 1st column]
The first row must be skipped as its' the header.
Also the result column [That should be the A column only] is starting from A3.

 

[Kamolga]

Maybe

Sub MacImportSheets()
  'Copy all sheets to Unique Sheet if different than data1 and data 2
    Sheets("Unique").Activate
    Dim Sht As Worksheet
        For Each Sht In ActiveWorkbook.Worksheets
            If (Sht.Name <> "Unique" And Sht.Name <> "data1" And Sht.Name <> "data2") Then
                Sht.Select
                Range("A3:A9999").Copy
                Sheets("Unique").Select
                Sheets("Unique").Activate
                Range("A65536").End(xlUp).Offset(1, 0).Select
                ActiveSheet.Paste
                Else
            End If
        Next Sht
    'Erase all duplicates in column A
    Sheets("unique").Select
    ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

[/QUOTE]

 

[icesanta]

My range is complete sheet's. So I think the below line is the problem
Range("A3:A9999").Copy

 

[Kamolga]

To copy entire sheet, use

Cells.copy

But you can not copy a sheet and paste it somwhere else than A1 without getting an error (as your paste would go out of the sheet). So, if you know you will never get more than 52 columns and 10000 lines, just use

Range("A1:ZZ10000").copy

or any range that suits you.

 

[icesanta]

The code will copy the sheet exactly.

 

[icesanta]

I mean the code is not working as desired.

 

[icesanta]

Any expert there ?

 

[icesanta]

[h=2]VBA: Get an unique list in a new sheet named "unique" in a column.[/h]

<acronym title="visual basic for applications">VBA</acronym>: Get an unique list in a new sheet named "unique" in a column.

Data is in multiple sheets in each cell. Some sheets, rows, columns or cells may be empty.

<acronym title="visual basic for applications">The VBA</acronym> will not work in sheet named "data1" and "data2".

<acronym title="visual basic for applications">The VBA</acronym> will not override if data exists in "unique" sheet but will start from the next empty cell of "unique" sheet.
​