Paste Values When Creating A Master Summary Sheet

sbox134 · Apr 8th 2019

[SIZE=12px]Hello!
I'm trying to create a master summary sheet that will summarize information that is on a number of other sheets in the workbook. I was able to find some code by searching through the forum to get me 95% of the way there, but I've got one problem. The code that I have below, but the modification I'd like help with is having it paste values instead. Currently it carries over the formulas that are in the cells on each respective tab and my master sheet shows all errors. Any help is greatly appreciated![/SIZE]

Code

Sub SumSheets()
Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Summary" And sh.Name <> "Sheet1" And sh.Name <> "Sheet2" Then
            sh.Range("F17:F66").Copy Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next


End Sub

Maqbool · Apr 8th 2019

Hi

Try the code below:

Code

Sub SumSheets()


Sheets("Summary").Range("A2:A1000").ClearContents  ' clear everything in summary before pasting


Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Summary" And sh.Name <> "Sheet1" And sh.Name <> "Sheet2" Then
        
            sh.Range("F17:F66").Copy
            Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlValues
        End If
    Next


End Sub

Display More

Regards

Maqbool

**KjBox** · Apr 8th 2019

Try

Code

Sub SumSheets()
    Dim sh As Worksheet
    
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Summary" And sh.Name <> "Sheet1" And sh.Name <> "Sheet2" Then
            sh.Range("F17:F66").Copy Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
    With Sheets("Summary").UsedRange
        .Value = .Value
    End With
    
End Sub

Display More

sbox134 · Apr 8th 2019

Thank you Maqbool and KjBox for the help. I couldn't get the solution that KjBox provided to work, but the solution provided by Maqbool worked perfect. Thanks again for taking the time to help me out!!

Paste Values When Creating A Master Summary Sheet

Participate now!

Share