I wish to copy a list of entries to a new sheet.
If i use the code below it first copy's cell "b2" and then from the
last cell to the first.
Now i have a double entry.

I want it to end at cell b2, because the first cell has collum
headings in it!

How do i write the code so that cell b2 is not copied first?

kind regards

Sjoerd


this is my code:

Sub CopyUniqueEntries()

Dim uniquelist As range

Set uniquelist = ActiveSheet.range("A2", "A10000")

Sheet1.range("b2",
Sheet1.range("b65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=uniquelist.Cells(1, 1),
Unique:=True

End Sub

Advanced Filter has a requirement that the list have a header, that the
header be included in the list range, and the header will be pasted along
with the unique list. Since you start the range at B2, The Filter assumes
that is the header and copies it, and then the unique list. This why B2 is
copied twice. If you start your Range at B1 to include the header, then B2
will not be duplicated, but the header will be pasted in A2. Either way you
define your Filter range, the entry in A2 is unwanted and needs to be
deleted after the paste.
Also, your Paste To range need only be defined as one cell.

Sub CopyUniqueEntries()
Dim LastRow As Long

LastRow = Cells(Rows.Count, "B").End(xlUp).Row 'last data cell in col B
Range("B2:B" & LastRow).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"A2"), Unique:=True
Range("A2").Delete 'removes unwanted header
End Sub

Mike F
"Jumparound" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I wish to copy a list of entries to a new sheet.
> If i use the code below it first copy's cell "b2" and then from the
> last cell to the first.
> Now i have a double entry.
>
> I want it to end at cell b2, because the first cell has collum
> headings in it!
>
> How do i write the code so that cell b2 is not copied first?
>
> kind regards
>
> Sjoerd
>
>
> this is my code:
>
> Sub CopyUniqueEntries()
>
> Dim uniquelist As range
>
> Set uniquelist = ActiveSheet.range("A2", "A10000")
>
> Sheet1.range("b2",
> Sheet1.range("b65536").End(xlUp)).AdvancedFilter _
> Action:=xlFilterCopy, CopyToRange:=uniquelist.Cells(1, 1),
> Unique:=True
>
> End Sub
>

Watch the word wrap on line 2.

Mike F
"Mike Fogleman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Advanced Filter has a requirement that the list have a header, that the
> header be included in the list range, and the header will be pasted along
> with the unique list. Since you start the range at B2, The Filter assumes
> that is the header and copies it, and then the unique list. This why B2 is
> copied twice. If you start your Range at B1 to include the header, then B2
> will not be duplicated, but the header will be pasted in A2. Either way
> you define your Filter range, the entry in A2 is unwanted and needs to be
> deleted after the paste.
> Also, your Paste To range need only be defined as one cell.
>
> Sub CopyUniqueEntries()
> Dim LastRow As Long
>
> LastRow = Cells(Rows.Count, "B").End(xlUp).Row 'last data cell in col B
> Range("B2:B" & LastRow).AdvancedFilter Action:=xlFilterCopy,
> CopyToRange:=Range( _
> "A2"), Unique:=True
> Range("A2").Delete 'removes unwanted header
> End Sub
>
> Mike F
> "Jumparound" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>I wish to copy a list of entries to a new sheet.
>> If i use the code below it first copy's cell "b2" and then from the
>> last cell to the first.
>> Now i have a double entry.
>>
>> I want it to end at cell b2, because the first cell has collum
>> headings in it!
>>
>> How do i write the code so that cell b2 is not copied first?
>>
>> kind regards
>>
>> Sjoerd
>>
>>
>> this is my code:
>>
>> Sub CopyUniqueEntries()
>>
>> Dim uniquelist As range
>>
>> Set uniquelist = ActiveSheet.range("A2", "A10000")
>>
>> Sheet1.range("b2",
>> Sheet1.range("b65536").End(xlUp)).AdvancedFilter _
>> Action:=xlFilterCopy, CopyToRange:=uniquelist.Cells(1, 1),
>> Unique:=True
>>
>> End Sub
>>
>
>

Include A1 (with the real headers) in your unique list.
Put the advanced filter in B1 (that's where the header will be)
Your unique list will be in B2:B###

If you don't want the header in column B, then delete that cell (not the row)
and shift things up.

Jumparound wrote:
>
> I wish to copy a list of entries to a new sheet.
> If i use the code below it first copy's cell "b2" and then from the
> last cell to the first.
> Now i have a double entry.
>
> I want it to end at cell b2, because the first cell has collum
> headings in it!
>
> How do i write the code so that cell b2 is not copied first?
>
> kind regards
>
> Sjoerd
>
> this is my code:
>
> Sub CopyUniqueEntries()
>
> Dim uniquelist As range
>
> Set uniquelist = ActiveSheet.range("A2", "A10000")
>
> Sheet1.range("b2",
> Sheet1.range("b65536").End(xlUp)).AdvancedFilter _
> Action:=xlFilterCopy, CopyToRange:=uniquelist.Cells(1, 1),
> Unique:=True
>
> End Sub

--

Dave Peterson

On 3 sep, 14:50, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Include A1 (with the real headers) in your unique list.
> Put the advanced filter in B1 (that's where the header will be)
> Your unique list will be in B2:B###
>
> If you don't want the header in column B, then delete that cell (not the row)
> and shift things up.
>
>
>
>
>
> Jumparoundwrote:
>
> > I wish to copy a list of entries to a new sheet.
> > If i use the code below it first copy's cell "b2" and then from the
> > last cell to the first.
> > Now i have a double entry.
>
> > I want it to end at cell b2, because the first cell has collum
> > headings in it!
>
> > How do i write the code so that cell b2 is not copied first?
>
> > kind regards
>
> > Sjoerd
>
> > this is my code:
>
> > Sub CopyUniqueEntries()
>
> > Dim uniquelist As range
>
> > Set uniquelist = ActiveSheet.range("A2", "A10000")
>
> > Sheet1.range("b2",
> > Sheet1.range("b65536").End(xlUp)).AdvancedFilter _
> > Action:=xlFilterCopy, CopyToRange:=uniquelist.Cells(1, 1),
> > Unique:=True
>
> > End Sub
>
> --
>
> Dave Peterson- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

Tnx for the answers guys!

i figured i could delete the first entry right after copying, so i
made a makro to delete fhe first row aftert his one
but ill try to use your answers too!

I'm not sure I understand.

But if you meant that you're not going to add headers and instead just delete
that first row (which would have been treated like a header), you may want to be
careful.

If that's the only entry that appears in the original list, you'd be deleting
one of the unique values.

I'd either add the headers first, do the advanced filter, then delete the
headers.

Or check to see if that "header" appears in the list further down before
deleting it.

Jumparound wrote:
<<snipped>>
>
> i figured i could delete the first entry right after copying, so i
> made a makro to delete fhe first row aftert his one
> but ill try to use your answers too!

--

Dave Peterson

On 4 sep, 17:29, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> I'm not sure I understand.
>
> But if you meant that you're not going to add headers and instead just delete
> that first row (which would have been treated like a header), you may want to be
> careful.
>
> If that's the only entry that appears in the original list, you'd be deleting
> one of the unique values.
>
> I'd either add the headers first, do the advanced filter, then delete the
> headers.
>
> Or check to see if that "header" appears in the list further down before
> deleting it.
>
> Jumparound wrote:
>
> <<snipped>>
>
>
>
> > i figured i could delete the first entry right after copying, so i
> > made a makro to delete fhe first row aftert his one
> > but ill try to use your answers too!
>
> --
>
> Dave Peterson

Oke, this is what happends...

information get exported to excel
My macro kicks in.
I copy unique values from Sheet1.B:B to Sheet2.A2 (this includes the
header.)
Then i delete first row (Sheet2.A2 -> the header from Sheet1.B1)
Then i clear empty spaces
Then i sort on name
"rest of the code executes"

This i a working way, have tested it on several documents.
Now im ready to try something else.

Sub CopyUniqueEntries()
Dim LastRow As Long


LastRow = Cells(Rows.Count, "B").End(xlUp).Row 'last data cell in col
B
Range("B2:B" & LastRow).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"A2"), Unique:=True
Range("A2").Delete 'removes unwanted header
End Sub

This look indeed like the code i need to use, now im ready to try it.