User Defined Fuction In Excel

[mkatz13]

I have a workbbok with 13 sheets, one for each month and a summary page. I have used the macro recorder to create a macro that copies cells from the summary page and pastes the values into the cell where the cursor is when I hit the
hot key for the macro.

The recorder produced this code


ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Today&quot.Select
Range("A3:G3&quot.Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("July&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

My problem is that every month I have to change the code to reflect the new month. I would like to write a function which based on the month() function , would return the name of the month, which is how the sheets are named.
I would then call that function from the code above in place of where July now is.

However, I am an extreme novice in Excel. How do I write such a function, where do I place the code, how do I pass a parameter and how do I return the value.Ca I even call a function from within the code above?

I would write the function using a do case, that much I know.
Any help would be appreiated


Thanks in advance

Mike Katz

 

[TonyJollans]

Hi mkatz13,

Recorded code can almost always be improved upon, but it's about the best place to start experimenting from. To do as you ask, just replace the line

Sheets("July&quot.Select

with

Sheets(format(date,"mmmm")).Select

Enjoy,
Tony

 

[Stretchwickster]

This may not be the quickest way and is just off the top of my head:

  Private Function CurrentMonth() As String
    Select Case Month(Now)
      Case 1
        CurrentMonth = "January"
      Case 2
        CurrentMonth = "February"
      Case 3
        CurrentMonth = "March"
      Case 4
        CurrentMonth = "April"
      Case 5
        CurrentMonth = "May"
      Case 6
        CurrentMonth = "June"
      Case 7
        CurrentMonth = "July"
      Case 8
        CurrentMonth = "August"
      Case 9
        CurrentMonth = "September"
      Case 10
        CurrentMonth = "October"
      Case 11
        CurrentMonth = "November"
      Case 12
        CurrentMonth = "December"
      Case Else
        CurrentMonth = "Invalid Month"
    End Select
  End Function

  Private Sub CommandButton1_Click()
    MsgBox CurrentMonth
  End Sub

Hope this helps!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Stretchwickster]

lol...I thought there might be a quicker way Tony!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[TonyJollans]

Hi Clive,

Yeah, slightly quicker (to type, certainly).

It's one of so many things that are built in but not in any obvious place and which the help index never brings up.

Enjoy,
Tony

 

[ashfaque1]

Open your excel sheet and press Alt+F11 and then insert a module (module1). then write your function e.g.

public function monthname(x as integer) as string
do case x
case 1
monthname = "January"
case 2
....
....
....
....
....
....
end case
end function

assign a value to monthname (eg. monthname = "July&quot according to a condition.

Now you can use monthname() function anywhere in the workbook.

 

[mkatz13]

Got it

thanks to all who responded