Copying formulas in range and pasting in a selection
 

I cannot seem to figure out what is wrong with the following formula. I want
to select a range of cells, copy only the formulas in that range and then
paste only the formulas in the range("W8:BT60").

When I run this, if there are values in cells not containing formulas in the
destination range, the values disappear. Any help is appreciated. Thank you.

Range("GradeFormulas_ITC105").Select
Range("GradeFormulas_ITC105").Copy
Range("W8:BT8").Select
Range("W8:BT8").PasteSpecial xlPasteFormulas, xlNone,
SkipBlanks:=False, Transpose:=False
Selection.AutoFill Destination:=Range("W8:BT60"), Type:=xlFillCopy
Range("A8").Select

Copying formulas in range and pasting in a selection
 

The paste special will replace everything in the destination range.

To not overwrite values in the destination range you need to check each cell
for a value.

Something like this

Sub test()

counter = 0
For Each cell In Range("Source")
counter = counter + 1
If Range("A1").Offset(0, counter).HasFormula = True Or
Range("A1").Offset(0, counter) = Empty Then
Range("A1").Offset(0, counter) = cell.Formula
End If
Next

End Sub

I am assuming that your source and destination ranges are the same size.
This will not overwrite values in the destination, only cells with formulas
or empty cells.

Change the "Source" to your source name and use V8 for your offset start.
--
If this helps, please remember to click yes.


"Billy B" wrote:

I cannot seem to figure out what is wrong with the following formula. I want
to select a range of cells, copy only the formulas in that range and then
paste only the formulas in the range("W8:BT60").

When I run this, if there are values in cells not containing formulas in the
destination range, the values disappear. Any help is appreciated. Thank you.

Range("GradeFormulas_ITC105").Select
Range("GradeFormulas_ITC105").Copy
Range("W8:BT8").Select
Range("W8:BT8").PasteSpecial xlPasteFormulas, xlNone,
SkipBlanks:=False, Transpose:=False
Selection.AutoFill Destination:=Range("W8:BT60"), Type:=xlFillCopy
Range("A8").Select

Copying formulas in range and pasting in a selection
 

One note. The first version copies the exact formula and would not preserve
relative references. To keep the relative references intact you would need
to change the code like this. This appears to be more like your original
intent

Sub test()

counter = 0
For Each cell In Range("Source")
counter = counter + 1
cell.Copy
If Range("A1").Offset(0, counter).HasFormula = True Or
Range("A1").Offset(0, counter) = Empty Then
Range("A1").Offset(0, counter).PasteSpecial xlPasteFormulas
End If
Next

End Sub
--
If this helps, please remember to click yes.


"Paul C" wrote:

The paste special will replace everything in the destination range.

To not overwrite values in the destination range you need to check each cell
for a value.

Something like this

Sub test()

counter = 0
For Each cell In Range("Source")
counter = counter + 1
If Range("A1").Offset(0, counter).HasFormula = True Or
Range("A1").Offset(0, counter) = Empty Then
Range("A1").Offset(0, counter) = cell.Formula
End If
Next

End Sub

I am assuming that your source and destination ranges are the same size.
This will not overwrite values in the destination, only cells with formulas
or empty cells.

Change the "Source" to your source name and use V8 for your offset start.
--
If this helps, please remember to click yes.


"Billy B" wrote:

I cannot seem to figure out what is wrong with the following formula. I want
to select a range of cells, copy only the formulas in that range and then
paste only the formulas in the range("W8:BT60").

When I run this, if there are values in cells not containing formulas in the
destination range, the values disappear. Any help is appreciated. Thank you.

Range("GradeFormulas_ITC105").Select
Range("GradeFormulas_ITC105").Copy
Range("W8:BT8").Select
Range("W8:BT8").PasteSpecial xlPasteFormulas, xlNone,
SkipBlanks:=False, Transpose:=False
Selection.AutoFill Destination:=Range("W8:BT60"), Type:=xlFillCopy
Range("A8").Select

Copying formulas in range and pasting in a selection
 

The code that allows relative references works fine if I only wanted to copy
formulas to one row because in the loop it looks like the reference always
goes back to A1.

What I need to do is copy the formulas down 24 rows starting on at V8 on the
worksheet.

Thanks

"Paul C" wrote:

One note. The first version copies the exact formula and would not preserve
relative references. To keep the relative references intact you would need
to change the code like this. This appears to be more like your original
intent

Sub test()

counter = 0
For Each cell In Range("Source")
counter = counter + 1
cell.Copy
If Range("A1").Offset(0, counter).HasFormula = True Or
Range("A1").Offset(0, counter) = Empty Then
Range("A1").Offset(0, counter).PasteSpecial xlPasteFormulas
End If
Next

End Sub
--
If this helps, please remember to click yes.


"Paul C" wrote:

The paste special will replace everything in the destination range.

To not overwrite values in the destination range you need to check each cell
for a value.

Something like this

Sub test()

counter = 0
For Each cell In Range("Source")
counter = counter + 1
If Range("A1").Offset(0, counter).HasFormula = True Or
Range("A1").Offset(0, counter) = Empty Then
Range("A1").Offset(0, counter) = cell.Formula
End If
Next

End Sub

I am assuming that your source and destination ranges are the same size.
This will not overwrite values in the destination, only cells with formulas
or empty cells.

Change the "Source" to your source name and use V8 for your offset start.
--
If this helps, please remember to click yes.


"Billy B" wrote:

I cannot seem to figure out what is wrong with the following formula. I want
to select a range of cells, copy only the formulas in that range and then
paste only the formulas in the range("W8:BT60").

When I run this, if there are values in cells not containing formulas in the
destination range, the values disappear. Any help is appreciated. Thank you.

Range("GradeFormulas_ITC105").Select
Range("GradeFormulas_ITC105").Copy
Range("W8:BT8").Select
Range("W8:BT8").PasteSpecial xlPasteFormulas, xlNone,
SkipBlanks:=False, Transpose:=False
Selection.AutoFill Destination:=Range("W8:BT60"), Type:=xlFillCopy
Range("A8").Select