PDA

View Full Version : Copy & Pasting Formula to Cells in Another Sheet



smartbuyer
11-28-2013, 09:06 PM
Hi,

I have 2 sheets to work on. Sheet1 is a database of formula and Sheet2 is where I want the formula to be pasted to.

Sheet1 has the formula and the cell where it should go in Sheet2

I cant figure out how to execute the copy pasting one cell at a time from one sheet to another. Also, the number of formula to copy depends on what's on Sheet2

See attached. 10885

Thanks everyone!

jolivanes
11-28-2013, 09:49 PM
With a loop


Sub Try()
Dim a As String, c As Range
For Each c In Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
a = c.Value
Sheets("Sheet2").Range(a) = c.Offset(, -1).Value
Next c
End Sub

smartbuyer
11-28-2013, 09:57 PM
Hi,

It's close. It's doing what I wanted to do but

It seems to be deleting other data. I have attached the file that I am working on. 10886

Sheet1's name may change depending on user preferences.

It is important that data in Sheet1(budget) does not change except the addition of the formula to specific cells.

Also, if you help me figuring out how to drag the formula (dynamic cell range) offsetting it 11 places to the right?

jolivanes
11-28-2013, 10:48 PM
Sub Maybe_This()
Dim a As String, c As Range
For Each c In Range("H2:H" & Cells(Rows.Count, 8).End(xlUp).Row)
a = c.Value
Sheets(1).Range(a) = c.Offset(, 1).Value
Next c
End Sub

jolivanes
11-29-2013, 12:07 AM
Please read forums rules about cross posting