Copy named ranges from multiple sheets to summary sheet

[DYB]

Hi

I have a workbook that contains multiple worksheets some of which have named ranges that I want to copy into a sheet called 'Combined'. I have created some simple VBA code that does that but it copies the source formulas into the destination cells rather than the values. Checking through some forums online I found some code using '.value' which did convert the formulas to values in the destination cells, but unfortunately it also converted the formulas in the source cells to values too.

In simple terms, I need the code to copy the values from the named ranges and paste into the 'Combined' worksheet, leaving the source formula in the named ranges unchanged. The code I have at the moment is below:

Sub Summary()
Dim n

'Merges worksheet data using named ranges

Sheets("Combined").Select
Range("B2:P3000").ClearContents
For Each n In Array("BRF", "CSP", "MAN", "OPS", "REC", "TRNG")
Range ( n).Copy Sheets("Combined").Range("B" & Rows.Count).End(xlUp).Offset(1)

Next

End Sub


Any help would be much appreciated.

 

[DanteAmor]

Try this:

Sub Summary()
  Dim n
  Application.ScreenUpdating = False
  'Merges worksheet data using named ranges
  With Sheets("Combined")
    .Range("B2:P3000").ClearContents
    For Each n In Array("BRF", "CSP", "MAN", "OPS", "REC", "TRNG")
      Range(n).Copy
      .Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    Next
  End With
End Sub

-----------------------
Option with Values:

Sub Summary_2()
  Dim n
  'Merges worksheet data using named ranges
  With Sheets("Combined")
    .Range("B2:P3000").ClearContents
    For Each n In Array("BRF", "CSP", "MAN", "OPS", "REC", "TRNG")
      .Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(Range(n).Rows.Count, Range(n).Columns.Count).Value = Range(n).Value
    Next
  End With
End Sub

 

[DYB]

Both of those work perfectly - thank you so much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.