Protected Worksheet Macro To Insert Rows?

Feb 20, 2007

I have a protected worksheet, which I have 2 macros, 1 to spell check and 1 to insert rows, they both unprotect the sheet and re protect it again once they have completed. The problem I am having is that when I protect the work sheet first time round I tick the box to allow users to insert rows, once the Macros run they disable this functionality. Is there anyway I can include this in my Macros or do I need to add a new button!

ADVERTISEMENT

Insert New Rows Into Protected Worksheet?

Oct 13, 2013

I have created a table where the first 6 columns are data entry and the next 6 columns are formulas to give desired results.

I have made all the data entry cells unlocked and the rest of the spreadsheet locked (cells with formulas etc.)

I would like to be able to insert rows to the protect sheet which I have managed to achieve but when I insert the row the formulas do not update in the row.

View 3 Replies View Related

Allow Users To Insert / Delete Rows On A Protected Worksheet?

Apr 5, 2014

I have a worksheet that I have some "locked" cells on. I also have protected the worksheet but when I check to allow users to insert rows and to delete rows and then save it, when I re-open it I can't insert rows or delete rows???

I am using some VBA to allow for the collapseing of rows while maintaining protection and that works perfectly. I want users to be able to insert and delete rows while maintaining protection. I thought a simple check in the protection was good enough, but for some reason it isn't.

View 2 Replies View Related

Macro To Insert Rows Using A Worksheet Value

Apr 7, 2009

Need to look at a number in a cell, inserts that many rows below that row, then repeats this for following rows that also have cells that indicate how many rows to insert.

Attached is a simple sample data sheet with how data looks before and how it should look after.

View 2 Replies View Related

Insert Cut Row In Protected Range Of Rows

Sep 13, 2013

I need a VBA code for a worksheet to automatically insert cut paste a row from a range of unprotected rows to the range of protected rows once the user decides to do so.

I have a sheet called "Customer Stock" which has two tables called "Uncollected" and "Collected".

I need "Uncollected" table to be unprotected and "Collected" Table to be protected.

Once the a Customer collects the goods, the user can cut and insert the collected row from the unprotected "uncollected" table to just above the first row of the protected "collected" table,

so that the inserted cut row now should be protected.

The password should be 1234 and the user won't know the password. Only the administrator will be having it.

View 14 Replies View Related

Hidden Rows In Protected Worksheet?

Jul 21, 2014

I was able to create a macro that allows a user to unhide rows in a protected worksheet without unprotecting the other locked fields (see below). My question is: I want to add to this command so that 1 row can not be "unhidden" if the previous row is still hidden. For example, row 25 can not be unhidden if row 24 is still hidden.

Macro command used:

Private Sub Commandbutton4_Click()
Sub Hide_Rows2h()
ActiveSheet.Unprotect Password:="xxx"
Rows("25").Hidden = Not Rows("25").Hidden
ActiveSheet.Protect Password:="xxx"

View 3 Replies View Related

Hide Columns / Rows While Having Worksheet Protected?

Jun 19, 2014

I got a worksheet here. I'd like to lock all the cell height and width using protected sheets function. I realized from time to time I have the need to hide them. How do I enable hiding sheets while maintaining cell integrity?

View 3 Replies View Related

Delete Multiple Rows Protected Worksheet

Apr 4, 2007

Is it possible to delete multiple rows when the worksheet is protected using vba? For Example Peps - Select a undetermined amount of rows and then use a macro to unprotect - delete the selected rows - protect.

View 5 Replies View Related

Count Rows In Column On Worksheet Then Insert That Many Rows On Another Worksheet

Nov 15, 2013

Count all the true statements in column A (Work) of sheet1 (Checklist), once counted insert that many rows on sheet2 in a specific location, I found a count formula just don't know how to do the insert rows part

Code:
Sub CountRows()
Dim Rng As Range, CountTrue As Long
Set Rng = Sheets("Checklist").Range("Work")
CountTrue = Application.WorksheetFunction.CountIf(Rng, "True")
End Sub

View 3 Replies View Related

How To Run Macro In Protected Worksheet

Mar 25, 2014

I want to run a macro in a worksheet which is proteced.

It's a simple macro which erases the content in (some) cells, nothing more. Even those celles are formatted as unlocked the macro fails.

View 4 Replies View Related

Macro On Protected Worksheet

Aug 16, 2007

I have the following macro and when it "reprotects" my worksheet I need it to also turn on these Protect Sheet properties:

Format cells
Format columns
Format rows
Insert hyperlinks
and I need it to leave on the defaults of Select locked cells and select unlocked cells

Sub Spell_Check()
ActiveSheet.Unprotect Password:="pmo"
Cells.CheckSpelling "SRdictionary.dic", SpellLang:=1033
ActiveSheet.Protect Password:="pmo", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

View 4 Replies View Related

Run VBA Macro Code On Protected Worksheet

May 18, 2009

I have incorporated print/print-preview command buttons/VBA into a workbook with protected sheets. To enable these command buttons to function when the relevant sheet is protected, I have had to add VBA code to unprotect the sheet before generating the print preview, and then to protect it again afterwards. However, the code I have used (see below) prompts the user to enter the protection password, is there any code I can use where I can write the password into the code itself to unprotect the worksheet without the using having to enter the password?

'Unprotect Sheet
ActiveSheet.Unprotect

'Print preview & cell formatting code

'Protect Sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True

View 2 Replies View Related

Sort Macro On Protected Worksheet

Jun 8, 2008

I'm not fond with macros, but this is what i need help with.

I need two macros one to sort ascending (Ctrl+A), and one to sort descending (Ctrl+D).

This is what the macro to do. Unprotect, sort selected column, protect.

View 7 Replies View Related

Insert Rows On Hidden Worksheet

Mar 23, 2009

My workbook contains 10 worksheets. Some users will use all 10 worksheets; some will use only 5 worksheets. If a user is not using a worksheet, I would like to hide it but leave it in the workbook.

However, I want to keep all workbooks in sync even though a user may choose to not use a particular worksheet. When my macros encounter a hidden worksheet, they stall. Does coding exist that allows a Macro to run on a hidden worksheet without making the worksheet visible?

View 2 Replies View Related

Macro To Split Rows Into Groups Of 5 And Insert 3 Blank Rows In Between

Feb 9, 2013

I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:

Supplier
Invoice Date
GL Date
Invoice Amt

[Code].....

View 1 Replies View Related

Macro To Insert New Rows Based On Commas In Previous Rows?

Mar 15, 2014

I'm a macro novice and have been trying to teach myself how to write the correct one for a task I need to do, but I cannot seem to get it right. Basically, I have bunch of data and for one of the variables, different values are separated by commas. What I want is to create a row copying the info below for each piece of data after the comma.

Sheet1

A
B
C
D

[Code].....

I suspect there is a fairly easy way to do this, but I cannot figure it out from searching the forums (or rather, I can't get it to work right).

View 6 Replies View Related

Macro To Insert Based On Worksheet?

Jul 25, 2014

I have a macro that runs in Sheet 1 and enters the word "Complete" in cell P of the active row. The same cell that requires the word "Complete" in Sheet 2 is in cell T of the active row. Is there a way to have the same shortcut that will identify which worksheet it is in and go to the appropriate cell?

Code:
Range("P" & ActiveCell.Row).Select
ActiveCell.FormulaR1C1 = "Completed"
ActiveCell.Offset(0, 1).Range("A1").Select

View 4 Replies View Related

Insert Worksheet Macro Conditional

Mar 9, 2007

Still struggling with the calculator. Is there anyway to get a macro to insert a set number of worksheets. The set number would be the sum of two fields on Sheet 1.
Example attached. I have searched the forum and can now use a macro to insert a worksheet but have no idea how to tailor it.

View 2 Replies View Related

Insert Row On A Table In Protected Sheet

Dec 11, 2013

I am working on an excel table in my worksheet contains formulas and locked cells. lets say table range is from column "a" to "f" and there is formula on cell in columns "c" and "f". I just locked cells in column "c" and "f" to prevent people changing the formula accidentally. but when the sheet is protected i am not able to insert or delete rows as the rows has locked cells in columns "c" and "f".

I tried the below macro to unlock the sheet, insert row and lock the sheet again:

[Code] .....

So by running the macro it asks the password and do the job but the issue is it needs password and i do not want people to know my password. I don't want them able to unlock the workbook and make any undesired changes. I want them to be allowed insert or delete rows and input data in unlocked cell. I want the sheet to be locked all the time and only when people want to insert or delete a row it be unlocked and be locked again after inserting or deleting whit no need to insert password in this case only.

View 1 Replies View Related

Macro To Insert Column Heading To All Worksheet

Nov 27, 2008

Pls help me do a macro that upon clicking a button it will copy and paste a column heading (located in Row1 of Sheet1) to all non-empty worksheets (Sheet 2 to Sheet N) in the workbook simultaneously. Note that each worksheet has no column heading and has the same number of columns as that of the column header in Sheet1. Worksheet 2 to Worksheet N contains data extracted daily.

View 7 Replies View Related

Macro To Insert Two Columns With IF Formulas Into Worksheet?

Dec 13, 2011

Writing a macro to insert two columns with IF formulas into a Worksheet. The 2 columns to be added would be after Column G on Sheet 1.

The new Column H would be

"=IF(G1>=Q#,G#/Q#,0)"

And the new Column I would be

"=IF(G#>=Q#,MOD(G#,Q#),G#)",

Where # represents the row number.

View 3 Replies View Related

Use Macro To Insert Same Formula Into Each Worksheet In Same Folder?

Aug 21, 2012

I have a folder for each month, and in the folder there are worksheets for each day of the month. eg for January folder, it will contain 31 worksheets. They all have the same column headings. I want to know if there is any way i can use a macro to automatically insert a formula in each worksheet, using loop?

View 2 Replies View Related

Macro - Auto Insert And Create A Worksheet

Apr 21, 2007

Is that possibe to use the "templeate" sheet and lookup the "Master" sheet to create a new worksheet called "result"

View 2 Replies View Related

Macro To Insert Rows..

Oct 31, 2009

I have a worksheet that includes the list of buildings in an area and the number of households in each building. I want to list the households on separate rows and to number them from 1 to x according to the total number I have. Is it possible to do this using a macro, if so, how?

View 5 Replies View Related

Insert Rows By Macro

Jan 18, 2010

I've created a macro to assign to a button that inserts a row above say row 10, then another macro to assign to a different button that inserts a row above row 20. My problem is, having clicked the first button a row is inserted above row 10 but of course row 20 now becomes row 21, so now I need the second button to insert a row above row 21 instead of 20.

Is there any way I can write a macro that remembers the old row numbers?

View 14 Replies View Related

Macro To Insert Rows ..

Sep 18, 2005

Example:

My sheet needs to insert a blank row under a number everytime it changes.
The column "C" may have 20 or so rows with the number 10 for example, the
next five might be the number 8, I need a row between the last number ten and
the first number eight. This may be repeated many times through the
spreadsheet.

As well, the adjacent columns "D" & "E" are the same as described above.
all 3 columns are continuous ie: ....

View 12 Replies View Related

Macro To Insert Rows

May 25, 2007

I need a macro that will insert 11 rows before the word total. The word total will always be merged between col A&B but will not always be in the same row. It might be in row 5 one day and row 25 the next.

View 9 Replies View Related

Insert Rows - Macro

Dec 2, 2008

I have recorded a macro to insert six rows. I'd like to take this one step further and have the user enter the number of rows they want to insert. Right now the data is entered in A10:F33.

View 9 Replies View Related

Protected Sheet - Insert Picture File

Feb 10, 2007

Workbook Password Protect 2 Levels. I have managed to write simple but effective code that allows only one password to be enetered to open a SS & offers 3 levels of protection, limited permission to enter data, read-only & data completely hidden. This is excellent for my needs however I want the user who has limited rw to be able to insert a pic file which is their signature but because the sheet has protected cells it won't allow user to insert picture jpg file.

I have just thought of a creative solution tho. What if the picture file is already saved in the workbook and by using code to ask to insert pic file it copies from one location in SS to the appropriate cell in the worksheets?

The scenario is that I have fortnightly timesheets in 12 calendar months in 12 separate sheets. When staff member fills in timesheet I want them to insert signature for that f/night indicating thatthey have filled it in & it is correct but because of protection it won't allow me to.

View 2 Replies View Related

Macro To Insert Rows Conditionally

Apr 8, 2014

I've got a dataset of certain trades across different markets and traders, and on different days. The date of the trade is in column A, the contract ID is column D and the trader ID is column J.

Traders do not trade every day. I would like to insert a new row/rows for each date on which traders do not trade between their first and last trade, for each market in which they participate.

Is this possible using macros?

And, if so, then I would like to insert the high and low prices for the market on those dates from a different worksheet into the newly created blank rows. On this second workbook the contract ID is column A, the date is column C, the high price is column F and the low price is column E. I would like the low price to be inserted into column AJ on the original worksheet and the high price into column AK.

View 2 Replies View Related

Macro To Insert Formatted Rows

Apr 14, 2009

I need columns A, B, and C to have borders all the way around each cell in the row. Then, columns D through H need to be merged, with one border around the merged cell. The row height needs to be size 16 font, which I've been solving by putting in one letter that size, colored white so it doesn't show up when I print.

Basically, I have a spreadsheet of a bunch of tools, and I need to insert a row below each entry for the mechanics to sign their initials for three stages of the repair process, and also have the larger, merged cell for notes.

View 7 Replies View Related

Macro To Insert Rows In Excel

Nov 4, 2011

I frequently work with a data set that has about 35,000 rows and have to split it up into smaller sets. I manually insert a row after every 8,000 rows and then copy each smaller set into a new CSV file. Is it possible to automate this with a macro?

View 3 Replies View Related

Macro To Insert Rows At Different Points

Oct 4, 2007

Column 'A' in my spreadsheet has cells with a number in and blank cells in no particular order. What I need to do is insert a new row above each number but I don't know how to write this as a macro.

View 9 Replies View Related

Macro To Insert Rows Part II

Sep 19, 2003

i needed a macro that would go through the cells in column A, which is a list of adverstiser and insert a row when the name of the advertiser changed. So the macro needs to compare each cell to the one before and when the values differ, insert a macro. But I need one that will first go through column A, and then once finished with the items in A, go through column B and do the same thing. Can't get the column B to work...

View 9 Replies View Related

Add A Worksheet To An Existing Spreadsheet Which Has VBA Behind It, But The Worksheet Is Password Protected

Aug 23, 2006

One of my work colleagues need to add a worksheet to an existing Excel Spreadsheet which has VBA behind it, but the worksheet is password protected. The developer who wrote the application has now left, so we have no idea what the password is. Is there anyway of getting round it, like cracking into the spreadsheet to find out the password, or another way?

View 2 Replies View Related

Macro To Insert Rows In Multiple Tabs

Feb 19, 2010

I'm trying to figure out how to create a macro for a project at work. Basically, think of a spreadsheet with 5 tabs, but the information in Tab 1-Column D is the same in Tab-4 Column D and Tab-5 Column D. When I insert a row, though, I have to go to each tab, insert the row, and copy down the formulas from the row above to ensure the flow-through stays true. This can get very tedious.

Does anyone have a template or tips on a macro that would, in essence, work like this:

a) Highlight the row above which a row should be inserted
b) Trigger the macro
c) A row is inserted above the highlighted row in Tabs #1, #4 and #5
d) The information from the row above the inserted row is copied down to the new row in each of the three tabs.

View 6 Replies View Related

Macro To Insert Defined Number Of Rows

Dec 29, 2008

My first sheet shows the data - a list of audits and their current status.

AuditStageAlphaIn ProgressBetaReportingCharlieCompleteDeltaCompleteOmegaPlanning

I've then used a simple count formula to add up the number by each stage and it's these numbers that I want the macro to use when determining how many rows to input.

Complete2Reporting1In Progress1Planning1

A second worksheet hosts the desired output - a simple report template, split into stages for each status type (completed, planning etc.) with the correct number of rows for each.

Completed AuditsCharlieDeltaReportingBetaAudits in ProgressAlphaAudits in PlanningOmega

So, I want the macro to ensure that we have the right number of rows for each stage, as this month will be run on a monthly basis and the stage each audit is at will change, and the number of rows required in each section will also be different.

View 9 Replies View Related

Macro Used To Insert Rows And Copy Formulas

Jul 13, 2009

I was searching through this site and found this macro ...

View 9 Replies View Related

VBA Macro To Move & Insert Rows And Average

Apr 6, 2009

I would like to have a VBA code to the following:Please note that the rows are dynamic and I need the results row for every customer.Currently it is totalling and displaying the results column at the botton of every customer but I need it at the start of every customer

1.When the new customer number starts the cells are shifted and moved one row.

2.Insert the results word and it displays the results thats displayed currently at the bottom,shoudl be displayed at the top where the row gets inserted in (1)

Current view:

Customer Date of POD Del date Diff Percent

123456 03.02.2009 03.02.2009 0 100
05.02.2009 10.02.2009 5 0
Result 0

(The result is that if all the rows are 100% ,the result row is 100% else it is 0%)

Preferred View:

Customer Date of POD Del date Diff Percent

123456 Result 0
03.02.2009 03.02.2009 0 100
05.02.2009 10.02.2009 5 0

View 9 Replies View Related

Insert Rows Macro & Copy Formats

Sep 25, 2006

I need to find a way to insert a row (preferably at the bottom of an existing list) that mirrors the formatting of the row above.

I have had a stab at this (see attached example file) but my solution requires the user to select the correct activecell before it works correctly

View 9 Replies View Related

To Write A Macro That Will Insert A Formula Directly Into The Cells In Column M Of My Worksheet

Aug 9, 2007

I am trying to write a macro that will insert a formula directly into the cells in column M of my worksheet.

The worksheet has about 3400 rows. When I run the macro it does not adjust the cell reference in the formula so I am getting the same value in all 3400 rows.

I am using the following ....

View 9 Replies View Related

ADVERTISEMENT