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