Loop - copy & paste

[Vishaal]

Hi,

Thanks in advance,

I am copying the formula from Range ("A12:A24")
And after select the next range ("B12") and paste it
Again select the next range ("C12") and paste it
Again select the next range ("D12") and paste it

How can i do it through loop or vba (Coloumn B to AY)

 

[Vishaal]

Well
I don't know what the formulas are in your sheet
and what is yous the excel version, here in 2013 work fine
Any way try

Sub
 copy_formula()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    With ActiveSheet
    Set Rng = Range("a12:a24")
   Rng.Copy
        For i = 2 To 50
           .Cells(12, i).PasteSpecial Paste:=xlPasteFormulas
        Next
        Application.Calculation = xlCalculationAutomatic
 With Range("b12:ay24")
    .Value = .Value
    End With
    End With
   Application.ScreenUpdating = True
End Sub

Thanks bro

Excel 2007
32bit
Formula is

=if(and(n12="na",n13="yes"),countif(n$2:n12,"na")-sum(ee$1:ee11),"")

I have no problem with your code first part, its working

I also just want that second part will also work like your code first part, so excel will not hang if i will work with a12:a1629 to b12:km1629

If both work will be done one coloumn by one coloumn

I mean your code first part have pasted the formula, now that coloumn will copy its formula and paste as values after next coloumn

Your code working fine if i have approx 20 to 60 coloumn but when i am trying it with large amount, my excel hang

Hope my point is clear to u

I want that
Rng = Range("a12:a24")
Cells(12, i).PasteSpecial Paste:=xlPasteFormulas (it will paste the formula in coloumn b then c then d)
After this it copy that coloumn (not loop will copy b and paste values)

 

[Vishaal]

I mean

Range a12:a24 copy
Paste formula on b 12
Copy b12:b24
Paste values

Range a12:a24 copy
Paste formula on c 12
Copy c12:c24
Paste values

Range a12:a24 copy
Paste formula on d 12
Copy d12:d24
Paste values


Range a12:a24 copy
Paste formula on e 12
Copy e12:e24
Paste values


Loop will work like this

 

[mohadin]

Sub copy_formula()
    Application.ScreenUpdating = False
    With ActiveSheet
        Set Rng = Range("a12:a24")
        Rng.Copy
        For i = 2 To 50
            .Cells(12, i).PasteSpecial Paste:=xlPasteFormulas
            With .Cells(12, i)
                .Value = .Value
            End With
        Next
    End With
End Sub

 

[Vishaal]

Its working but not pasting values

 

[mohadin]

Hi
you are right it passes the first row values only
Then try this

Sub copy_formula()
    Application.ScreenUpdating = False
    With ActiveSheet
        Set Rng = Range("a12:a24")
        Rng.Copy
        For i = 2 To 50
            .Cells(12, i).PasteSpecial Paste:=xlPasteFormulas
            With .Cells(12, i).Resize(13)
                .Value = .Value
            End With
        Next
    End With
End Sub

 

[Vishaal]

Not pasting value

Just checked

Pls check

 

[Vishaal]

Checked

I have used a12:a1659 to PM

Your code paste the values but only for cells A12:A24, i mean in all coloumn row 12 to 24 only
After that its pasting the formula

 

[mohadin]

For 1659 rows for loop is taking very long time!!!
Any way If so change Resize thing to (1659-12+1)

 

[mohadin]

This is dynamic

Sub copy_formula()
    Application.ScreenUpdating = False
    With ActiveSheet
        lr = .Cells(Rows.Count, "a").End(xlUp).Row
        Set Rng = Range("a12:a" & lr)
        Rng.Copy
        For i = 2 To 51
            .Cells(12, i).PasteSpecial Paste:=xlPasteFormulas
            With .Cells(12, i).Resize(lr + 1)
                .Value = .Value
            End With
        Next
    End With
    Application.ScreenUpdating = True
End Sub

 

[Vishaal]

Thanks bro