Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TROUBLE COPYING AND PASTING FORMULAS | Excel Worksheet Functions | |||
Copying/Pasting Formulas | Excel Discussion (Misc queries) | |||
Copying and pasting multiple formulas, it it possible? | Excel Discussion (Misc queries) | |||
Copying & pasting formulas | Excel Worksheet Functions | |||
pasting formulas into a selection | Excel Worksheet Functions |