Copy Paste macro not working

pincivma

Board Regular
Joined
Dec 12, 2004
Messages
199
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
It is not clear where you want to paste. But check the following example:

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

Board Regular
Joined
Dec 12, 2004
Messages
199
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case try
Code:
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

Board Regular
Joined
Dec 12, 2004
Messages
199
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Try this

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

Board Regular
Joined
Dec 12, 2004
Messages
199
In that case try
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,295
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top