+ Reply to Thread
Results 1 to 7 of 7

How do I prevent Macro from overwriting data

  1. #1
    Registered User
    Join Date
    12-04-2013
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    31

    Lightbulb How do I prevent Macro from overwriting data

    Hi all,

    This is my first post (of probably many!)

    I have created a Macro that copies and expands rows in Sheet 2 based on a value enter in a column in Sheet 1

    (see attached example)

    It works but I need some help fine tuning what I want to achieve:

    -After expanding the rows I want to enter data manually in the next column (Room Type),however each time I run the Macro it deletes the info I entered and creates a blank cell. In other words I do not want the Macro to populate column K. I've tried not entering column K on sheet one, but again if I create column K manually after the Macro is run - it dissapears on every running of the Macro - if that makes sense.
    -As you can see I also have a problem copying the Headers into from Sheet 1 into Sheet 2, when the Macro is run the first line populates where the headers should be, how do I get the headers?

    -Then one last question (for when the Macro runs as i want it to)
    If the value in Sheet 1 is reduces, what will happen to Sheet 2 where the K column should be constant.

    For example:

    Original value in Sheet 1 for number of rooms is 3
    In Sheet 2 it will populate 3 seperate lines
    I manually update Sheet 2 Column K for 3 Lines (Line 1:Standard,Line 2:Superior,Line 3:Deluxe)
    I then change the value in Sheet 1 to number of rooms 2
    Will the Macro keep Line 3 without data besides the manual input (Deluxe) or will it delete the entire row?

    Many thanks in advance!!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: How do I prevent Macro from overwriting data

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    12-04-2013
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: How do I prevent Macro from overwriting data

    Hi protonLeah,

    Thank you very much for you reply and time with the new macro - much appreciated.

    I still have the same problem though that each time the macro is run it clears all the sells in column K in HOTELEXT. Eventually this document will be hundreds of lines long and lines will be added contiuously.

    What i want is for the user to be able to add a line in HOTELBASIC, run the macro and then expand that row. all the previously entered data should still be there.

    could it be something to do with this part of the macro?

    Application.ScreenUpdating = False
    HOTELEXT.Cells.ClearContents
    HOTELEXT.Range("a1:K1").Value = Header

    Cheers,

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How do I prevent Macro from overwriting data

    Hi, MooseAUH,

    Please Login or Register  to view this content.
    means deleting what is on the sheet "MASTER SHEET-HOTELS EXTENDED",

    Please Login or Register  to view this content.
    will copy over the contents from Sheet "MASTER SHEET-HOTELS BASIC" to "MASTER SHEET-HOTELS EXTENDED".

    Please Login or Register  to view this content.
    will only stop the flickering on the screen.

    You should try running the code after commenting the two first lines (at least the first one deleting the contents) by putting an ' in front of them, using the symbol of the command bar in the VBE or by writing REM before them.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: How do I prevent Macro from overwriting data

    Delete the lines:
    ' HOTELEXT.Cells.ClearContents
    ' HOTELEXT.Range("a1:K1").Value = Header

    and the macro will APPEND the new data to the bottom of HOTELEXT sheet without deleting any previous data

  6. #6
    Registered User
    Join Date
    12-04-2013
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: How do I prevent Macro from overwriting data

    We are nearly there!

    Deleting the lines:
    Please Login or Register  to view this content.
    Does APPEND the new data to the bottom of HOTELEXT, however it is duplicating all previous entered data again

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: How do I prevent Macro from overwriting data

    I guess I don't know what you are trying to do. Sorry, maybe you should repost and start over.

+ 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. lock Subtotals to prevent overwriting.
    By gruf1968 in forum Excel General
    Replies: 1
    Last Post: 03-12-2010, 07:01 PM
  2. Prevent overwriting cells
    By MGT2000 in forum Excel General
    Replies: 0
    Last Post: 10-29-2008, 02:11 PM
  3. Prevent overwriting file...
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2008, 11:47 AM
  4. How to prevent overwriting of file ?
    By asitagrawal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-25-2008, 10:23 AM
  5. Prevent user from overwriting file
    By peter.thompson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-14-2006, 08:35 AM

Tags for this Thread

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