Locking Formula

kipper19

Board Regular
Joined
Apr 12, 2014
Messages
92
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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
 
Upvote 0
Try this one

Code:
Sub copy()
With Range("B1:B20")
    .Value = Range("A1:A20").Value
End With
End Sub
 
Upvote 0
Did you changed the sheet name? The Code suggested by Michael M will help you. Sorry i couldn't be helpful.
 
Last edited:
Upvote 0
@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
 
Upvote 0
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
 
Upvote 0
I have it working, might need some more thought on it though but thanks for your help
 
Upvote 0
So maybe....
Code:
Sub copy()
With Sheets("Sheet2").Range("B1:B20")
    .Value = Sheets("Sheet2").Range("A1:A20").Value
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top