+ Reply to Thread
Results 1 to 8 of 8

Adding new row keeping existing format all the wile keeping worksheet protected.

  1. #1
    Forum Contributor
    Join Date
    11-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    100

    Adding new row keeping existing format all the wile keeping worksheet protected.

    So I am creating an equipment log which has some locked cells I want to be able to add a new row keeping the formatting of the rows. I have most if it down but it selects the row which a cell is selected.

    then it inserts a row just below it. How can I modify this VBA Macro to add a blank row to the bottom keeping the existing formula? the blue plus signs have my existing macro attached.

    the password is equiplog

    Thank you
    Attached Files Attached Files
    Last edited by nsmjc; 02-13-2017 at 02:03 PM. Reason: SOLVED

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Adding new row keeping existing format all the wile keeping worksheet protected.

    Have you considered using an Excel "table" - which formats and copies formulas automatically
    - try in attached sheet (which includes a very simple example) by adding some values in row 5
    - column A retains BOLD format
    - column F retains formula and format
    - column G retains drop-down and conditional format
    Attached Files Attached Files
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Contributor
    Join Date
    11-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    100

    Re: Adding new row keeping existing format all the wile keeping worksheet protected.

    Yes I did try tables at first but those who make the decisions about important things
    like how a spreadsheet looks does not like the look of the tables as they refuse to
    understand the power of a table and what it could do for us.
    Hense I am looking for a way to add lines at the bottom of the work sheet while keeping the formating.

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Adding new row keeping existing format all the wile keeping worksheet protected.

    You could capture the data in an Excel table and link it to a "pretty" worksheet for "those who must be obeyed" (perhaps on a "trial" basis and then never get rid of it!!)
    Sounds to me like there are still a few dinosaurs roaming around locally

    I'm guessing that you will not be taking up my suggestion , so I'll sort the macro for you when I have a minute or two later today.
    It would be sensible to keep it simple - so do you want to add 1,5 or 10 lines each time (or even a different number) - it does not cost anything to have a few formatted lines, so you can afford to go high!! And if stegosaurus does not like untidy formatted empty rows, we could have another simple macro to delete those in one hit as and when required.

  5. #5
    Forum Contributor
    Join Date
    11-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    100

    Re: Adding new row keeping existing format all the wile keeping worksheet protected.

    You hit the nail on the head I dont beleive it really matters how many rows are there except when we go to print We cant have 500 blank rows printing.

    I belive 5 or 10 rows would be acceptable.

    I appreciate any help!!!!

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Adding new row keeping existing format all the wile keeping worksheet protected.

    a couple of questions:

    Your workbook contains 1 sheet, but the code is written to apply to all sheets in the workbook
    - are there multiple sheets and do the same number of rows get inserted into each sheet?

    Am I missing something here?
    - We want to add rows at the bottom of the data - so why are we inserting rows?
    - Is there any reason why we cannot just copy the formatting and data validation from the last 2 used rows and paste those to the next 2 rows?
    (- there do not seem to be any formulas to concern us in the worksheet - could be catered for though)

  7. #7
    Forum Contributor
    Join Date
    11-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    100

    Re: Adding new row keeping existing format all the wile keeping worksheet protected.

    Thanks for your help on this Macro. I was able to convince her the table is the most efficent way for this information to be entered.
    so I will not need anu further assistance with this as I am going to utlize the table featuer.

  8. #8
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Adding new row keeping existing format all the wile keeping worksheet protected.

    Excellent
    Thanks for the reps

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Keeping Duplicates Existing in All Columns
    By starlord0323 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-10-2017, 05:56 PM
  2. [SOLVED] Adding Numbers As Strings And Keeping Format
    By ejoneslor in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-24-2016, 07:13 AM
  3. Not showing zero values on a graph but keeping the existing format
    By wilksy in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 07-24-2013, 10:38 AM
  4. Copy entire worksheet from one workbook to another, keeping layout and format
    By SamsTechAnswers in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-02-2012, 08:53 PM
  5. Inserting a picture, keeping sheet protected
    By bibfocal68 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-29-2012, 04:45 AM
  6. Replies: 1
    Last Post: 02-14-2011, 04:59 PM
  7. Keeping groups open when workbook protected
    By lundgrendaniel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-12-2005, 11:06 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1