Locking Formula

[kipper19]

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

 

[thespardian]

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

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

 

[kipper19]

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

 

[Michael M]

Try this one

Sub copy()
With Range("B1:B20")
    .Value = Range("A1:A20").Value
End With
End Sub

 

[thespardian]

Did you changed the sheet name? The Code suggested by Michael M will help you. Sorry i couldn't be helpful.

 

[Michael M]

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

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

 

[kipper19]

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

 

[kipper19]

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

 

[Michael M]

So maybe....

Sub copy()
With Sheets("Sheet2").Range("B1:B20")
    .Value = Sheets("Sheet2").Range("A1:A20").Value
End With
End Sub