Announcement

Collapse
No announcement yet.

Insert Rows On Multiple Sheets

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Insert Rows On Multiple Sheets

    I need to figure out a way to automatically insert rows and copy data on multiple selected sheets. for example, if i insert a row anywhere on sheet 3, i need that same row inserted in the same location with the cells populated with the same data on sheets 4, 6 and 9.

    I would very much appreciate any help anyone could provide.

  • #2
    Re: Insert Rows On Multiple Sheets

    Yes, Group the Worksheets. See Select one or multiple worksheets in the Excel help.

    Comment


    • #3
      Re: Insert Rows On Multiple Sheets

      Thanks, Dave. I actually know about grouping and ungrouping sheets. What I'm trying to do is automate the task. We have several users in our office that don't know how to copy and paste - seriously. I'm trying to figure out a way to do this with VBA in a macro - I think - so the process can be automated.

      Comment


      • #4
        Re: Insert Rows On Multiple Sheets

        Right click on the Excel icon, top left next to File, choose View Code and paste and modify CodeNames of sheets to suit
        Code:
        Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
            Select Case Sh.CodeName
                Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"
                Sheets(Array(Sheet1.Name, Sheet2.Name, Sheet3.Name, Sheet4.Name)).Select
                Case Else
                  Me.Select
            End Select
        End Sub

        Comment


        • #5
          Re: Insert Rows On Multiple Sheets

          Originally posted by Dave Hawley
          Right click on the Excel icon, top left next to File, choose View Code and paste and modify CodeNames of sheets to suit
          Code:
          Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
              Select Case Sh.CodeName
                  Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"
                  Sheets(Array(Sheet1.Name, Sheet2.Name, Sheet3.Name, Sheet4.Name)).Select
                  Case Else
                    Me.Select
              End Select
          End Sub

          Seriously, Dave? If this work I'm going to send you a check. i've been wrestling with this for months!

          Comment


          • #6
            Re: Insert Rows On Multiple Sheets

            No payment required, the service is free

            You may want it restricted to a certain range, if so let me know as it's relatively simple to do so.

            Comment


            • #7
              Re: Insert Rows On Multiple Sheets

              dave,

              i tried it in a test sheet and i don't think i'm modifying the code names of sheets to suit correctly.

              for example, if i want a row inserted anywhere on sheet 2 to be replicated on sheets 4 and 5, should i enter "sheet2" after CASE and then (sheet4.name, sheet5.name) after ARRAY? If i haven't yet renamed the sheets?

              Thanks a million!

              Comment


              • #8
                Re: Insert Rows On Multiple Sheets

                Attach a blank workbook with at least 8 sheets and on the 4 sheets that should be grouped, Enter "Group" in A1. Details on Sheet Code Names HERE

                Comment


                • #9
                  Re: Insert Rows On Multiple Sheets

                  thanks dave. i've logged in under a different username. i have one for home and one for my office terminal...

                  i'm feeling a bit lost & in over my head. i'm a residential construction general contractor and we build very high-end, detail intensive projects. i'm working on building a workbook for project managers to track job-costs and monthly payments due by clients that is highly automated to mitigate opportunities for manual entry errors. here's exactly what i'm trying to do:

                  my workbook has sheet 3 titled "Client Estimate". sheet 4 is called "Draw 1", sheet 5 is called "Draw 2" and so on thru sheet 15 which is called "Draw 12". When my estimator puts together an estimate for a project, he uses the "Client Estimate" sheet to assemble the document that is pre-populated with 55 of our internal accounting categories. For the purpose of selling the project, he often needs to insert rows with additional information as sub-categories. i'd like any information entered on the estimate to automatically appear on the "draw" sheets exactly as it appears on the estimate - as they are the monthly client invoices. likewise, if any changes are made to the budget while the project is in progress (and there always are) the additional information entered needs to appear on all subsequent "draw" sheets. So, to make this simpler, if a row is inserted on the "Client Estimate" sheet (sheet 3) it needs to be automatically inserted and populated on all of the sheets to the right (sheets 4 - 15). If a row is inserted on the "Draw 3" sheet (sheet 6) the same row needs to be automatically inserted on all of the sheets to the right (sheets 7 - 15).

                  I was thinking last night that limiting this function to 'all sheets to the right of the active sheet' may make this simpler and that the code could work for the whole workbook instead of separate code for each sheet. is there a way to set the range for, 'all sheets to the right'?

                  Does any of this make any sense?

                  Thank you again for your kind assistance!

                  Doug

                  Comment


                  • #10
                    Re: Insert Rows On Multiple Sheets

                    One username per person. I have merged the 2.

                    Comment


                    • #11
                      Re: Insert Rows On Multiple Sheets

                      Hello Dave - I am relatively new to working with Excel and I came across this thread while investigating how to do some similar sheet grouping with restriction.

                      Specifically I want to have two or more sheets grouped such that all rows will be identical; If i insert or delete a row in one sheet, the same will be reflected in other sheets. That condition being met, I want to only column A to be identical among the sheets while all other columns remain independent with the exception that row order and internal continuity are maintained.

                      Is there a way to set this up so that it is automated and doesn't require me to group or ungroup manually? Thanks. -Anthony

                      Originally posted by Dave Hawley View Post
                      No payment required, the service is free

                      You may want it restricted to a certain range, if so let me know as it's relatively simple to do so.

                      Comment


                      • #12
                        Re: Insert Rows On Multiple Sheets

                        Antwag,

                        Welcome to Ozgrid.

                        While we're glad to have you on board, do no post your questions in threads started by others - - this is known as thread hijacking.

                        Always start a new thread for *YOUR* questions and if you think it helps clarify your situation you can in a link to this thread (or any other).

                        Start a new thread for your question and be sure to give it a search-friendly title that aply described your thread.
                        AAE
                        ----------------------------------------------------

                        Forum Rules | Message to Cross Posters | How to use Tags

                        Comment

                        Working...
                        X