Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Insert Rows On Multiple Sheets

  1. #1
    Join Date
    27th March 2008
    Posts
    6

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    Re: Insert Rows On Multiple Sheets

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

  3. #3
    Join Date
    27th March 2008
    Posts
    6

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    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
    VB:
    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 
    
    

  5. #5
    Join Date
    27th March 2008
    Posts
    6

    Re: Insert Rows On Multiple Sheets

    Quote 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
    VB:
    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!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    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.

  7. #7
    Join Date
    27th March 2008
    Posts
    6

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    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

  9. #9
    Join Date
    27th March 2008
    Posts
    6

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    Re: Insert Rows On Multiple Sheets

    One username per person. I have merged the 2.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: April 28th, 2008, 22:44
  2. Insert Copied Rows In All But 3 Sheets
    By klh166 in forum EXCEL HELP
    Replies: 2
    Last Post: April 19th, 2008, 03:01
  3. Macro: Insert Multiple Sheets & Name As Cell
    By mprija in forum EXCEL HELP
    Replies: 5
    Last Post: December 18th, 2006, 14:38
  4. Replies: 1
    Last Post: July 27th, 2006, 13:28
  5. Insert Multiple Rows
    By rbrewer in forum EXCEL HELP
    Replies: 3
    Last Post: June 3rd, 2004, 00:27

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno