VBA Code to Copy formula from first row to the cells below

[msharma864512]

Hi,

I am looking for a code to copy formula from first row to the cells below and after pasting the formula, the all the cells below should have paste special values only, and the formula should not be visible, but the formula should be retained in the first cell from where the formula was copied.

Details are in the attached spreadsheet.

Regards,
Manish

 

[Luke M]

Try this.

Sub ExampleCode()
    Dim rngChange As Range
    
    'What table and column?
    Set rngChange = Worksheets("Sheet1").ListObjects("Table1").ListColumns("EBITDA Margin").DataBodyRange
    
    Application.ScreenUpdating = False
    
    With rngChange
        .Formula = "=[@EBITDA]/[@Revenue]"
        .Copy
        .PasteSpecial xlPasteValues
        
        'Leave formula in first row
        .Cells(1).Formula = "=[@EBITDA]/[@Revenue]"
    End With
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[msharma864512]

Try this.

Sub ExampleCode()
    Dim rngChange As Range
   
    'What table and column?
    Set rngChange = Worksheets("Sheet1").ListObjects("Table1").ListColumns("EBITDA Margin").DataBodyRange
   
    Application.ScreenUpdating = False
   
    With rngChange
        .Formula = "=[@EBITDA]/[@Revenue]"
        .Copy
        .PasteSpecial xlPasteValues
       
        'Leave formula in first row
        .Cells(1).Formula = "=[@EBITDA]/[@Revenue]"
    End With
   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Thanks Luke,

The formula works perfectly fine, but its not working If i want to add any conditions.

Example is in the attached spreadsheet.

FYI, the formula I am trying to use have multiple conditions and formula is to pull values from a database.

Regards,
Manish

 

[msharma864512]

Thanks Luke,

The formula works perfectly fine, but its not working If i want to add any conditions.

Example is in the attached spreadsheet.

FYI, the formula I am trying to use have multiple conditions and formula is to pull values from a database.

Regards,
Manish

Additionally, the table wherein I need to write up this code is derived from Power Query, hence when I am trying to write a simple code to see if that is working in that table. I am getting an error. I have attached the screenshot of the error.

Regards,
Manish

 

[msharma864512]

Additionally, the table wherein I need to write up this code is derived from Power Query, hence when I am trying to write a simple code to see if that is working in that table. I am getting an error. I have attached the screenshot of the error.

Regards,
Manish

I have attached the sample excel as well wherein I am facing the issue.

I want the formula to be written for Value Column in Data Flat File Tab

 

[msharma864512]

I have attached the sample excel as well wherein I am facing the issue.

I want the formula to be written for Value Column in Data Flat File Tab

Hi Luke,

Please see if you can help with this.

Regards,
Manish

 

[Luke M]

To VBA, the name of the table is just "CapIQFinancialsData". Changing that one line then to

Set rngChange = Worksheets("Data Flat File").ListObjects("CapIQFinancialsData").ListColumns("Value").DataBodyRange

and the whole thing works again.

You did several responses in a row, so I'm not completely sure what problems you were still having. This should get you going again, at least.

 

[msharma864512]

To VBA, the name of the table is just "CapIQFinancialsData". Changing that one line then to

Set rngChange = Worksheets("Data Flat File").ListObjects("CapIQFinancialsData").ListColumns("Value").DataBodyRange

and the whole thing works again.

You did several responses in a row, so I'm not completely sure what problems you were still having. This should get you going again, at least.

Luke, I tried that, but it was not working.

Attached here is the latest attachment, wherein I want that code, column is highlighted in yellow in Data Flat File tab.

 

[msharma864512]

To VBA, the name of the table is just "CapIQFinancialsData". Changing that one line then to

Set rngChange = Worksheets("Data Flat File").ListObjects("CapIQFinancialsData").ListColumns("Value").DataBodyRange

and the whole thing works again.

You did several responses in a row, so I'm not completely sure what problems you were still having. This should get you going again, at least.

Thanks Luke,

This one is working perfectly now