Results 1 to 9 of 9

Thread: Locking Formula

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Locking Formula

    Hi All, this question I asked a long time ago and didn't really get on top of it, I'm assuming I would need some VBA code to accomplish this

    Looking for some help on this one, if I have a series of numbers in cells A1:A20, then I would like these numbers to be printed in there corresponding cells B1:B20, Now the numbers in cells A1:A20 can be changed but the numbers in B1:B20 will not change from the original numbers so that I have some baseline figures to work with

    Hoping someone can assist

  2. #2
    New Member
    Join Date
    Aug 2012
    Posts
    45
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Locking Formula

    I am not good at VBA. But i think it will help you. Assuming that you are in Sheet1. If your sheet name is different then change it accordingly please

    Code:
    Sub copy()ThisWorkbook.Sheets("Sheet1").Range("B1:B20").Value = ThisWorkbook.Sheets("Sheet1").Range("A1:A20").Value
    End Sub

  3. #3
    New Member
    Join Date
    Apr 2014
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Locking Formula

    The spardian, thanks but that didnt work, pasted the code in a module changed the cell reference etc but the code just stays red in error mode

  4. #4
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,885
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Locking Formula

    Try this one

    Code:
    Sub copy()
    With Range("B1:B20")
        .Value = Range("A1:A20").Value
    End With
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  5. #5
    New Member
    Join Date
    Aug 2012
    Posts
    45
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Locking Formula

    Did you changed the sheet name? The Code suggested by Michael M will help you. Sorry i couldn't be helpful.
    Last edited by thespardian; Oct 21st, 2019 at 03:03 AM.

  6. #6
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,885
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Locking Formula

    @thespardian
    I'm guessing the OP used the code as posted, which will fail because the action appears on the sub line

    Code:
    Sub copy()ThisWorkbook.Sheets("Sheet1").Range("B1:B20").Value = ThisWorkbook.Sheets("Sheet1").Range("A1:A20").Value
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  7. #7
    New Member
    Join Date
    Apr 2014
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Locking Formula

    Michael, thanks but im using 2 sheets so the code is for sheet 2, sorry I didnt mention that, Spardian yes I changed the sheet number to 2, does this code go in the tab code or in a module? also im guessing with your code Michael I would need to stipulate sheet2 etc, apologies for the dumb questions

  8. #8
    New Member
    Join Date
    Apr 2014
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Locking Formula

    I have it working, might need some more thought on it though but thanks for your help

  9. #9
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,885
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Locking Formula

    So maybe....
    Code:
    Sub copy()
    With Sheets("Sheet2").Range("B1:B20")
        .Value = Sheets("Sheet2").Range("A1:A20").Value
    End With
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •