Insert Rows On Multiple Sheets

[huds0047]

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.

[Dave Hawley]

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

[huds0047]

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.

[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

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 

[huds0047]

Right click on the Excel icon, top left next to File, choose View Code and paste and modify CodeNames of sheets to suit

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!

[Dave Hawley]

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.

[huds0047]

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!

[Dave Hawley]

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

[huds0047]

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

[Dave Hawley]

One username per person. I have merged the 2.