Copy named ranges from multiple sheets to summary sheet

DYB

New Member
Joined
Jan 12, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,248
Office Version
  1. 2013
Platform
  1. Windows
Try this:

VBA Code:
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:

VBA Code:
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
 
Solution

DYB

New Member
Joined
Jan 12, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Both of those work perfectly - thank you so much!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,248
Office Version
  1. 2013
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,176,201
Messages
5,901,890
Members
434,928
Latest member
mvbuccheri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top