• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Merge/Stack Multiple Named Ranges (Across Multiple Worksheets) in a Master Sheet

Status
Not open for further replies.
Hi, Cruiser!

Much better then. A VBA solution will highly speed up worksheet recalculation as well as making more flexible handling lists/tabs increase. Will go towards this. Get back to you soon.

Regards!

PS: Just hope juzzvinay would settle for this too.
 
Hi, Cruiser!

Thought I forgot about this? Ok, you guessed. :) Give a look at this link:
https://dl.dropboxusercontent.com/u...ter Sheet (for juzzvinay at chandoo.org).xlsm

This is the code behind the cyan button, clear, neat, simple and fast (so says the author! ;)):
Code: 
Option Explicit

Sub MergeAll()
    ' constants
    Const ksSummaryWS = "Hoja0"
    Const ksSummaryRange = "Table0"
    ' declarations
    Dim rngS As Range, rngW As Range
    Dim I As Long, J As Integer
    ' start
    Set rngS = Worksheets(ksSummaryWS).Range(ksSummaryRange)
    With rngS
        If .Rows.Count > 1 Then .Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
    End With
    I = 1
    ' process
    For J = 1 To Worksheets.Count
        If Worksheets(J).Name <> ksSummaryWS Then
            Set rngW = Worksheets(J).Range(rngS.Address).EntireColumn.CurrentRegion
            With rngW
                If .Rows.Count > 1 Then
                    .Range(.Rows(2), .Rows(.Rows.Count)).Copy rngS.Cells(I + 1, 1)
                    I = I + .Rows.Count - 1
                End If
            End With
        End If
    Next J
    ' end
    Set rngW = Nothing
    Set rngS = Nothing
    Beep
End Sub

Just advise if any issue.

Regarsd!
 
@Cruiser
Thanks - would appreciate the help on the question. I solved it somehow, but I don't think I will be able to do it again. So it would be good to know an alternative.

Cruiser said:
Juzzvinay - I recently found this thread. I am sure you have figured this out by now, but I did not see a reply here. I can help with this question if you still need it.
"I also have a cell (formatted as Number - cannot be non Number format) with 2 leading zeros, but when I refer to the cell using "&" and a fixed word - it does not copy using the 2 leading zeros."
Click to expand...
 
@SirJB7
Thank you very much!! I really thought you had forgotten about it ;) - let me try to understand it during the day and see if I am getting stuck again.

I am sure many people, along with me, will find this useful.

:)

SirJB7 said:
Hi, Cruiser!

Thought I forgot about this? Ok, you guessed. :) Give a look at this link:
https://dl.dropboxusercontent.com/u/60558749/Merge_Stack Multiple Named Ranges (Across Multiple Worksheets) in a Master Sheet (for juzzvinay at chandoo.org).xlsm

This is the code behind the cyan button, clear, neat, simple and fast (so says the author! ;)):
Code: 
Option Explicit

Sub MergeAll()
    ' constants
    Const ksSummaryWS = "Hoja0"
    Const ksSummaryRange = "Table0"
    ' declarations
    Dim rngS As Range, rngW As Range
    Dim I As Long, J As Integer
    ' start
    Set rngS = Worksheets(ksSummaryWS).Range(ksSummaryRange)
    With rngS
        If .Rows.Count > 1 Then .Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
    End With
    I = 1
    ' process
    For J = 1 To Worksheets.Count
        If Worksheets(J).Name <> ksSummaryWS Then
            Set rngW = Worksheets(J).Range(rngS.Address).EntireColumn.CurrentRegion
            With rngW
                If .Rows.Count > 1 Then
                    .Range(.Rows(2), .Rows(.Rows.Count)).Copy rngS.Cells(I + 1, 1)
                    I = I + .Rows.Count - 1
                End If
            End With
        End If
    Next J
    ' end
    Set rngW = Nothing
    Set rngS = Nothing
    Beep
End Sub

Just advise if any issue.

Regarsd!
Click to expand...
 
Sometimes the "General" format retains leading zeros and sometimes it doesn't... on the same sheet! Frustrating. I have dealt with it two ways. 1) If you are simply entering the data, you can select "Custom" format and choose a format or create one. 2) If you are referencing another cell using the "General" format, use the TEXT function. Say you have the number 009 in cell A1. The formula:

=TEXT(A1,"000") forces a 3 digit result which includes the leading zeros. I use this with a +1 to generate a list of sequential numbers starting at 001. Of course, this requires a uniformed length for all of your strings of numbers. If you had 4 characters in A1, this would return only the first 3. On the other hand, if your number strings have varying lengths, but the number of leading zeros is consistant, say two, you could cut out the original zeros and add them back with:
TEXT("00","00")&MID(A1,3,255)
The format of the TEXT function using numbers is (Source or "Number","text_format").

If the text strings are of varying length AND the number of leading zeros are of varying length, well, that's a different story. I guess your formula would have to search for the first non zero in the string and figure the LEN from there subtracted from the original LEN to determine the number of zeros to add back in... I've never tried it.

Hope something here helps. The Ninjas can probably offer some even better ways to accomplish your goal.

juzzvinay said:
@Cruiser
Thanks - would appreciate the help on the question. I solved it somehow, but I don't think I will be able to do it again. So it would be good to know an alternative.
Click to expand...
 
Last edited:
SirJB7 said:
Hi, @juzzvinay, @Cruiser!
Let me know if anything needs a slight tweak; if not slight I don't know if I'd like to know it. :rolleyes:
Regards!
Click to expand...

Hi,

Apologies for the delay in replying - financial year end pressures!!

SirJB7 - could you explain the 'simple' code? How can I select what portion of which table from which worksheet from which external workbook should be merged? For ex, I have 4 different (but similar layout and format) tables in 4 different worksheets stored in 4 different locations on the same PC - how do I merge them together in the code mentioned? I can only figure out how to merge if they are in the same workbook currently.

Thanks
 
Hi, juzzvinay!

I'm gonna make it complex trying to explain the simple, but here it goes:
- no external workbook, summary is in worksheet defined in constant ksWSSummary
- processes all other worksheets
- copies the whole worksheet columns

The code works as follows:

CONSTANTS DEFINITION AREA: summary worksheet and output named range
' constants
Const ksSummaryWS = "Hoja0"
Const ksSummaryRange = "Table0"

VARIABLE DECLARATION AREA: rngS for summary, rngW for work (each child sheet)
' declarations
Dim rngS As Range, rngW As Range
Dim I As Long, J As Integer

INITIALIZATION AREA: define summary range and clears it
' start
Set rngS = Worksheets(ksSummaryWS).Range(ksSummaryRange)
With rngS
If .Rows.Count > 1 Then .Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
End With
I = 1

MAIN AREA: for each worksheet different than summary, defines a work range and appends it to summary range
' process
For J = 1 To Worksheets.Count
If Worksheets(J).Name <> ksSummaryWS Then
Set rngW = Worksheets(J).Range(rngS.Address).EntireColumn.CurrentRegion
With rngW
If .Rows.Count > 1 Then
.Range(.Rows(2), .Rows(.Rows.Count)).Copy rngS.Cells(I + 1, 1)
I = I + .Rows.Count - 1
End If
End With
End If
Next J

TERMINATION AREA: destroy created objects and plays a beep
' end
Set rngW = Nothing
Set rngS = Nothing
Beep

Regards!
 
Thanks SirJB7 for the quick revert.

I thought it would be a simple entry to input external tables/worksheets/ workbooks.

If you can think of some way to modify the code to allow one time setup of external file links it would be great but don't waste too much time over it.

At the risk of repeating myself, I feel Jeffrey's solution from earlier in the thread is close to the solution - if you don't mind could you have a look at his code and suggest if it can be modified to allow one time entry of external workbooks/ tables - ideally if it can be set up in such a way that all the tables thus set up are displayed like a form and user can select which tables should be merged. Or most critically if it can simply merge all the external table links in the code.

For ex, I don't mind having to sit once and enter the code manually to identify the external tables - c:\desktop\workbookI.xlsx --> worksheet1 --> tableA
c:\desktop\workbookI.xlsx --> worksheet1 --> tableB
c:\desktop\workbookI.xlsx --> worksheet2 --> tableA
d:\documents\workbookII.xlsx --> worksheet1 --> table A

and so on..

So long as afterwards it auto merges from all the specified input locations.

In case I have goofed up my explanation, please let me know.

If you are able to identify a workable solution and don't have to spend too much time on it, it would be great.

Once again, thank you so much for your invaluable time - it truly appreciated.
 
Hi, juzzvinay!

It's a very long thread with huge posts, so read them carefully will take me some time that I don't have right now. However I found 2 jeffreyweir's links, one to an old topic of this site but previous to 2013 migration so it's actually very hard to find something (I don't happen to find my own old stuff easily), and another one to an external link with something about SQL.

So considering those points and the nature of the work to be done here, I prefer to build the new Frankenstein from my 2 fresh codes. However if you'd like to follow jeffreyweir's path I suggest you to post here addressing him your requirement (@ + nick) and if in a few days you don't have any answer you might try to contact him by starting a private conversation.

Just please tell me which path would you be following so as I know if I have to work a little more or I can go for a couple of Carlsberg.

Regards!
 
Sample File
http://speedy.sh/9tST7/Named-Ranges-Sample-Extract.xlsm

VBA Code - As suggested by @jeffreyweir
(Modified from code in http://chandoo.org/forum/threads/creating-a-priority-table-from-multiple-tables-solved.10785)

Code: 
Private Sub Worksheet_Activate()

    Dim lo As ListObject
    Dim lr As ListRow
    Dim rngSource As Range
    Dim rngDest As Range
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
       

    Set lo = [Summary].ListObject
    With lo
        On Error Resume Next
        .DataBodyRange.Rows.Delete
        On Error GoTo 0

        Set rngSource = [Mumbai].ListObject.DataBodyRange
        Set rngDest = .HeaderRowRange.Offset(1)
        rngDest.Resize(rngSource.Rows.Count).Value = rngSource.Value

        Set rngSource = [Bangalore].ListObject.DataBodyRange
        Set rngDest = .ListRows.Add.Range
        rngDest.Resize(rngSource.Rows.Count).Value = rngSource.Value
       
        Set rngSource = [Chennai].ListObject.DataBodyRange
        Set rngDest = .ListRows.Add.Range
        rngDest.Resize(rngSource.Rows.Count).Value = rngSource.Value


        With .Sort
            .SortFields.Clear
            .SortFields. _
                    Add Key:=Range("Summary[[#All],[Value Date]]"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlDescending, _
                        DataOption:=xlSortNormal
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
   
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    End Sub

I am only having a problem getting the VBA to read tables from external workbooks. Specifically I get an error when I try to modify this line
Set rngSource = [Mumbai].ListObject.DataBodyRange

to

Set rngSource = Workbooks(C:\Desktop\Test.xlsx).Worksheets(Mumbai).Range(Mumbai).ListObject.DataBodyRange

So it works if the Table is in the same workbook as the summary sheet but not otherwise. Thats where I need help - what is the correct nomenclature to refer to external workbook. Do I need to instruct the macro to open the workbook first? Or make it Active? Or something else?

That is the primary issue and most critical to solve.

A smaller issue is how do I tell the macro to only copy a certain portion of the Table (Mumbai in this case)?

Secondly, the data copied currently does not refer to the source data i.e. if I click a cell which has been copied in the Summary sheet, it only shows me the end value. Is there any way it can display the formula used to calculate it? Or can the code be modified to make each cell a hyperlink to the source file?

Similarly, can the source formatting also be copied over with the data? How should the code be modified for that?

Thirdly, what changes need to be made to the code to sort using multiple columns?

I would also appreciate, if for my knowledge, you could explain Jeffrey code to me (like you explained your code - for example, what does Add Key:=Range("Summary[[#All],[Value Date]]"), _ imply?) and specify different conditions which can be used with Set RngSource command.

I have already been in touch with Jeffrey in Jan 2014 as well as again last month - I believe this topic has gotten old for him and he must be involved in other matters. So I don't think any further help will be come from there.

If you feel modifying Jeffreys code is simpler for you than creating a new code then great - if not, please build the fresh code (do try to keep in mind my queries).

In an ideal world I would like to create a userform - when the macro is run, it will generate a popup showing the various tables which are coded by the macro to be copied in the summary sheet and will allow the user to select all or any number of Tables to be summarised in the summary sheet and then display only the tables thus selected. Possible? Or am I aiming unrealistically?

Thanks SirJB7

PS- Enjoy a Carlsberg first and then look at this refreshed!
 
Hi, juzzvinay!

You were a little bit tricky, but there you'd find your reward. When I wrote:
SirJB7 said:
Just please tell me which path would you be following so as I know if I have to work a little more or I can go for a couple of Carlsberg.
Click to expand...
I tried to mean: chose if going with jeffreyweir's solution or with mine, hence ask the proper author. So you're cheating.

I left a door either a bit open or not well closed and as you managed to get it fully open, you deserve my blind shot.

If your main issue is how to reference structured tables from external workbooks try doing what follows.
a) Replace this:
Code: 
        Set rngSource = [Mumbai].ListObject.DataBodyRang
by this:
Code: 
        Set wb = Workbooks.Open("<full path>\Named Ranges - Sample (Extract).xlsm")
        Set rngSource = wb.Worksheets("M").[Mumbai].ListObject.DataBodyRange
b) Remember to add this at the beginning:
Code: 
        Dim wb as workbook
and this at the end:
Code: 
        wb.close False
        Set wb = Nothing

First try to solve this and then send my daily six-pack of Carlsberg so as to incite me to give a new look to your new uploaded file.

Regards!
 
Thank you very much Kind Sir!! This is just :awesome:!

I was so sure that the solution was within reach and all it took was just a few additional words. Of course knowing which words and where to use them is what makes all the difference.

I am sure it will work although I should probably check it out before sending the 12-pack and letting you off the hook ;););)
 
Hi, juzzvinay!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
SirJB7 said:
Hi, @juzzvinay, @Cruiser!
Let me know if anything needs a slight tweak; if not slight I don't know if I'd like to know it. :rolleyes:
Regards!
Click to expand...
Wow, I can't believe this is ten weeks old! I am still in the 2003 version of my project... Still haven't gotten to the point of using the VBA code you so graciously provided. I've disconnected my PC from the internet since the end of support for XP and office 2003, so I haven't been back here for a while. Posting this on my wife's mac. Trying to get to a certain point before I upgrade my PC and start working with Office 360... At any rate, I am sorry that you did all that work and then I dropped off the face of the earth. I assure you I will get to the point of implementing your code eventually. It's just that every time I think I am "close" I find more ways to improve my project that just MUST be implemented. Nothing is ever "good enough"! Cheers
 
SirJB7 said:
Hi, Cruiser!

Thought I forgot about this? Ok, you guessed. :) Give a look at this link:
https://dl.dropboxusercontent.com/u/60558749/Merge_Stack Multiple Named Ranges (Across Multiple Worksheets) in a Master Sheet (for juzzvinay at chandoo.org).xlsm

This is the code behind the cyan button, clear, neat, simple and fast (so says the author! ;)):
Code: 
Option Explicit

Sub MergeAll()
    ' constants
    Const ksSummaryWS = "Hoja0"
    Const ksSummaryRange = "Table0"
    ' declarations
    Dim rngS As Range, rngW As Range
    Dim I As Long, J As Integer
    ' start
    Set rngS = Worksheets(ksSummaryWS).Range(ksSummaryRange)
    With rngS
        If .Rows.Count > 1 Then .Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
    End With
    I = 1
    ' process
    For J = 1 To Worksheets.Count
        If Worksheets(J).Name <> ksSummaryWS Then
            Set rngW = Worksheets(J).Range(rngS.Address).EntireColumn.CurrentRegion
            With rngW
                If .Rows.Count > 1 Then
                    .Range(.Rows(2), .Rows(.Rows.Count)).Copy rngS.Cells(I + 1, 1)
                    I = I + .Rows.Count - 1
                End If
            End With
        End If
    Next J
    ' end
    Set rngW = Nothing
    Set rngS = Nothing
    Beep
End Sub

Just advise if any issue.

Regarsd!
Click to expand...
I am finally at the point of trying this out on my project. Unfortunately, your "Hoja" example is not working in excel 2003. When I try to open the .xlsm file, it says it canot be found with XLM converter. When I save it as .xls, it says "Unitialized Active X controls will not be opened in this version of excel". I continued, enabled macros, and opened the file. The Merge All button didn't work, so I opened the macro and ran it. I got a Runtime Error '57121': Application-defined or object-defined error. I hit debug and it highlighted the row of code: Set rngS=Worksheets(KsSummaryWS.Range(KsSummaryRange). Any thoughts?
 
Cruiser said:
Sometimes the "General" format retains leading zeros and sometimes it doesn't... on the same sheet! Frustrating. I have dealt with it two ways. 1) If you are simply entering the data, you can select "Custom" format and choose a format or create one. 2) If you are referencing another cell using the "General" format, use the TEXT function. Say you have the number 009 in cell A1. The formula:

=TEXT(A1,"000") forces a 3 digit result which includes the leading zeros. I use this with a +1 to generate a list of sequential numbers starting at 001. Of course, this requires a uniformed length for all of your strings of numbers. If you had 4 characters in A1, this would return only the first 3. On the other hand, if your number strings have varying lengths, but the number of leading zeros is consistant, say two, you could cut out the original zeros and add them back with:
TEXT("00","00")&MID(A1,3,255)
The format of the TEXT function using numbers is (Source or "Number","text_format").

If the text strings are of varying length AND the number of leading zeros are of varying length, well, that's a different story. I guess your formula would have to search for the first non zero in the string and figure the LEN from there subtracted from the original LEN to determine the number of zeros to add back in... I've never tried it.

Hope something here helps. The Ninjas can probably offer some even better ways to accomplish your goal.
Click to expand...
Just ran across this again. CORRECTION: Using the TEXT function wil cause a MINIMUM number of characters in your return. A higher number will NOT be truncated.
 
Hi, Cruiser!
My .xlsm file version is intended to be used with the new Excel workbook format that yet goes by its 3rd edition (2007, 2010, 2013), if we skip Mac version 2011. I haven't tested it under the older .xls 2003 version, but maybe you have a workaround.
There's a patch that MS released years ago for handling new file versions of all Office products (.xlsx, .xlsm, .docx, ...) with prior versions. Here's the link:
http://www.microsoft.com/en-us/download/details.aspx?id=3
Regards!
 
SirJB7 said:
Hi, Cruiser!
My .xlsm file version is intended to be used with the new Excel workbook format that yet goes by its 3rd edition (2007, 2010, 2013), if we skip Mac version 2011. I haven't tested it under the older .xls 2003 version, but maybe you have a workaround.
There's a patch that MS released years ago for handling new file versions of all Office products (.xlsx, .xlsm, .docx, ...) with prior versions. Here's the link:
http://www.microsoft.com/en-us/download/details.aspx?id=3
Regards!
Click to expand...
Somehow I missed this post, so a very belated thanks to you Sir! Just wanted to let you know that I opened the Hoja worksheet on another computer in excel 2010 and it works like a charm. Pretty darned elegant piece of work. Thanks again.
 
SirJB7 said:
Hi, Cruiser!

Thought I forgot about this? Ok, you guessed. :) Give a look at this link:
https://dl.dropboxusercontent.com/u/60558749/Merge_Stack Multiple Named Ranges (Across Multiple Worksheets) in a Master Sheet (for juzzvinay at chandoo.org).xlsm

This is the code behind the cyan button, clear, neat, simple and fast (so says the author! ;)):
Code: 
Option Explicit

Sub MergeAll()
    ' constants
    Const ksSummaryWS = "Hoja0"
    Const ksSummaryRange = "Table0"
    ' declarations
    Dim rngS As Range, rngW As Range
    Dim I As Long, J As Integer
    ' start
    Set rngS = Worksheets(ksSummaryWS).Range(ksSummaryRange)
    With rngS
        If .Rows.Count > 1 Then .Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
    End With
    I = 1
    ' process
    For J = 1 To Worksheets.Count
        If Worksheets(J).Name <> ksSummaryWS Then
            Set rngW = Worksheets(J).Range(rngS.Address).EntireColumn.CurrentRegion
            With rngW
                If .Rows.Count > 1 Then
                    .Range(.Rows(2), .Rows(.Rows.Count)).Copy rngS.Cells(I + 1, 1)
                    I = I + .Rows.Count - 1
                End If
            End With
        End If
    Next J
    ' end
    Set rngW = Nothing
    Set rngS = Nothing
    Beep
End Sub

Just advise if any issue.

Regarsd!
Click to expand...
Hey SirJB7,
I just had to attach the macro to a different type of button and, viola! All good in excel 2003. The problem I am running into is that formulas in my source data add blank rows to the merge/stacker. I have dynamic ranges for the linked data (data won't be manually input into the merge/stacker), but I can't figure out the code in Excel 2003 to populate the source data sheets (hoja1,2,3) from my dynamic range(s). Can I trouble you for some more words of wisdom, ie, VBA for pulling the data from my named dynamic named range(s) into dynamic ranges in hoja1,2,3?
 
Hi, Cruiser!
Having taken a few sabbatical months I don'e even remember how should I spell this program of he bib green X... it's H-e-c-e-l-l?
Regards!
 
Status
Not open for further replies.
Back
Top