Change way VBA pastes formula

[jas1980]

I have a formula in P2 that creates a string formula the following macro then places it in to A5, if what is up to (2) is ran it works fine, up to (3) pastes before SendKeys runs so formula isn't active and, if I run to end with or with without (2) then protect is activated before formula.

Is there a better way to place and activate this formula in the whole range so sheet still protects?

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
wsheet.UNPROTECT Password:="TRADE"
Next wsheet
'(1)****************************************************************************************
Range("P2").Copy
Range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
SendKeys "{F2}"
SendKeys "{ENTER}"
'(2)*****************************************************************************************
Range("A5").Copy Range("A6:A12")
'(3)*****************************************************************************************
filetoopen = ThisWorkbook.Worksheets("CSV COLLECT").Range("V3")
Workbooks.Open fileName:=filetoopen
ActiveWorkbook.Close ("A5")
For Each wsheet In ActiveWorkbook.Worksheets
wsheet.PROTECT Password:="TRADE"
Next wsheet
Application.ScreenUpdating = True
Application.DisplayStatusBar = True

End Sub

 

[Joe4]

If you are just trying to enter the string in P2 as a formula in cell A5, then just replace this:

Range("P2").Copy
Range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
SendKeys "{F2}"
SendKeys "{ENTER}"

with this:

Dim frm As String
frm = Range("P2")
Range("A5").Formula = frm

 

[jas1980]

Amazing, sometimes finding a couple of lines fixes everything. Thankyou

 

[Joe4]

You are welcome!

Yes, I usually try to avoid using "SendKeys" in my VBA code whenever possible. Often times, they are other ways of accomplishing the task (like in this instance).

 

[jas1980]

You are welcome!

Yes, I usually try to avoid using "SendKeys" in my VBA code whenever possible. Often times, they are other ways of accomplishing the task (like in this instance).

Yes I did, I spent a couple of days trying to find an alternative but ended up here, is always good to learn new stuff : )

 

[Joe4]

Yes I did, I spent a couple of days trying to find an alternative but ended up here, is always good to learn new stuff : )

That is what we are here for. Always glad to help!

The trick with this one was setting to a variable first.
When I tried this first:

Range("A5").Formula = Range("P2")

it did not work.
So I just started experimenting, and found that setting it equal to a variable first worked.