Excel VBA Using a function in a cell to pull in data from different sheets

To be able to use VBA in spreadsheet cells, you have to create the function in VBA so that it will work like other formulas.

Open up VBA Editor but pressing Alt-F11. Open Project Explorer by pressing Ctrl-R.

In the Project Explorer, look for the current file you are working on. Right click and choose Insert -> Module.

In the module, type in some like the bellow.

Function GetQuarterEnd(Rngdate As Range) As Date
Dim myDateVariable As Date
Dim Year1, Month1, Day1, ResultMth, ResultDate As Integer

Year1 = Year(Rngdate.Value)
Month1 = Month(Rngdate.Value)
Day1 = Day(Rngdate.Value)

Select Case Month1
    Case 1 To 3
        ResultMth = 3
    Case 4 To 6
        ResultMth = 6
    Case 7 To 9
        ResultMth = 9
    Case 10 To 12
        ResultMth = 12
End Select

Select Case ResultMth
    Case 3, 12
        ResultDate = 31
    Case 6, 9
        ResultDate = 30
End Select
myDateVariable = DateSerial(Year1, ResultMth, ResultDate)
GetQuarterEnd = myDateVariable
End Function

Select all Open in new window

After that, you can start using formula like =GetQuarterEnd(A1) in spreadsheet cells.

In you question, you said you want to put data from multiple sheet into one. That doesn't sound like it has anything to do with calling VBA function from spreadsheet cells.