kds
03-20-2018, 06:01 AM
hello
I made about 9000 formulas in one sheet and each formula contains index, concatenate, if.wrong,... all the formulas refer to a database and everything works fine. There are 2 other sheets with the same amount of formulas. The file is 30 Mb.
To make the file smiler, i replaced in every formula = with "= (so it would be considered as text, the file becomes a lot smaller). I copied all this 1000 cells below so i can copy-paste it in the range i want using a macro. Then, also using a macro: every "= must be changed in =, followed by a copy-paste (only value)
However, when i do this and the macro is recording: excel does what i want it to do. But when i just want to run this macro it doesn't. Result: it copie-paste all the formulas beginning with "= (see attachement)
Can somebody help me? Thanks!
Sub B_Lokalen()
'
' B_Lokalen Macro
'
'
Range("A1:EI527").Select
Selection.ClearContents
Range("A1000:EI1526").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1:EI527").Select
Selection.Replace What:="""=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1:EI527").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub
I made about 9000 formulas in one sheet and each formula contains index, concatenate, if.wrong,... all the formulas refer to a database and everything works fine. There are 2 other sheets with the same amount of formulas. The file is 30 Mb.
To make the file smiler, i replaced in every formula = with "= (so it would be considered as text, the file becomes a lot smaller). I copied all this 1000 cells below so i can copy-paste it in the range i want using a macro. Then, also using a macro: every "= must be changed in =, followed by a copy-paste (only value)
However, when i do this and the macro is recording: excel does what i want it to do. But when i just want to run this macro it doesn't. Result: it copie-paste all the formulas beginning with "= (see attachement)
Can somebody help me? Thanks!
Sub B_Lokalen()
'
' B_Lokalen Macro
'
'
Range("A1:EI527").Select
Selection.ClearContents
Range("A1000:EI1526").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1:EI527").Select
Selection.Replace What:="""=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1:EI527").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub