CODEWITHSUNDEEP
excelvba
Graham Walters
Graham Walters

Reputation: 2064

Excel Macro: Set the formula of a cell

I'm writing a macro to insert a row into all selected sheets and then set some of the values to equal the values in another sheet. I've managed to insert the row using the following code, but I'm getting stuck trying to set the values. Without a macro, I'd simply enter =InputC7 Input being the name of the first sheet in the workbook.

Sub InsertRows()
'
' InsertRows Macro
' Inserts rows into all selected sheets at the same position
'

    Dim CurrentSheet As Object

    ' Loop through all selected sheets.
    For Each CurrentSheet In ActiveWindow.SelectedSheets
        ' Insert 1 row at row 7 of each sheet.
        CurrentSheet.Range("a7:a7").EntireRow.Insert
        CurrentSheet.Range("c7").Value =Input!C7 'this is not working
    Next CurrentSheet
End Sub

Upvotes: 2

Views: 14546

Answers (2)

Ripster
Ripster

Reputation: 3585

If you just want the value from the sheet named "Input" you can do this:

CurrentSheet.Range("C7").Value = Sheets("Input").Range("C7").Value

If you want the formula "=Input!C7" you can do this:

CurrentSheet.Range("C7").Formula = "=Input!C7"

Upvotes: 4

stenci
stenci

Reputation: 8481

You need to use the Formula instead of the Value property, and the text should be quoted:

CurrentSheet.Range("c7").Formula "=Input!C7"

Upvotes: 2

Related Questions