Locking Formula

kipper19

New Member
Joined
Apr 12, 2014
Messages
49
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

Board Regular
Joined
Aug 31, 2012
Messages
53
Office Version
2016, 2013
Platform
Windows
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
 

kipper19

New Member
Joined
Apr 12, 2014
Messages
49
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,327
Office Version
2013
Platform
Windows
Try this one

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

thespardian

Board Regular
Joined
Aug 31, 2012
Messages
53
Office Version
2016, 2013
Platform
Windows
Did you changed the sheet name? The Code suggested by Michael M will help you. Sorry i couldn't be helpful.
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,327
Office Version
2013
Platform
Windows
@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
 

kipper19

New Member
Joined
Apr 12, 2014
Messages
49
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

New Member
Joined
Apr 12, 2014
Messages
49
I have it working, might need some more thought on it though but thanks for your help
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,327
Office Version
2013
Platform
Windows
So maybe....
Code:
Sub copy()
With Sheets("Sheet2").Range("B1:B20")
    .Value = Sheets("Sheet2").Range("A1:A20").Value
End With
End Sub
 

Forum statistics

Threads
1,086,224
Messages
5,388,554
Members
402,123
Latest member
taps86

Some videos you may like

This Week's Hot Topics

Top