Easy VBA need help: Output unique value to new sheet

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
Hi,

I need to write a code to output the unique value of current range which contains repeating value and text but no space to a new sheet called "MyNewSheet".

Then I need another set of code, same as above, but if the system found "MyNewSheet" is already there, output it to sheet "MyNewSheet1",
"MyNewSheet2","MyNewSheet3" etc.

Thanks
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Howdy Mr. Chong,
Let's see if we can start by breaking this down into seperate steps.

1. What exactly do you mean by: "the unique value of current range which contains repeating value and text but no space"?

2. What do you mean by "current range"? (The used range, the selected range, a specified range...?)
 
Upvote 0
Thanks.

1) Suppose in one sheet it has a single column list

ABC
DEF
XYZ
XYZ
ABC

Then if someone Highlighted this range, and click a button containing the sub, then it will outout to a new sheet called "NewSheet" a new list of unique values, such as

ABC
DEF
XYZ

Yes current range means selected range (sorry I am new to VBA).

Pls help...
 
Upvote 0
I am not a complete lazy person just purely ask people here. I tried also. These are my "half-done" code.

Sub Test()
Selection.Offset(-1).Select
'Range("r - 1,c: r - 1 + rr + 1,c").Select
ActiveCell.FormulaR1C1 = "Output Result"
Selection.Resize.CurrentRegion.Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"E21"), Unique:=True
End Sub

But I am stuck in the middle. Pls help...
 
Upvote 0
Ok this is my latest code. It works to certain extent, but I know it is poorly written :)

The problem with this code is:-

(1) What is AE21 has value already ?
(2) What if the Sheetname "MyNewSheet" is already there ? Is there way to make it "MyNewSheet1" etc if "MyNewSheet" is already there ?

Thanks.
====

Sub Test()
Dim c As Integer
Selection.Offset(-1).Select
'Range("r - 1,c: r - 1 + rr + 1,c").Select
c = ActiveCell.Column
ActiveCell.FormulaR1C1 = Format("Output Result", ";;;")
Selection.Resize.CurrentRegion.Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"AE21"), Unique:=True
Range("AE21").Select
Selection.CurrentRegion.Select
Selection.Cut
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = "MyNewSheet"
Range("D10").Select
MsgBox "Done !!!"
End Sub
 
Upvote 0
OK, just got home from work so I haven't had much chance to check out your code.
Here's a way to deal with checking for and creating the new sheets. I'll see about incorporating the rest of your routine into it. For now, try this and see if I understand your intent.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> MrChongTestSub()
<SPAN style="color:#00007F">Dim</SPAN> Ws <SPAN style="color:#00007F">As</SPAN> Worksheet, ShtCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, NewShtCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
ShtCnt = Sheets.Count
NewShtCnt = 0
x = 0

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Ws <SPAN style="color:#00007F">In</SPAN> Worksheets
    <SPAN style="color:#00007F">If</SPAN> Ws.Name = "MyNewSheet" <SPAN style="color:#00007F">Then</SPAN>
    x = 1
    Else: <SPAN style="color:#00007F">If</SPAN> InStr(Ws.Name, "MyNewSheet") <SPAN style="color:#00007F">Then</SPAN> NewShtCnt = NewShtCnt + 1
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">If</SPAN> x = 0 <SPAN style="color:#00007F">Then</SPAN>
    Sheets.Add , After:=Sheets(ShtCnt)
    ActiveSheet.Name = "MyNewSheet"
    <SPAN style="color:#007F00">'Your other code here</SPAN>
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Ws <SPAN style="color:#00007F">In</SPAN> Worksheets
    <SPAN style="color:#00007F">If</SPAN> InStr(Ws.Name, "MyNewSheet") And Ws.Index = ShtCnt <SPAN style="color:#00007F">Then</SPAN>
        Sheets.Add , After:=Sheets(ShtCnt)
        ActiveSheet.Name = "MyNewSheet" & NewShtCnt + 1
        NewShtCnt = NewShtCnt + 1
        <SPAN style="color:#007F00">'Your other code here</SPAN>
   <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope it helps (so far),
Dan
 
Upvote 0

Forum statistics

Threads
1,213,479
Messages
6,113,895
Members
448,530
Latest member
yatong2008

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top