VBA Paste in next free cell

[tcorcoran15]

Dim R As Range, Rng As Range

[FONT=Verdana]Sheets("CE List").Select[/FONT]


[FONT=Verdana]'Removes any duplicates[/FONT]
[FONT=Verdana]ActiveSheet.Range("$A$1:$A$5000").RemoveDuplicates Columns:=1, Header:=xlNo[/FONT]


[FONT=Verdana]With Sheets("CE List")[/FONT]
[FONT=Verdana]Set Rng = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)[/FONT]
[FONT=Verdana]For Each R In Rng[/FONT]
[FONT=Verdana]'Copies name from top of CE List into Statement tab B2[/FONT]
[FONT=Verdana]R.Copy Sheets("Statement").Range("B2")[/FONT]


[FONT=Verdana]'Copies Statement File into Export File as paste value[/FONT]
[FONT=Verdana]Sheets("Statement").Range("A1:I500").Copy[/FONT]
[FONT=Verdana]With Sheets("EXPORT")[/FONT]
[FONT=Verdana].Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues[/FONT]
[FONT=Verdana]End With[/FONT]
[FONT=Verdana]Next R[/FONT]


[FONT=Verdana]'If you don't want to delete all the names, then comment out the next line[/FONT]
[FONT=Verdana]Rng.EntireRow.Delete[/FONT]
[FONT=Verdana]End With[/FONT]

[FONT=Verdana]Sheets("EXPORT").Select[/FONT]
[FONT=Verdana]Rows("1:1").Select[/FONT]
[FONT=Verdana]Selection.Delete Shift:=xlUp[/FONT]

I have the above code which I would like to change slightly.

The '.Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues' part pastes the data into the next free cell in A1.

I would still like it to paste into Column A, however I would like to change it to look at the column F to see where the next available cell is?

Any help greatly appreciated.

 

[Fluff]

Try

.Range("F" & .Rows.Count).End(xlUp).Offset(1, -5).PasteSpecial Paste:=xlPasteValues

 

[tcorcoran15]

Works great, thanks for your help!

 

[Fluff]

Glad to help & thanks for the feedback