Consolidating multiple worksheets into one and retain links to source data in master sheet

Hi everyone,

I have 50+ spreadsheets, with the same column headings and # of columns, as well as the tab names (i.e., *Report* is the commonality). Every week after analyzing a particular report, I name that tab "[insert week] Report." Afterwards, I want to run a code that consolidates ALL of my tabs with the *Report* into one single spreadsheet. However, rather than showing the data as hard coded values when running the VBA Code, I'd like to show the source data too. So for example, my first report is dated 1/1/2011, which means on the consolidated master sheet, the 1/1/2011 values should be "='1-1-2011 Report'!Cell Position" in the formula bar, instead of showing the paste special values. This way I can evaluate what changes I need to make in the consolidated master sheet and be able to click on that cell directly to make the change. If I need to make a change to the 1/1/2011 tab, then the consolidated master sheet will be automatically updated to reflect the change.

See below for a VBA code that I found that does part of what I'm looking for. I've already modified the data ranges in the code based on what I want the master tab to copy and paste from. The issue with this code is it only copies and paste special values when the code is generated. I don't have insight on the source data of the values on the consolidated master sheet.

Any suggestion is greatly appreciated! Thanks so much!

Option Explicit
 
Sub ConsolidateSheets()
 
 
Dim cs As Worksheet, ws As Worksheet, LR As Long, NR As Long
 
If Not Evaluate("ISREF(Consolidate!b1)") Then _
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Consolidate"
 
Set cs = Sheets("Consolidate")
cs.Cells.ClearContents
NR = 1
 
For Each ws In Worksheets
If LCase(ws.Name) Like "*report*" Then
ws.Activate
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("a10:AB" & LR).Copy
cs.Range("A" & NR).PasteSpecial xlPasteFormulas
cs.Range("A" & NR).Formula = Range("a10:AB" & LR).Formula
Application.CutCopyMode = False
NR = cs.Range("B" & Rows.Count).End(xlUp).Row + 1
End If
Next ws
 
cs.Activate
Range("B2").Select
End Sub

Moderator Edit:

Welcome to the forum.

Please notice that code tags have been added to your post. The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at the top of the page and read Rule #3.
Thanks.