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

 

[pincivma]

Hi Dante

Does your code copy the formulas directly across to column B which is 49 columns to the left?

 

[DanteAmor]

Hi Dante

Does your code copy the formulas directly across to column B which is 49 columns to the left?

 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("BA9", Range("BA" & Rows.Count).End(xlUp))
    For Each wData In MyData
        If wData.HasFormula = True Then
            wData.Copy Cells(wData.Row, "B")
        End If
    Next
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
    End With
End Sub

Review the code, change "B" by the desired column.
Just try.

 

[pincivma]

Hi Dante

I just tested your code and this is what happened. The cursor went to column B but instead of copying the formulas in column BA over to column B, it copied column E into column B. Since column E has no formulas, Column B then showed all zeros. For example, Cell B9 has formula =E9, Cell B10 has formula = E10 etc.

 

[DanteAmor]

Hi Dante

I just tested your code and this is what happened. The cursor went to column B but instead of copying the formulas in column BA over to column B, it copied column E into column B. Since column E has no formulas, Column B then showed all zeros. For example, Cell B9 has formula =E9, Cell B10 has formula = E10 etc.

the formula is copied but it takes the relative cells according to the formula in BA. It would be clearer if you explain with an example what formula you have in BA and how you want it to appear in B. You also do the following exercise: copy only one BA formula and paste it in B and comment on what you have in BA and how the formula was in B after the paste.

 

[DanteAmor]

More clarifications.
First and very important, from column BA - 49 the result is column D, so my macro in post #7 : Cells(wData.Row, "D").
But if you want the result in column B only change to Cells(wData.Row, "B").


So, if in cell BA9 you have the formula =BD9 and you copy the formula in B, in B will be the formula =E9.
If you want the formula =BD9 to appear in B, then you should not copy it, as you requested in post #1 :

ActiveCell.Select
Selection.Copy
ActiveCell.Offset (0, -49) .Select
ActiveSheet.Paste

Then try this too:

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

 

[Fluff]

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

If the formulas in BA are being deleted, you must have some other code at work, because the code I supplied does not delete anything.
Also Col B is blank because 49 columns left of BA is col D not col B.

To have the formulae in Col B simply change the offset to -51

 

[pincivma]

Hi Dante

MY mistake. I'm new to this so I do make a lot of mistakes. Your first macro "CopyPaste1" does work if I fix the column and not the row. Your "CopyPaste2" macro also woks if I do not fix column BA. I tested out both macros on a separate workbook with minimal rows and they both do the job. Today I will test it out on the workbook that I'm working on.

Hi Fluff

I will also retry your macro too and change the offset to -51. Maybe that might be the problem.

Thank both for being patient with me.

 

[DanteAmor]

Don't worry, it's a pleasure to help you. Thanks for the feedback.