Code Assistance (Paste Special)
 

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
..

Code Assistance (Paste Special)
 

If .Value < "" Then
.EntireRow.Copy _
Sheets("Monthly Average").Cells(aLastRow, "A")
End If

does a copy|paste.

If .Value < "" Then
.EntireRow.Copy
Sheets("Monthly Average").Cells(aLastRow, "A").PasteSpecial _
Paste:=xlPasteValues
End If


Tom Taetsch wrote:

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
.

--

Dave Peterson