autofill data from a sheet to a sheet named after a current month

Question

pls help me,i want to track the daily,monthly records of my shop my first sheet is where i record all the earnings for the day,i made an update button that transfers all of the data collected at the end of the day to the next worksheets,which are named after the months.

i successfully made it on the month of july because i just typed in "July" to select the worksheet where the data will be transferred.

but what i want to get is automatically selecting the worksheet based on the current month ,where the next worksheets are named after.

i tried worksheets(format(now,"mmm")).select

but im getting an error stating that i am out of range

here's my whole code:

Private Sub CommandButton1_Click()

If MsgBox("Are you sure?", vbYesNo, "Title") = vbYes Then

Dim CtrlCDate As String, CtrlCPSQty As Integer, CtrlCPSAmt As Integer, CtrlCPLQty As Integer, CtrlCPLAmt As Integer, CtrlCPQty As Integer, CtrlCPamt As Integer, CtrlCSQty As Integer, CtrlCSAmt As Integer, TC As Integer, TL As Integer, TS As Integer
    Worksheets("CTRLC Ops").Select
    CtrlCDate = Range("N3")
    CtrlCPSQty = Range("N6")
    CtrlCPSAmt = Range("O6")
    CtrlCPLQty = Range("N7")
    CtrlCPLAmt = Range("O7")
    CtrlCPQty = Range("N8")
    CtrlCPamt = Range("O8")
    CtrlCSQty = Range("N9")
    CtrlCSAmt = Range("O9")
    TC = Range("O10")
    TL = Range("O11")
    TS = Range("N13")
    Worksheets("July").Select
    Worksheets("July").Range("A3").Select
    If Worksheets("July").Range("A3").Offset(1, 0) <> "" Then
    Worksheets("July").Range("A3").End(xlDown).Select
    End If
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = CtrlCDate
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = CtrlCPSQty
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = CtrlCPSAmt
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = CtrlCPLQty
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = CtrlCPLAmt
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = CtrlCPQty
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = CtrlCPamt
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = CtrlCSQty
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = CtrlCSAmt
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = TC
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = TL
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = TS
    Worksheets("CTRLC Ops").Select
    Worksheets("CTRLC Ops").Range("D1").Select

End If

End Sub

PLS HELP ME,tried to do research but im not sure which part am i wrong.thanks in advance

Mark Nash Mark Nash · Accepted Answer · 2014-07-29T14:44:13

Worksheets(Format(now,"mmm")).Select will select a worksheet called "Jul". Is that the name of the sheet, or is it July?

"mmm" will return the first three letters of the month, "mmmm" will return the full month name.

So if you want to select worksheet "July", then use Worksheets(Format(now,"mmmm")).Select

autofill data from a sheet to a sheet named after a current month

1 Answers