Macro to Combine Data from Multiple Sheets with Cell References

[djc]

Hello all,
I am working on a compilation issue. I have 50+ plus sheets used for data entry. Every sheet has the same headers in the range A18:AE18. The data gets entered in the rows below the header and each sheet has varying number of rows.

I have another sheet named “Master” that needs to roll up all the data from the other tabs.

I am in need of a macro that will go find the data range in each sheet and return the link to those cells, not just the number and return the links (cell reference) to the Master sheet; (each sheet is a quasi-master sheet andedits need to happen in these sheets). Each sheet that needs to roll up is named with a “J-“ as the 2 left characters for easy identification.

Claus Busch provided a nifty little macro for me a few days ago (thank you again Claus!), which worked beautifully for my initial project, however, now I am in need to expand it to include the links. Below is his macro

Sub Test()
Dim LRow As Long
Dim wsh As Worksheet

For Each wsh In ThisWorkbook.Worksheets
If Left(wsh.Name, 2) = "J-" Then
With wsh
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A5:AE" & LRow).Copy _
Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
End If
Next
End Sub

Unfortunately, I cannot just clear the data and rerun the macro when edits are made since the Master it tied to other sheets.

Any feedback would be fantastic. Thank you in advance.

 

[Claus Busch]

Hi,

Am Mon, 21 Oct 2013 15:53:20 -0700 (PDT) schrieb djc:

I am in need of a macro that will go find the data range in each sheet and return the link to those cells, not just the number and return the links (cell reference) to the Master sheet; (each sheet is a quasi-master sheet and edits need to happen in these sheets). Each sheet that needs to roll up is named with a ?J-? as the 2 left characters for easy identification.

if you need hyperlinks in the "Master" sheet to go to the sheets and
ranges with the data then try:

Sub Test()
Dim LRow As Long
Dim wsh As Worksheet

For Each wsh In ThisWorkbook.Worksheets
If Left(wsh.Name, 2) = "J-" Then
With wsh
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Sheets("Master").Activate
ActiveSheet.Hyperlinks.Add _
anchor:=Cells(Rows.Count, 1).End(xlUp).Offset(1, 0), _
Address:="", _
SubAddress:="'" & wsh.Name & "'!" & _
Range(Cells(19, "A"), Cells(LRow, "AE")).Address
End With
End If
Next
End Sub


Regards
Claus B.

 

[Claus Busch]

Hi,

Am Mon, 21 Oct 2013 15:53:20 -0700 (PDT) schrieb djc:

Unfortunately, I cannot just clear the data and rerun the macro when edits are made since the Master it tied to other sheets.

after reading your post again I think I misunderstood your problem
first.
Try instead:

Sub Test()
Dim LRow As Long
Dim wsh As Worksheet

For Each wsh In ThisWorkbook.Worksheets
If Left(wsh.Name, 2) = "J-" Then
With wsh
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A19:AE" & LRow).Copy
Sheets("Master").Activate
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
ActiveSheet.Paste link:=True
End With
End If
Next
End Sub


Regards
Claus B.

 

[djc]

The second one did the trick! I should make sure I don't confuse "links" and "references" in future posts. I'm glad you were able interpret my problem. Thank you Claus!

 

[Claus Busch]

Hi,

Am Tue, 22 Oct 2013 07:39:22 -0700 (PDT) schrieb djc:

The second one did the trick!

I am always glad to help. Thank you for the feedback.


Regards
Claus B.