![]() |
|
|
|
#1
|
|||
|
|||
|
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
|
|
| 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 |