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

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

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

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

The problem is that from the Source worksheet ("Stage Forecast"), all the
data is maintained through conditional formatting. Therefore the or statement
would not affect it as you mentioned previously.

Is there a way to read the cells contents with conditional fomatting?

"Joel" wrote:

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

Chip Pearson has an article discussing conditional formating

http://www.cpearson.com/Excel/CFColors.htm

It is complicated to test if the conditional formating is causing the strike
through. It is simplier to duplicate the formula that is producing the
strikeout in VBA code. If the strikeout is testing if a date is older than 6
months than simply put in the VBA code a test for 6 months rahter than
looking at the conditional format inside the cell to determine if a stikeout
has occured.

"huge_ness" wrote:

The problem is that from the Source worksheet ("Stage Forecast"), all the
data is maintained through conditional formatting. Therefore the or statement
would not affect it as you mentioned previously.

Is there a way to read the cells contents with conditional fomatting?

"Joel" wrote:

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