VBA Code Editing Help

[Vishaal]

Thanks in advance,

i have the following VBA code,

Its o.k. no change required

Range("IX10").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-253]=""Na"",R[1]C[-253]=""Yes""),COUNTIF(R2C[-253]:RC[-253],""Na"")-SUM(R1C:R[-1]C),"""")"
    Range("IX10").Select
    Selection.Copy
    Range("IX11:IX1798").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Second started, its in part
I want to run this code from “IY10” to “SO10”, but if I will run this code at once, Excel will be start the error “Not responding” due to heavy work of code, so please provide the process how can we use the code without making excel “not responding”

Range("IX10:IX1798").Select
    Selection.Copy
    Range("IY10:JW10").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("IY10:JW1798").Select
    Selection.Copy
    Range("IY10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("IX8").Select
 
    Range("IX10:IX1798").Select
    Selection.Copy
    Range("JX10:KV10").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("JX10:KV1798").Select
    Selection.Copy
    Range("JX10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("IX8").Select
    Range("IX10:IX1798").Select
    Selection.Copy
    Range("KW10:LU10").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("KW10:LU1798").Select
    Selection.Copy
    Range("KW10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("IX8").Select
    Range("IX10:IX1798").Select
    Selection.Copy
    Range("LV10:MT10").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("LV10:MT1798").Select
    Selection.Copy
    Range("LV10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("IX8").Select
    Range("IX10:IX1798").Select
    Selection.Copy
    Range("MU10:NS10").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("MU10:NS1798").Select
    Selection.Copy
    Range("MU10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("IX8").Select
    Range("IX10:IX1798").Select
    Selection.Copy
    Range("NT10:OR10").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("NT10:OR1798").Select
    Selection.Copy
    Range("NT10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("IX8").Select
 
    Range("IX10:IX1798").Select
    Selection.Copy
    Range("OS10:PQ10").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("OS10:PQ1798").Select
    Selection.Copy
    Range("OS10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("IX8").Select
    Range("IX10:IX1798").Select
    Selection.Copy
    Range("PR10:QP10").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("PR10:QP1798").Select
    Selection.Copy
    Range("PR10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("IX8").Select
    Range("IX10:IX1798").Select
    Selection.Copy
    Range("QQ10:RO10").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("QQ10:RO1798").Select
    Selection.Copy
    Range("QQ10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("IX8").Select
 
    Range("IX10:IX1798").Select
    Selection.Copy
    Range("RP10:SO10").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("RP10:SO1798").Select
    Selection.Copy
    Range("RP10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("IX8").Select

pls help

 

[DanteAmor]

Try this

Sub test6()
  Application.ScreenUpdating = False
  Range("IX10:IX1798").Copy
  Range("IY10:JW10").PasteSpecial xlPasteAll
  Range("IY10:JW10").PasteSpecial xlPasteValues
  
  Range("JX10:KV10").PasteSpecial xlPasteAll
  Range("JX10:KV10").PasteSpecial xlPasteValues


  Range("KW10:LU10").PasteSpecial xlPasteAll
  Range("KW10:LU10").PasteSpecial xlPasteValues


  Range("LV10:MT10").PasteSpecial xlPasteAll
  Range("LV10:MT10").PasteSpecial xlPasteValues
  
  Range("MU10:NS10").PasteSpecial xlPasteAll
  Range("MU10:NS10").PasteSpecial xlPasteValues
  
  Range("NT10:OR10").PasteSpecial xlPasteAll
  Range("NT10:OR10").PasteSpecial xlPasteValues
  
  Range("OS10:PQ10").PasteSpecial xlPasteAll
  Range("OS10:PQ10").PasteSpecial xlPasteValues
  
  Range("PR10:QP10").PasteSpecial xlPasteAll
  Range("PR10:QP10").PasteSpecial xlPasteValues
  
  Range("QQ10:RO10").PasteSpecial xlPasteAll
  Range("QQ10:RO10").PasteSpecial xlPasteValues
  
  Range("RP10:SO10").PasteSpecial xlPasteAll
  Range("RP10:SO10").PasteSpecial xlPasteValues


  Application.ScreenUpdating = True
  Application.CutCopyMode = False
  
  MsgBox "Done"
End Sub

 

[Vishaal]

Thanks bro

 

[DanteAmor]

Youre welcome! thanks for the feedback

 

[Vishaal]

Hi DanteAmor Sir Ji,

Its possible for mention code in thread

1. It will copy the formula
2. After it is paste IX10:IX1798
3. Now for the range IY10 to SO10 automatically paste the formula as per following details
(I) paste in five coloumn
(Ii) copy that five coloumn and paste values
After that it will do for next five coloumns

Because its time taken for counting the ix to so alphabeats