kelly mort
Well-known Member
- Joined
- Apr 10, 2017
- Messages
- 2,169
- Office Version
- 2016
- Platform
- Windows
Hello,
I have this code that I am trying to use to copy some data from one sheet to another. It is copying though yet the final output is not something that I am happy with.
At the time of copying, "ITEM 6" will be the below all the other items on the data1 sheet – I am sorting with the items column (col C).
After the copy, only the last "ITEM 6" member from data1 sheet appears for all the "ITEM 6" records.
Is there a problem with this code and or how best can I copy the said data to the data2 sheet?
Thanks in advance.
I have this code that I am trying to use to copy some data from one sheet to another. It is copying though yet the final output is not something that I am happy with.
At the time of copying, "ITEM 6" will be the below all the other items on the data1 sheet – I am sorting with the items column (col C).
After the copy, only the last "ITEM 6" member from data1 sheet appears for all the "ITEM 6" records.
Is there a problem with this code and or how best can I copy the said data to the data2 sheet?
Thanks in advance.
Code:
Dim e&, ls&, lr&, MyCells As Range
Dim T1 As Worksheet, S1 As Worksheet
Set T1 = Sheets(“Data1”)
Set S1 = Sheets(“Data2”)
e = 0
lr = T1.Range("A" & Rows.Count).End(xlUp).Row
If lr < 7 Then lr = 7
For Each MyCells In T1.Range("C7:C" & lr)
If MyCells = "ITEM 6" Then
T1.Range("A" & lr & ":U" & lr).Copy S1.Cells(Rows.Count, "A").End(xlUp).Offset(1)
T1.Range("A" & MyCells.Row & ":U" & MyCells.Row).ClearContents
ls = S1.Range("A" & Rows.Count).End(xlUp).Row
If ls < 7 Then ls = 7
With S1.Range("A7:A" & ls)
Do
e = e + 1
Loop Until IsError(Application.Match(e, S1.Cells, 0))
End With
S1.Cells(ls, "A") = e
End If
Next MyCells