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

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 ]