Copy formulas down using autofill 1

[marboa62]

My routine works copying information on the left side of the sheet incresing month after month.

On the right side I need only copy the formulas in the last row in the row following, up to the total

COLUMS

A B ... Q R S ... Z
... ... ... ... ... ... ... ...
12 8 5 F1 F2 F3 Fs Fz
15 7 10
14 9 7

125 85 68 125 352 405 284 687

I want copy formulas F1 - Fz in the 2 row following
If I select F1 - Fz and make a double click the formulas are being copied in the 2 row under

Recording in a macro I find

"Selection.AutoFill Destination:=Range("Q71:Z73")"

How can I make it dynamic ?

Marins

 

[AnotherHiggins]

Better yet, use an often-overlooked feature of Excel.

Go to Tools > Options > Edit and ensure that Extend data range formats and formulas is checked.

See Excel's help file for Extend formats and formulas to additional rows.

The important part for you is:

Note In order to be extended to new rows in the range, the formats and formulas must appear in at least three of the five preceding rows.

So you should be able to add data to the left-hand column and have all of the formulas to the right populate all by themselves, no VBA needed!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

This is a double post!

You never answered my question, "What LOGIC defines the Start and End of the dynamic range", since you stated that now it's Q71:Z73 but it could be 100 to 200???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[marboa62]

Answer for anotherhiggins

In "option" I have the right configuration.

If I select the n colums and on the south-est give a double click, all formulas are copied under down, reaching the last row in which left colums are already filled.

I need VBA code in order avoiding this manual double click

How can I do ?

Marins

 

[SkipVought]

"...reaching the last row in which left colums are already filled."

The STARTING row...

rStart = [Q1].end(xldown).row + 1

The ENDING row...

rEnd = [P1].end(xldown).row

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[marboa62]

Answer for skip

Excuse me Skip,
in the first thread I try to explain the nature of my question

Perhaps I was not clear

How can you copy the formulas under down, reaching the rows where the left side of colums are filled.

Recording the macro, the step is coded
"Selection.Autofill Destination:=Range("...:...")

Must I register the new rows entering in the month and use Cells to determinate how many row to paste ?

Or exist another and economic solution ?

Cheers
Marins

 

[AnotherHiggins]

My point is that you do NOT need VBA to do this. Excel can do it for you.

Try this... Create a new sheet and set it up as follows
[tt]
Date Value Formula1 Formula2
1/1/08 10 =B2+1 =C2*10
1/2/08 20
1/3/08 30
1/4/08 40[/tt]

Now AutoFill columns C&D by double clicking (as you have been).

In A6, type in 1/5/08 and in B6 type in 50.

[!]Voila!!!!!!![/!]

The formulas for Row 6 should populate all by themselves. No VBA. No user-manipulation. This is just a built-in feature of Excel. Use it and enjoy.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

In your other thread, I posted...

Range([Q3], [Q3].End(xlToRight)).AutoFill Destination:=[i]DestinationRange[/i]

and asked about the logic for the Destination range.

So putting the two together...

Range([Q3], [Q3].End(xlToRight)).AutoFill Destination:=Range(Cells(rStart,"Q"),Cells(rEnd,"Q"))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[marboa62]

Ok Skip, having rStart and rEnd defined as you suggest

how can I insert them in the last istructions

Range("Q3").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
rStart = [Q1].End(xlDown).Row + 1
rEnd = [P1].End(xlDown).Row

Range (???). selection
Activesheet paste

Marins
only a mile to end...

 

[marboa62]

Hi Skip,

the routine suggested

Range([Q3], [Q3].End(xlToRight)).AutoFill Destination:=Range(Cells(rStart,"Q"),Cells(rEnd,"Q"))

does not work, stopping program for debugging


I try also with

Range([Q3], [Q3].End(xlToRight)).AutoFill Destination:=Range(Cells(rStart, 10), Cells(rEnd, 10))

with the same result

What is the problem now ?

Thanks
Marins

 

[SkipVought]

Range([Q3], [Q3].End(xlToRight)).AutoFill Destination:=Range(Cells(rStart,"Q"),Cells(rEnd,"Q"))

does what your 7 lines of code wants to do.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Sorry, I missed this...

Range([Q3], [Q3].End(xlToRight)).AutoFill _
    Destination:=Range(Cells(rStart, "Q"), Cells(rEnd, [b][Q3].End(xlToRight).Column)[/b])

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[marboa62]

Thanks to everybody !
Your hints helped me
I got the solution

Marins
Spirit of Australia