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]

Tony,

Can you put a break in the code? Then hit your button and step thru. Tell me what happens.

Sub Button8_Click()
'
With Sheets("P&L Var - MTD Summary")[highlight red]
    r = .[C1].CurrentRegion.Rows.Count[/highlight]
    Range(.Cells(r, "C"), .Cells(r, "D")).Copy .Cells(r + 1, "C")
    .Calculate
 End With
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!}

 

[Mizzness]

Skip,

"Can't execute code in break mode" is what I get.

 

[SkipVought]

put a msgbox in your code and run it.

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,

Here is the code:

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
If E3 = 12 Then
MsgBox "We Have A Problem"
End If
End With
End Sub


Nothing happens when I run it.

Also, just so you don't think I'm bailing out on you I'll be leaving at exactly 5:00 here in the east.
I may not be able to get back to you till tomorrow morning.

Thank you for your patience and effort and I'm sure we'll get the answer to this frustrating problem.

Anthony

 

[SkipVought]

Get rid of the IF statement -- JUST THE MsgBox!

If your code is not running, you have a problem with your event handler. It's not the code.

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

In a Module

DOUBLE CLICK the button. You should see your code. It should NOT be in a module. It should be in the 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]

I have both- a Command Button and a UserForm Button residing in my "Macro" tab.

The UserForm button is assigned to the Sub Forest() code.

Did I not call it correctly & How do I assign it to a button ?

As you can tell, I'm not familiar with Command Buttons or assigning code but this has been a very steep learning curve.

Thanx.

 

[Mizzness]

skip,

As I believe confusion is started to reign, I have a Command & UserForm button on my "Macro" tab.

The Sub Forest() code is presently assigned to the UserForm.

I don't think I called the macro correctly and how do I assign the code to either button since it's not as clear on how to assign code to a Command Button.

I am not too familiar with Command Buttons or assigning macros (as you can see).

 

[Mizzness]

Skip,

Got rid of the If Statement and the Msg Box "Message" does appear.

The code is now as follows:

Private Sub UserForm_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
MsgBox "We Have A Problem"
End With
End Sub


How do I assign a button to it ?

 

[SkipVought]

DOUBLE CLICK the button. You should see your code. It should NOT be in a module. It should be in the UserForm.

You code should be in the CommandButton1_Click event in UserForm1.

Skip,

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

 

[SkipVought]

BTW, the MACRO does not need to be there, just a call to the macro...

Private Sub CommandButton1_Click()
    MyMacro
End Sub

MyMacro can be in a Module.

Skip,

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

 

[Mizzness]

The code is:

Private Sub CommandButton1_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
MsgBox "We Have A Problem"
End With
End Sub

I have a Command Button in my XL sheet nad by clicking on it I do see my code. So far so good.

How do I assign the button to the code ?
The novice in me is comiging out.

Thanx.

 

[Mizzness]

Ah just saw your last post.

 

[SkipVought]

Maybe I need to clarify some things.

1. there are TWO kinds of controls: Form controls and Control Toolbox controls.

2. Form controls can be placed on a sheet and when you do, you get a dialog to assign a macro if you wish. LATER you can assign a macro by right clicking the control and select assign macro. That button click event resides in a MODULE.

3. Control Toolbox controls can be place on a worksheet or on a UserForm in the VB Editor. These controls are more difficult to use, but have alot more functionality. When you place the control, you are NOT given any indication of where the event code resides. You can double click the control to expose the click event and other event codes in the VB editor.

Skip,

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

 

[SkipVought]

You keep changing the story.

NOW your command button in in a SHEET and NOT in a FORM?

So when you double click, your click event code will be in the SHEET OBJECT as

Private Sub CommandButton1_Click()

End Sub

and you call you macro from this code...

Private Sub CommandButton1_Click()
  MyMacro
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!}

 

[Mizzness]

Don't know if I've done this correctly.(probably not as I only get the Msg to come up)


This code resides in the module:

Sub Forest()
'
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
MsgBox "We Have A Problem"
End With
End Sub


This resides in the userform:

Private Sub CommandButton1_Click()
Sub Forest()
End Sub


Thanx.

 

[SkipVought]

I though you stated that your button was on your sheet? Now it's back in a userform? OL!

Private Sub CommandButton1_Click()
    Forest
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!}

 

[Mizzness]

Confusion reigns.

I need guidance in calling the macro to the User Form Button.

Also, I don't know if you have received my prior messages as I don't see them posted here.

 

[SkipVought]

TT is having trouble with their server.

please contact via e amil...

ii36250 at bellhelicopter dot textron dot com

substitute ats and dots accordingly.

Skip,

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

 

[Mizzness]

I think they finally updated the time of posts.

So the threads are off in sequence.
Probably never realized that daylight savings just happened.

 

[Mizzness]

As you say, "There are many ways to skin a cat"

Is there another way around this ?