Place Data in last row

[jayjay3489]

Hi

I know this code is going to be quite simple but everyhtign i have tried just doesnt work.

I need the data to be added to the end of a sheet not in the A1 as it always does and overwrites previous data.

The code filters the data on "Input" sheet and adds to it to the sheet name given from a column in the input sheet. When i add new data to the input page i want it to go to the end of the sheet its destined and then add the new information.

Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 4
Set ws = Sheets("Input")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:m1"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate

Sheet2.Cells.Clear
Sheet1.Activate

 

[Stubble Kid]

It looks like you need to change the line ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1") as this is copying to row A1 everytime.

Suggest the following:

ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1048576").End(xlUp).Offset(1 + (i = 2))

This will position the copying to the next available row in column A, except for the very first For/Next loop when it should copy to A1.
[Tech note: "(i=2)" returns TRUE or FALSE which in a numerical situation becomes "-1" or "0" - saves a more convoluted IF/THEN]