I am reasonably new to VBA... I have built a report and macro but am stuck copy and pasting a formula from one sheet to another.
This is the part of the code I am having problems with...
Worksheets("Data Checks").Range("B11").Copy Worksheets("Items By Location").Range("Y2")
So Id like to copy the following formula from Worksheets("Data Checks").Range("B11") to Worksheets("Items By Location").Range("Y2")
The formula is as follows...
=OR(LEFT('Items By Location'!D2, 4)="JV05", LEFT('Items By Location'!D2, 4)="JVAW", LEFT('Items By Location'!D2, 5)="HARHW")
But when the macro runs it pastes as...
=OR(LEFT('Items By Location'!#REF!, 4)="JV05", LEFT('Items By Location'!#REF!, 4)="JVAW", LEFT('Items By Location'!#REF!, 5)="HARHW")
I need it to keep the 'D2' which will then be auto filled down to the bottom of the report.
I know it may be easier to type out the formula but I'd like this formula to be amendable without going into VBA.
Any help or ideas would be greatly appreciated
Many thanks Harvey
If you copy the cell, the formula will adjust since it's a relative reference. To avoid that, assign the formula string directly from one to the other:
Worksheets("Items By Location").Range("Y2").Formula = Worksheets("Data Checks").Range("B11").Formula