Loop - copy & paste

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
543
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
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)
 
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

Code:
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
Code:
=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)
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
Code:
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
 
Upvote 0
Hi
you are right it passes the first row values only
Then try this
Code:
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
 
Upvote 0
Not pasting value

Just checked

Pls check
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
For 1659 rows for loop is taking very long time!!!
Any way If so change Resize thing to (1659-12+1)
 
Last edited:
Upvote 0
This is dynamic

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top