Formula Copied for Each Date 1

[Mizzness]

All.

By attaching the following code to a button, I'm trying to copy down the formula to the next line. This would bve done on a daily basis.

c3:d3 to c4:d4 one day
c4:d4 to c5:d5 the next and so on.

Sub()
Sheets("P&L Var - MTD Summary").Select
Range("C33").Select
Selection.Copy
Range("C4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate
End Sub

What do I need to add ?

Thanx.

 

[SkipVought]

Sub()
 with Sheets("P&L Var - MTD Summary")
    r = .[C1].currentregion.rows.count
    Range(cells(r, "C"), cells(r, "D")).Copy Cells(r+1, "C)    
    .Calculate
End Sub

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[AnonGod]

good one.

here's what i would have done without seeing that code...

Sub movedown()
    Dim i As Integer
    Worksheets("Sheet1").Activate
    i = Range("F1").Value
    Range("C" & i & ":D" & i).Copy
    Range("C" & i + 1).Activate
    ActiveSheet.Paste
    Application.CutCopyMode = xlCopy
    i = i + 1
    Range("F1").Value = i
End Sub

 

[SkipVought]

I advise AGAINST using the Activate and Select methods.

How Can I Make My Code Run Faster? faq707-4105

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Mizzness]

Skip,

Thanks for the guidance however, the following is highlighted in Red:

Range(cells(r, "C"), cells(r, "D")).Copy Cells(r+1, "C)

Any reason ?

Thanx.

 

[Mizzness]

The error I get is:
Compile error: Syntax error

Thanx for your help.

 

[SkipVought]

Hi,

Seems I left off some important punctuations --

 With Sheets("P&L Var - MTD Summary")
    r = .[C1].CurrentRegion.Rows.Count
    Range(.Cells(r, "C"), .Cells(r, "D")).Copy .Cells(r + 1, "C")
    .Calculate
 End With

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Mizzness]

Skip,

The code isn't doing anything.
The formulas I need copied are c3:d3 to c4:d4 today.
For tomorrow, it can be either c3:d3 to c5:d5 or c4:d4 to c5:d5 and so on.

The formula I'm using is:
Sub Split()
With Sheets("P&L Var - MTD Summary")
r = .[C1].CurrentRegion.Rows.Count
Range(.Cells(r, "C"), .Cells(r, "D")).Copy .Cells(r + 1, "C")
.Calculate
End With
End Sub

Thanx.

 

[SkipVought]

The code that you posted (and that I wrote) copies the LAST ROW'S values from columns C & D and pastes them into the NEXT ROW. I tested it just now and it works!

Is that NOT what you want?

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Mizzness]

Maybe I am missing something or wasn't clear enough - cells c3:d3 contain formulas.
If I'm copying from the last row, which doesn't contain formulas, wouldn't I be copying nothing?

The formulas will always be in ce:d3.

Sorry for any confusion.

 

[SkipVought]

Please explain exactly WHAT you have on your sheet.

For instance, you stated, "The formulas I need copied are c3:d3 to c4:d4 today."

Under those conditions, are C4 & D4 empty?

How does one choose WHICH cells to copy WHERE. Usually, one copies the LAST row of data or data down to the last row of data, to the next EMPTY row SOMEWHERE, either in the same table or another. Apparently, that is not in your logic.

What IS your logic?

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Mizzness]

My sheet is set up as follows:

A3 = Grand Total (this will never change)
B3 = Date (changes by row only; e.g. B3=04/04/05, B4=04/04/05
C3 = Vlookup formula
D3 = Vlookup formula

The vlookup formulas are:
=VLOOKUP(A3,INDIRECT("'P&L Var Summary "& TEXT(B3,"mm\_dd") & "'!A"),2,FALSE) in cell C3 and
=VLOOKUP(A3,INDIRECT("'P&L Var Summary "& TEXT(B3,"mm\_dd") & "'!A"),3,FALSE) in D3.

So what I'm trying to do is link a macro to a button so that the user can copy down the vlookup formulas from C33 to C44 for today.
Tomorrow the macro would copy down the formulas from C33 to C55 or from C44 ot C55; which ever is easier.
Since the dates are established in Column B, by calculating,
this will update the cell in regards to the formula for that day.

I hope I was clear enough.

Thanx.

 

[SkipVought]

And what is it NOT doing???

..because what you described is EXACTLY what this procedure does WITHOUT ANYONE deciding or caring WHICH ROW is copied to the next empty row because it matters not.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Mizzness]

Skip,

I understand your frustration as mine is right up there too.

The macro doesn't seem to be doing anything.

What else can I check for ?
I've attached the button to it (even re-did it) and re-copied the formula; in essence started from scratch.

Thanx.
Anthony

 

[SkipVought]

Where does your macro reside; in a module, in a sheet object (which one), in the ThisWorkbook object, in a userform????

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Mizzness]

In a Module

 

[SkipVought]

And what kind of a control; MS Form or Control Toolbox Button?

And where is the control located?

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Mizzness]

MS Form

 

[SkipVought]

What code is is the Button_Click event that should be running this macro?

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Mizzness]

Sub Button8_Click()
'
With Sheets("P&L Var - MTD Summary")
r = .[C1].CurrentRegion.Rows.Count
Range(.Cells(r, "C"), .Cells(r, "D")).Copy .Cells(r + 1, "C")
.Calculate
End With
End Sub

Thanx.
Anthony