Code for Paste Special Values

[Tom Taetsch]

I am trying to change some code from:

.EntireRow.Copy Sheets("Monthly
Average").Cells(aLastRow, "A")

-TO-

something that will not copy and paste, but will copy and
paste special values into "Monthly Average" sheet.
Everything works fine except when it copys and pastes,
because of formulas in the "Mon" sheet.

I have attached the code so that you can see what I have
written so far.


Dim iLastRow1 As Long
Dim aLastRow As Long
Dim i As Integer
Dim j As Integer
Dim rw As Long, iCol As Integer

'Determine last row in Mon activity sheet with a "clock
hours"

iLastRow1 = Sheets("Mon").Cells(Rows.Count, "V").End
(xlUp).Row
aLastRow = Sheets("Monthly Average").Cells
(Rows.Count, "B").End(xlUp).Row + 1

Application.ScreenUpdating = False

'////////// Mon ////////////
' Copy and paste each row with a "clock hours" into the
archive file

For i = iLastRow1 To 26 Step -1
With Sheets("Mon").Cells(i, "V")
If .Value <> "" Then
.EntireRow.Copy Sheets("Monthly
Average").Cells(aLastRow, "A")
End If
End With
aLastRow = aLastRow + 1
Next

' Erase empty rows from daily archive sheet

Sheets("Monthly Average").Select
Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Application.ScreenUpdating = True


Thanks for the help,
Tom

 

[Don Guillett]

Without too much looking and no testing try putting your alast row within
the for and eliminating
aLastRow = aLastRow + 1

 

[Guest]

Don,
Forgive me for not completely understanding what you
wrote... Can you explain it alittle clearer?
Thanks,
Tom

 

[Don Guillett]

iLastRow1 = Sheets("Mon").Cells

(Rows.Count, "V").End

aLastRow = Sheets("Monthly Average"). _
Cells(Rows.Count, "B").End(xlUp).Row + 1