Replace Range formula in VBA

[elman]

Hello everybody!


I m new in vba so apologies if the question is easy or is already answered.


I want to:
-copy data from a sheet
-paste in another sheet (different .xlsx)
-copy some formulas from another sheet to the above sheet to make the calculations


For this i want to replace "=" with "#" to move formulas to the sheet


The question is: When i run the macro i get an error at ---> str1 = Range("EH2:FU2")


When i am deleting the rows with the strings, ranges etc in module the macro runs fine.


Below is the macro.


Thank you in advance.


Br
Elma


--------------------------------------------------------------------------
Sub copypaste()


Dim str1 As String
Dim str2 As String


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


'copy-paste data from query


Sheets("DATA_A").Select
Range(Cells(2, 1), Cells(50000, 137)).Clear


Windows("Query.xlsx").activate
Sheets("DATA").Select
Range(Cells(2, 1), Cells(50000, 137)).Copy
Windows("EOC07_16.xlsm").activate
Sheets("DATA_A").Select
Cells(2, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


'copy formulas from admin sheet


Sheets("DATA_A").Select
Range("EH2:FU50000").Clear
Sheets("Admin").Select
str1 = Range("EH2:FU2")
srt1 = Replace(str1, "=", "#")
Range("EH2:FU2").Copy
str2 = Range("EH2:FU50000")
srt2 = Replace(str2, "=", "#")
Sheets("DATA_A").Select
Range("EH2:FU50000").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.Copy
Range("EH2:FU50000").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Application.Calculation = xlCalculationAutomatic


End Sub
--------------------------------------------------------------------------

 

[Noodleski]

brain meltdown.

second attempt:

wouldn't it be easier to have something like instead of worrying about the '=' and '#'?
Range("EH2:FU50000").formula = "VBA version of your formula goes here. Record it for correct syntax"

 

[elman]

brain meltdown.

second attempt:

wouldn't it be easier to have something like instead of worrying about the '=' and '#'?
Range("EH2:FU50000").formula = "VBA version of your formula goes here. Record it for correct syntax"

hehe I guess u r wright its a meltdown

I'll try it! Thank you very much!

 

[Noodleski]

Just to be clear: it was my brain melting down. My first attemted reply was high grade stupidity..