Simple Coding Problem

[Mizzness]

All.

The following code is giving me the error of "subscript out of range".

Sub carolina()

Dim ws2 As Worksheet

Set ws2 = Sheets("Whatever")
With Sheets(whatever)
.Range("C33").Copy
ws2.[C4].PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
Set ws2 = Nothing
Calculate
End Sub


Two things:
1. Please guide me through the error.
2. This code is set up right now to copy from row 3 to row4 today. How can I have it copy to row 5 tomorrow then to row 6 the next day etc. on a daily basis.

Thanx.

 

[SkipVought]

Mizzness,

On what line of code?

Usually, this means that something like your sheet name does not exist. Is it spelled correctly?

Skip,

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

 

[PHV]

Isn't your problem here ?
With Sheets(whatever)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Mizzness]

Skip,PHV

Correct, that is where it lies.

With Sheets(whatever)

 

[SkipVought]

Sub carolina()

Dim ws2 As Worksheet

    Set ws2 = Sheets("Whatever")
    With [b]Sheets("SomeOther")[/b] 'make sure this name is correct 
        .Range("C3:D3").Copy
        [b]ws2.Cells(ws2.[C1].currentregion.rows.count+1,"C")[/b].PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
    Application.CutCopyMode = False
    Set ws2 = Nothing
    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!}

 

[Mizzness]

Skip,

Thanks. It worked great.

By attaching the following code to a button, I'm trying to copy down the formula as stated in my prior example.
c3:d3 to c4:d4 one day
c4:d4 to c5:d5 the next and so on.

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.