Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
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
Reply

Thread Tools
Display Modes


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 02:12 PM.


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