excelquestion8
New Member
- Joined
- Feb 9, 2021
- Messages
- 2
- Office Version
- 2016
- Platform
- Windows
Hi, I would like to only copy and paste columns A - L into another tab instead of the whole row. I would like to prevent overwriting data in columns after L. Here is my code:
VBA Code:
Sub CopyName()
Dim Check As Range, r As Long, lastrow2 As Long, lastrow As Long, lastrow3 As Long, lastrow4 As Long
Application.ScreenUpdating = False
lastrow = Worksheets("Assignments").UsedRange.Rows.Count
lastrow2 = Worksheets("name1").UsedRange.Rows.Count
lastrow3 = Worksheets("name2").UsedRange.Rows.Count
lastrow4 = Worksheets("name3").UsedRange.Rows.Count
If lastrow2 = 1 Then lastrow2 = 0
For r = lastrow To 2 Step -1
If Range("E" & r).Value = "name1" Then
Rows(r).Copy Destination:=Worksheets("name1").Range("A" & lastrow2 + 2)
lastrow2 = lastrow2 + 1
Else:
End If
Next r
If lastrow3 = 1 Then lastrow3 = 0
For r = lastrow To 2 Step -1
If Range("E" & r).Value = "name2" Then
Rows(r).Copy Destination:=Worksheets("name2").Range("A" & lastrow3 + 2)
lastrow3 = lastrow3 + 1
Else:
End If
Next r
If lastrow4 = 1 Then lastrow4 = 0
For r = lastrow To 2 Step -1
If Range("E" & r).Value = "name3" Then
Rows(r).Copy Destination:=Worksheets("name3").Range("A" & lastrow4 + 2)
lastrow4 = lastrow4 + 1
Else:
End If
Next r
Application.ScreenUpdating = True
End Sub