Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #10  
Old 02-24-2019, 04:16 PM
NoSparks NoSparks is offline Copy data Windows 7 64bit Copy data 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
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy data as well as formatting from one workbook to another. LearnerExcel Excel 1 03-10-2018 01:35 PM
Copy excel data wjmj Word 1 12-18-2015 10:10 PM
a macro that can copy data from copy.xls to our current excel macro.xls based on criteria: udhaya Excel Programming 1 11-12-2015 10:12 AM
Copy Like Data MBragg Excel 3 10-13-2015 11:45 PM
Copy data from one form to another jodjamz Outlook 0 04-28-2015 05:07 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:46 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft