Consolidate - edit code?

S

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!

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
 
F

fanpages

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 ]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top