Edit protected sheets via master sheet

Welcome to the forum

Yes, this can be done via VBA.
The code to protect and unprotect a sheet named "Apple" is below

When you are in sheet "Master" when do you want to insert/delete rows in the other sheets?
- user says I want to insert some rows?
- driven by values in sheet Master?
- which rows are to be "inserted"
etc

Please Login or Register  to view this content.

3 questions
Q1 How do we select the rows to be copied?
(eg could user select them with mouse and then run a macro? is it all the completed in rows in the data area? etc)

Q2 Does the data require deleting after its been copied to other sheet(s)?

Q3 Where is it copied to?
(eg bottom of ranges in the 2 additional sheets, somewhere else?)


It would also help if you attach a workbook (make it anonymous) - click on Reply, then Go Advanced then Manage Attachments etc

ManageAttachments3.jpg

I meant make the data anonymous - no personal names or anything organisationally sensitive - so amend text values to Client001 rather than actual client name etc

Don't forget to give us the password for the 2 sheets

Thanks for the workbook.
Are you copying and pasting one row at a time?

Can you just list all your steps for me replacing the question marks with sheet names and column letters etc
(I may be able to automate a bit more for you - you should not need to select all the tabs etc)

1 enter values in columns ? ? ? in sheet named "?"
2 select the an entire row (or columns A:D ???) to copy
3 select the 3 sheets named "?" "? " and "?" (using their tabs)
4 select the next available row in the appropriate section
5 paste values? or values and formula??? - all 3 sheets are updated together
6 Which sheets will contain passwords?

thanks

See if this gives you what you want

Select any cell in the row that you want to copy and run the macro with {CTRL} t
It copies and inserts in the same row. So make sure you insert the last row in the section if that is where you want the new row inserted.

The 2 sheets "Cost" and "Client" are currently password protected with password = "password"

Please Login or Register  to view this content.

Let's chat about how you want it to work

Amend this to what you want to happen

1. Go to row where I want to add a new block of 6 rows

2. Trigger macro that asks me what I want to insert from a dropdown
SELECT FROM:
3.00 Floors
4.00 Walls
5.00 Ceilings
6.00 Wall Finishes
7.00 Elevations
7.01 Elevation AA
7.02 Elevation BB etc...

3. Based on selection

- 6 rows inserted:
= Category row (no formula)
= 4 X data rows (see formulas below)
= Total row (=SUM formula for the 4 data rows)

- in 3 sheets - formulas as per example (row 10) in data rows:

Column F sheet"Net Costs"
=C14*E14

Columns C:F sheet "Cost"
= 'Net Costs'!C10
= 'Net Costs'!D10
= 'Net Costs'!D10*1.25
= C10*E10

Columns C:F sheet "Client"
= 'Net Costs'!C10
= 'Net Costs'!D10
= VALUE(Cost!E10)
= C10*E10

We should not really need all the options from the drop down

- does this mean you require no options?
- if so the category row is blank and you have to type in standard wording in 3 sheets
- with the selection cell in column A and in column B can be populated automatically

The easiest way to achieve this would be to have the 7 row X 3 sheet "standard" block available somewhere so that it is a simple copy and Insert/paste job
- all the formula will correctly re-reference cells and also it will make modification easy for you later if something fundamental changes

So - where do we put the 7 rows - my choice would be:
- the first 7 rows of the 3 sheets
- make the rows not visible

It's either that
- or somewhere below the data (and hide the rows)
- or create a separate sheet for this purpose with 21 lines (7 per sheet) - I have not tested this - but I think it will re-reference everything correctly

Which do you prefer?

This worked for me

What you need to do
- copy blocks of 7 model rows in 3 sheets
- paste to identical 7 rows in each sheet (below the data)
(I used Joinery & Metalwork as my 7 rows to copy and paste)
- amend the defaults for category and number (in sheet "Net Costs" , column A and B)

- create Named Ranges for each of the 3 areas
- I used Model1, Model2 and Model3 for "Net Costs", "Cost" and "Client" areas respectively
- use your own names but amend the Select Case bit of the VBA to match

- put this VBA in a general module, and allocate a short-cut to it, making it easy to run

Please Login or Register  to view this content.

NamedRangeForProj.jpg

I attach the test file just in case you need to look at anything
VBA in post18 runs with {CTRL} k

(and the insert single row VBA runs with {CTRL} t )

Hi Kev
Thank you for this it works a treat. Only one problem i have is that I had a go at creating my own Macro last night for deleting a line out, which was working but now has stopped any ideas why?

Please Login or Register  to view this content.

Please edit your previous post - select the VBA and then click on # icon
thanks

Yes and your code works for me - it deletes a row in 3 sheets as expected

Are your macros disabled?
2 obvious things to try:
- is VBA in debug mode needing a reset? (see picture)
- close the file and re-open it

ResetVBA.jpg

Are you using different passwords?
- that would also prevent your macro from working

Error in F192
I do not know when the error occurred on line 192. When testing lots of things get changed.
Fix it and hopefully it will not recur.

Unfortunately some formulas do not like rows being deleted etc
Usually that error manifests itself with #REF error - that is because a "key" cell has been deleted and the formula cannot use it

Keep this in mind, in case you need it later:
- Named Ranges can be single cells or a range of cells
- avoid the dreaded #REF by using named ranges instead of cell references in formula (unless you delete the entire range!!)
- eg name each of your subtotal cells "TotalPrelims", "TotalDemolition" etc and then arrive at the grand total with

Formula:

Please Login or Register  to view this content.

(or you can use =SUM)
- when a new category is inserted, create a new range name for its subtotal and amend the formula for grand total

Overall total logic check
It may be useful for you to have a logic check at the bottom of the sheet to give you confidence that your grand total is correct.
With your grand total in cell F198, this formula should equate to zero:

Formula:

Please Login or Register  to view this content.

You are welcome

Please mark thread as solved (Thread Tools @ top of thread)