Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate - edit code?
Hello everyone. I have some code that copies the contents of every sheet
within a workbook and pastes it to a single sheet one under the other in a sheet called "Upload". Is there a way to modify this code to paste values instead of paste? Thanks! Sub Consolidate() Dim ws As Worksheet Dim DestSh As Worksheet Dim shLast As Long Dim Last As Long Application.ScreenUpdating = False Set DestSh = Worksheets("Upload") For Each ws In Worksheets If ws.Name < DestSh.Name And ws.Name < "Total Signal" And ws.Name < "Upload" Then Last = LastRow(DestSh) shLast = LastRow(ws) ws.Range(ws.Rows(1), ws.Rows(shLast)).Copy DestSh.Cells(Last + 1, 1) End If Next Application.ScreenUpdating = True End Sub Function LastRow(ws As Worksheet) On Error Resume Next LastRow = ws.Cells.Find(What:="*", _ After:=ws.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate - edit code?
Hi Steph,
Hello everyone. I have some code that copies the contents of every sheet within a workbook and pastes it to a single sheet one under the other in a sheet called "Upload". Is there a way to modify this code to paste values instead of paste? Thanks! Change: s.Range(ws.Rows(1), ws.Rows(shLast)).Copy DestSh.Cells(Last + 1, 1) To read: s.Range(ws.Rows(1), ws.Rows(shLast)).Copy DestSh.Cells(Last + 1, 1).PasteSpecial xlPasteValues BFN, fp. [ http://www.experts-exchange.com/M_258171.html ] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clean Up Code - consolidate steps | Excel Discussion (Misc queries) | |||
How to edit VB code programmatically | Excel Programming | |||
Can I edit .iqy file from code? | Excel Programming | |||
Code Edit | Excel Programming | |||
Emailing Sheets Code Edit | Excel Programming |