Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Combine Data from Multiple Sheets with Cell References
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 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. 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Combine Data from Multiple Sheets with Cell References
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Combine Data from Multiple Sheets with Cell References
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Combine Data from Multiple Sheets with Cell References
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!
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Combine Data from Multiple Sheets with Cell References
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i combine data from multiple sheets into one sheet? | Excel Discussion (Misc queries) | |||
Formula/ Macro to combine data from multiple rows | Excel Programming | |||
Combine multiple sheets of data into one worksheet | Excel Discussion (Misc queries) | |||
combine data from multiple sheets | Excel Discussion (Misc queries) | |||
help please! need a macro which can combine data from 2 excel sheets | Excel Programming |