Copying Formulas to a Dynamic Range

[DSerr77]

I am trying to find a piece of code that will neable me to automatically copy formulas from a range of cells (that will change) into a given range of cells (that will also change). I am adding data to the bottom of a list and need to paste a group of look up formulas to the left. The number of rows will always be different as well as the location of the formulas. Any help is much appreciated.

PS. I have looked through some other posts relating to this but they were not working out for me. Thanks.

 

[SkipVought]

Hi,

Well we do need a bit more info. There are all kinds of ways.

What is the structure of the range you are copying from (headings, rows, columns???)

What is the logic for the range you want to copy to?

Skip,

_[red][/red]

 

[DSerr77]

Here is what I have put together so far.

Sub CFD()
'
' CFD Macro
' Macro recorded 12/5/2006 by Frontier
'
Dim xcol As String
Dim ycol As String
Dim ROS As Integer
Dim CF As Range
'
Set xcol = Columns(4).Count
Set ycol = Columns(5).Count
Set ROS = ycol - xcol
Range("C2:E2").Select
Selection.Copy
Set CF = ActiveCell.Address(xcol + 1, 4)
CF.Offset(1, 2).Resize("ROS", -4).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'
'
End Sub

I have 4 columns of formulas then 5 columns of data. When I append the table I have more rows in the data columns then formulas in the formula columns. So I am trying to copy the formulas then paste them into the exact number of rows.

 

[DSerr77]

Here is an exzample of the worksheet's structure:


Name Amount 1 Amount 2
Test 1 1 Test 1 Test 1
Test 2 2 Test 2 Test 2
Test 3 3 Test 3 Test 3
Test 4 4 Test 4 Test 4
Test 5 5 Test 5 Test 5
Test 6 6 Test 6 Test 6
Test 7 7 Test 7 Test 7
Test 8 8 Test 8 Test 8
Test 9 9 Test 9 Test 9
Test 0 10 Test 10 Test 10
Test 1 11 Test 11 Test 11
Test 2 12 Test 12 Test 12
Test 3 13 Test 13 Test 13
Test 4 14 Test 14 Test 14
Test 5 15 Test 15 Test 15
Test 6 16 Test 16 Test 16
Test 7 17 Test 17 Test 17
Test 8 18 Test 18 Test 18
Test 9 19 Test 19 Test 19
Test 0 20 Test 20 Test 20
21 Test 21 Test 21
22 Test 22 Test 22
23 Test 23 Test 23
24 Test 24 Test 24
25 Test 25 Test 25
26 Test 26 Test 26
27 Test 27 Test 27
28 Test 28 Test 28
29 Test 29 Test 29
30 Test 30 Test 30

 

[SkipVought]

Please check out Process TGML - in particular
[tt]
[ignore][tt]MonoSpaced Text[/tt][/ignore]
[/tt]
It will help to line up your example (removing TAB characters and substituting SPACES)

Problems:

Sub CFD()
'
' CFD Macro
' Macro recorded 12/5/2006 by Frontier
'
Dim xcol As Integer
Dim ycol As Integer
Dim ROS As Integer
Dim CF As Range

'[b]SET is for OBJECTS, a COUNT is not a STRING[/b]
    xcol = Columns(4).Count  
    ycol = Columns(5).Count
    ROS = ycol - xcol
    Range("C2:E2").Copy
'[b]what are you doing here with CF?  There's probably a better way.[/b]
    Set CF = ActiveCell.Address(xcol + 1, 4)
    CF.Offset(1, 2).Resize("ROS", -4).PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
'
'
End Sub

Skip,

_[red][/red]

 

[DSerr77]

CF is meant to select the appropriate range to copy to.

 

[SkipVought]

Duh!

WHAT is the SCOPE of the range?

I can't see what's the state BEFORE the paste and AFTER the paste.

How 'bout a little help? Please be CLEAR, CONCISE and COMPLETE.

Skip,

_[red][/red]