How to paste values when creating a master summary sheet
Category: [Excel] Demo Available
How to paste values when creating a master summary sheet
Requirement:
The user is trying to create a master summary sheet that will summarize information that is on a number of other sheets in the workbook.
The user is working with the following code but would like it to paste values instead. Currently it carries over the formulas that are in the cells on each respective tab and the master sheet is displaying errors
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
Solution:
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
Obtained from the OzGrid Help Forum.
Solution provided by maqbool.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to search for a word inside a workbook and open that sheet as active sheet |
| How to compare two workbooks with multiple sheets and highlighting duplicates |
| How to reference text in cell to unhide worksheet |
| How to list & display all files in user folder, select file and copy specific tab into master sheet |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions. cssboxshadow.com