Thread: Copy data
View Single Post
 
Old 02-24-2019, 04:16 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

Another possibility would be to use a column off to the right somewhere to keep track of what has already been copied.
This 'helper' column could be hidden if desired. Have used column J here.
Code:
Sub CopyOwnTab()

Dim i As Long, Lastrow As Long, ans As String
Dim dest As Worksheet, recCnt As Long

With Sheets("List")
    Lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
        For i = 3 To Lastrow
            If LCase(.Cells(i, "J").Value) <> "copied already" Then
                ans = .Cells(i, 2).Value
                On Error Resume Next    'incase the sheet does not exist
                Set dest = Sheets(ans)
                On Error GoTo 0         're-enable error notification
                If Not dest Is Nothing Then
                    .Cells(i, "A").Resize(, 7).Copy dest.Range("A" & Rows.Count).End(xlUp).Offset(1)
                    .Cells(i, "J").Value = "copied already"
                    recCnt = recCnt + 1
                Else
                    MsgBox "Sheet " & ans & " does not exist."
                End If
            End If
            Set dest = Nothing
        Next
End With
MsgBox "There were " & recCnt & " records copied."
End Sub
Reply With Quote