I added an OR statement to remove the lines with column Y containing a
STRIKETHROUGH. Make sure there isn't any conditional formating in column J
that is producing the strikeout. The code below will not remove the lines
with the strikeout if the strinkout is being generated by conditional
formating.

Sub CommandButton1_Click()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim myrange, MyRange1 As Range
Application.ScreenUpdating = False

Set WS1 = Worksheets("Stage Forecast")
Set WS2 = Worksheets("Training Dashboard")

With WS1

LastRow = .Cells(Rows.Count, "J").End(xlUp).Row
NewRow = LastRow + 1
.Range("K1:K330").Copy WS2.Cells(NewRow, "D")
.Range("L1:L330").Copy WS2.Cells(NewRow, "E")
.Range("M1:M330").Copy WS2.Cells(NewRow, "F")
.Range("N1:N330").Copy WS2.Cells(NewRow, "G")
.Range("O1:O330").Copy WS2.Cells(NewRow, "H")
.Range("Q1:Q330").Copy WS2.Cells(NewRow, "I")
.Range("Y1:Y330").Copy WS2.Cells(NewRow, "J")

Set myrange = WS2.Range("J3:J" & LastRow)
For Each C In myrange
If C.Value = "" Or _
C.Font.Strikethrough = True Then

If MyRange1 Is Nothing Then
Set MyRange1 = C.EntireRow
Else
Set MyRange1 = Union(MyRange1, C.EntireRow)
End If
End If
Next

If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End With
End Sub


"huge_ness" wrote:

Hi Joel,

Thanks for the reply. I ran your code, however I am facing a similar issue
as I once did during testing. When I run your code, all rows are shown.
However, in doing so I realized that cells that are being carried over are
not missing, there seems to be a formatting error that makes all dates in the
Y column in question: crossed out.

With that, we wouldn't be able to classify what is still active and what is
not.
I would like the J column to show only those rows in which the Y column of
"Stage Forecast" are valid dates that are still active, thus not crossed out.

Also, when I'm using your code, it updates the field by adding all the rows
on top of one another, so I get duplicated of the data.

Thanks,
Mark

"Joel" wrote:

I think the cause of the problem is the LastRow statement the "CELLS" didn't
specify a sheet and may of been refereing to the wrong sheet to get the last
row. Try these changes. Your code was also over-writing the last row of
data using just XLUP. You want to put new data in the next row after the
XLUP. I assumed all the columns had data in the last row and used column J
to determine the last row.


Sub CommandButton1_Click()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim myrange, MyRange1 As Range
Application.ScreenUpdating = False

Set WS1 = Worksheets("Stage Forecast")
Set WS2 = Worksheets("Training Dashboard")

with WS1

LastRow = .Cells(Rows.Count, "J").End(xlUp).Row
NewRow = LastRow + 1
.Range("K1:K330").Copy WS2.Cells(NewRow, "D")
.Range("L1:L330").Copy WS2.Cells(NewRow, "E")
.Range("M1:M330").Copy WS2.Cells(NewRow, "F")
.Range("N1:N330").Copy WS2.Cells(NewRow, "G")
.Range("O1:O330").Copy WS2.Cells(NewRow, "H")
.Range("Q1:Q330").Copy WS2.Cells(NewRow, "I")
.Range("Y1:Y330").Copy WS2.Cells(NewRow, "J")

Set myrange = WS2.Range("J3:J" & LastRow)
For Each c In myrange
If UCase(c.Value) = "" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next

If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
end with
End Sub


"huge_ness" wrote:

Hi,

I am trying to copy column values from the worksheet entitled "Stage
Forecast" to another worksheet called "Training Dashboard". I have written up
most of the code however during the transfer, not all the data is copying
over. As you can see from the code, I am mainly concerned with data that is
found in column J of the "Training Dashboard" worksheet. If column Y of
"Stage Forecast" has any cells that are empty, I do not display its row.

Also within column Y of "Stage Forecast", it is a date field that is either
shown underlined, or crossed out (meaning completed). I am only receiving
data that is crossed out using this code. I need to alter what is shown below
so that the data that is transmitted is only those rows in which row Y has a
date that is simply underlined.

Secondly as a secondary piece of code: I need my second worksheet "Training
Dashboard" to not show duplicate information once I click the the command
button to update the sheet.

Any help would be greaty appreciated,

Thanks.

VB code:

Sub CommandButton1_Click()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim myrange, MyRange1 As Range
Application.ScreenUpdating = False

Set WS1 = Worksheets("Stage Forecast")
Set WS2 = Worksheets("Training Dashboard")

WS1.Range("K1:K330").Copy WS2.Cells(Rows.Count, "D").End(xlUp)
WS1.Range("L1:L330").Copy WS2.Cells(Rows.Count, "E").End(xlUp)
WS1.Range("M1:M330").Copy WS2.Cells(Rows.Count, "F").End(xlUp)
WS1.Range("N1:N330").Copy WS2.Cells(Rows.Count, "G").End(xlUp)
WS1.Range("O1:O330").Copy WS2.Cells(Rows.Count, "H").End(xlUp)
WS1.Range("Q1:Q330").Copy WS2.Cells(Rows.Count, "I").End(xlUp)
WS1.Range("Y1:Y330").Copy WS2.Cells(Rows.Count, "J").End(xlUp)

LastRow = Cells(Rows.Count, "J").End(xlUp).Row
Set myrange = Sheets("Training Dashboard").Range("J3:J" & LastRow)
For Each c In myrange
If UCase(c.Value) = "" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If

End Sub