Copy Paste macro not working

[pincivma]

Hi There

I got this macro from Mr Excel forum but I forgot who gave it to me. I have a bug on the line that says: If MyData(r, 1).HasFormula = True Then

The bug says "Object Required" Can someone fix this for me? Below is the macro

Sub CopyPaste1()

Application.ScreenUpdating = False
Dim i As Long, MyData As Variant
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
MyData = Range("AB9:AB" & Cells(Rows.Count, "AB").End(xlUp).Row)
For r = 9 To UBound(MyData)

If MyData(r, 1).HasFormula = True Then
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -49).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 49).Select
End If
Next r

With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
End With

 

[Fluff]

As MyData is an array
a) you cannot use .HasFormula as that is a property of a range object.
b) there are no formulas

What are you trying to do?

 

[DanteAmor]

It is not clear where you want to paste. But check the following example:

Sub CopyPaste1()


    Application.ScreenUpdating = False
    Dim i As Long, MyData As Range, wData As Range
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
    End With
    
    Set MyData = Range("AB9", Range("AB" & Rows.Count).End(xlUp))
    For Each wData In MyData
        If wData.HasFormula = True Then
            wData.Copy
            Cells(wData.Row, "AC").PasteSpecial xlValues
        End If
    Next
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
    End With


End Sub

 

[pincivma]

As MyData is an array
a) you cannot use .HasFormula as that is a property of a range object.
b) there are no formulas

What are you trying to do?

I am trying to copy formulas from BA9 to B429 to cells that are 49 columns to the left. But I only want to copy cells that have formulas in them and skip those cells that have text. My macro below does work but it takes a long time to run. I want a faster macro.


Range("BA9").Select
Do
ActiveCell.Select
If ActiveCell.HasFormula Then
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -49).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 49).Select
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.FormulaR1C1 = "."

 

[Fluff]

In that case try

Sub pincivma()
   Dim Rng As Range
   For Each Rng In Range("BA9:BA429").SpecialCells(xlFormulas).Areas
      Rng.Offset(, -49).Formula = Rng.Formula
   Next Rng
End Sub

 

[pincivma]

Hi Flufff

Dante's code as seen above copies formulas but pastes values and not formulas. I will try your macro and see how it works.

 

[DanteAmor]

Try this

 Sub CopyPaste1()




    Application.ScreenUpdating = False
    Dim i As Long, MyData As Range, wData As Range
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
    End With
    
    Set MyData = Range("[COLOR=#0000ff]BA9[/COLOR]", Range("[COLOR=#0000ff]BA[/COLOR]" & Rows.Count).End(xlUp))
    For Each wData In MyData
        If wData.HasFormula = True Then
            wData.Copy Cells(wData.Row, "[COLOR=#0000ff]D[/COLOR]")
        End If
    Next
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
    End With




End Sub

 

[pincivma]

Hi Dante

I will also try your macro and see how it works

 

[pincivma]

In that case try

Sub pincivma()
   Dim Rng As Range
   For Each Rng In Range("BA9:BA429").SpecialCells(xlFormulas).Areas
      Rng.Offset(, -49).Formula = Rng.Formula
   Next Rng
End Sub

Hi Fluff

I ran your macro and it does not work. Your macro deletes formulas in the range("BA9:BA429") and when I check column B which is 49 columns to the left, the formulas are not there but blanks

 

[DanteAmor]

Hi Dante

I will also try your macro and see how it works

Of course, take your time