I am using the below code:
Dim LastRow As Integer, i As Integer, erow As Integer
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 2) = "1" Then
' As opposed to selecting the cells, this will copy them directly
Range(Cells(i, 1), Cells(i, 26)).Copy
' As opposed to "Activating" the workbook, and selecting the sheet, this will paste the cells directly
With Workbooks("Swivel - Master - January 2016.xlsm").Sheets("Swivel")
erow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(erow, 1).PasteSpecial xlPasteAll
End With
Application.CutCopyMode = False
End If
Next I
This works as it is suppose to, but I need to constrain the range that is pastes to. When this code is run, it copies the range of A2:Z2 (sample range for this question, it actually copies more rows than this), but it pastes to cells beyond column Z. I am most concerned with column AD as there is code to change the text of that row green when there is a value inserted into that column. After the copy/paste code is run, the row changes to green text, even though there is nothing in AD. Here is the code that changes the text to green in the rows (this code is in the Sheet1 object of the workbook).
Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim r As Range
Set r = Target.EntireRow
If Target.row = 1 Then Exit Sub ' Don't change header color
If r.Cells(1, "AD").Value <> "" Then
r.Font.Color = RGB(0, 176, 80)
Else
r.Font.ColorIndex = 1
End If
End Sub
Now, in column AD of a row that we complete the work on, we insert a date and time in this format: 1/4/2016 13:20. Can I change this line:
If r.Cells(1, "AD").Value <> "" Then
to check for the format rather than the value?
I am still learning VBA, but know that I have a lot to learn. Any assistance is appreciated.
EDIT: The anomaly does not occur until after this code is run (which is located in the target workbook "Swivel"):
Sub Remove_Duplicates()
'
Application.ScreenUpdating = False
ActiveSheet.Range("$A$1:$Z$2000").RemoveDuplicates Columns:=Array(10, 11, 12, 13, 14, 15, 16), Header:=xlYes
ActiveWindow.SmallScroll Down:=6
Range("C" & Rows.Count).End(xlUp).Offset(1).Select
Application.ScreenUpdating = True
End Sub
This changes the text to the green color, but there is nothing in column AD of that row to trigger the change.
Instead of trying to resolve this in the Worksheet_Change sub, I modified the Remove_Duplicates sub to this:
Sub Remove_Duplicates()
'
Application.ScreenUpdating = False
Dim usedrng As Range
ActiveSheet.Range("$A$1:$Z$2000").RemoveDuplicates Columns:=Array(5, 10, 11, 12, 13, 14, 15, 16), Header:=xlYes
For Each usedrng In ActiveSheet.UsedRange
If usedrng.Value = "" Then
usedrng.ClearContents
End If
Next
Range("C" & Rows.Count).End(xlUp).Offset(1).Select
Application.ScreenUpdating = True
End Sub
This has removed the fake empty values that I was contending with effectively. The WorkSheet_Change sub now works as it is written in the initial question above and the worksheet behaves in the manner it should when new rows of data are added, which is that the text should remain black until a date/time entry is made in column AD for that row.
Thanks to all those who offered their assistance. I hope this answer helps others as well.