Excel VBA Using a function in a cell to pull in data from different sheets
Hi
I have several workbooks with data in a similar format.
I know that you can call a VBA function in a cell.
Does anyone have an example where this technique is used to pull
data from several sheets into one?
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 DateDim myDateVariable As DateDim Year1, Month1, Day1, ResultMth, ResultDate As IntegerYear1 = 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 = 12End SelectSelect Case ResultMth Case 3, 12 ResultDate = 31 Case 6, 9 ResultDate = 30End SelectmyDateVariable = DateSerial(Year1, ResultMth, ResultDate)GetQuarterEnd = myDateVariableEnd Function
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.
I'm not great at pounding out code, but I can copy existing code and tweak it to suit my situation.
Here is an example of copying from another worksheet. It's not what you asked for, but simplifies the process: http://www.ehow.com/how_7361777_copy-paste-another-using-vba.html
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.
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.