6 Replies Latest reply: May 15, 2014 9:52 AM by joel _ RSS

    Multiple Excel Worksheets

    joel _

      Is it possible to export a Monarch summary to Excel in such a way that when the key field changes it creates a new worksheet (tab) in Excel?  Is there something I need to to in Monarch (like multiple summaries) to be able to do this?  Currently I'm cutting and pasting once in Excel but that takes forever.

      Thank you,

      Joel

        • Multiple Excel Worksheets
          Grant Perkins

          Joel,

           

          Have you got V7 Pro?

           

          Certainly you could create multiple summaries but I suspect you are really filtering multiple times (by each possible key) for the same summary and if so you could run the export with "All Filters" set but only the one summary required. That may well do what you are seeking to do.

           

          Of course it does mean creating and duplicating/editing a few filters ...     

           

          I hope this helps.

           

          Grant

           

          Originally posted by joel:

          Is it possible to export a Monarch summary to Excel in such a way that when the key field changes it creates a new worksheet (tab) in Excel?  Is there something I need to to in Monarch (like multiple summaries) to be able to do this?  Currently I'm cutting and pasting once in Excel but that takes forever.

          Thank you,

          Joel /b[/quote]

          • Multiple Excel Worksheets
            joel _

            Originally posted by Grant Perkins:

            Joel,

             

            Have you got V7 Pro?

             

            Certainly you could create multiple summaries but I suspect you are really filtering multiple times (by each possible key) for the same summary and if so you could run the export with "All Filters" set but only the one summary required. That may well do what you are seeking to do.

             

            Of course it does mean creating and duplicating/editing a few filters ...       

             

            I hope this helps.

             

            Grant

             

              /size[quote]quote:[/size]Originally posted by joel:

            Is it possible to export a Monarch summary to Excel in such a way that when the key field changes it creates a new worksheet (tab) in Excel?  Is there something I need to to in Monarch (like multiple summaries) to be able to do this?  Currently I'm cutting and pasting once in Excel but that takes forever.

            Thank you,

            Joel /b[/quote][/b][/size][/QUOTE]

            • Multiple Excel Worksheets
              joel _

              Originally posted by Grant Perkins:

              Joel,

               

              Have you got V7 Pro?

               

              Certainly you could create multiple summaries but I suspect you are really filtering multiple times (by each possible key) for the same summary and if so you could run the export with "All Filters" set but only the one summary required. That may well do what you are seeking to do.

               

              Of course it does mean creating and duplicating/editing a few filters ...       

               

              I hope this helps.

               

              Grant

               

                /size[quote]quote:[/size]Originally posted by joel:

              Is it possible to export a Monarch summary to Excel in such a way that when the key field changes it creates a new worksheet (tab) in Excel?  Is there something I need to to in Monarch (like multiple summaries) to be able to do this?  Currently I'm cutting and pasting once in Excel but that takes forever.

              Thank you,

              Joel /b[/quote][/b][/size][/QUOTE]

              • Multiple Excel Worksheets
                Bruce _

                Joel

                 

                Not sure if Grant's answer has answered your question, but his answer is realy the best way. I made over 90 filters in Monarch (pain is only felt once), but now export every night to Excel to muliple files and multiple sheets.

                 

                Hope this helps

                • Multiple Excel Worksheets
                  Data Kruncher

                  Hello all.

                   

                  Joel, I have a solution for you based in Excel. While it's not the most robust code I've ever seen, I've tested this several times and it does work.

                   

                  This assumes that your data has headings in row 1, and that your data starts in row 2. Further, you must define LastColumn as the letter of the last column in which you have data. Set it to whatever you need. Currently, the code is not set to handle headings for you automatically (duplicate row 1 on each new sheet).

                   

                  As well, your key values must reside in column A, and the column must be sorted before you run the program. Automatic sorting would be a nice addition to the routine, but I was trying to develop a workable solution, not a perfect program -- this time     Hey, it's getting late here.   smile.gif[/img] 

                   

                  [font="courier"]Sub ProcessData()

                   

                      Dim LastKey As String

                      Dim TempSheetName As String

                      Dim CopyStartRow As Integer

                      Dim CopyEndRow As Integer

                      Dim Key As String

                      Dim LastColumn As String

                       

                      LastKey = ""

                      LastColumn = "B"

                      CopyEndRow = 1

                      Range("A" & CopyEndRow + 1).Select

                      Key = ActiveCell

                      Do While Key <> ""

                          Worksheets("Data").Activate

                          Range("A" & CopyEndRow + 1).Select

                          CopyStartRow = ActiveCell.Row

                          LastKey = Key

                           

                          Do Until Key <> LastKey

                              Selection.Offset(1, 0).Select

                              Key = ActiveCell

                          Loop

                           

                          Worksheets.Add

                          ActiveSheet.Name = LastKey

                          Worksheets("Data").Activate

                          CopyEndRow = ActiveCell.Row - 1

                          Range("A" & CopyStartRow, LastColumn & CopyEndRow).Select

                          Selection.Copy

                          Worksheets(LastKey).Activate

                          Range("A2").Select

                          ActiveSheet.Paste

                          Application.CutCopyMode = False

                           

                      Loop

                       

                      Worksheets("Data").Activate

                      Range("A1").Select

                       

                  End Sub  /font[/quote]Hope this helps. Give it a try and let us know how it goes.

                   

                  Regards,

                  Kruncher

                  • Multiple Excel Worksheets
                    joel _

                    Thanks for all the help!  I hope to work on this project later this week.  I'm somewhat new to Monarch so it takes me a while to work through things. 

                     

                    Thank you,

                    Joel