Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 .. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste and Paste Special No Longer Working - Excel 2003 | Excel Discussion (Misc queries) | |||
In Excel: add a Paste-Special Option to paste IN REVERSE ORDER. | Excel Worksheet Functions | |||
Modify this code to paste special, values | Excel Programming | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming | |||
Copy Paste Special Value using Code over Several Worksheets | Excel Programming |