About a week ago, a client of mine asked me if I could write him a function to pull data from the previous worksheet. Upon further interrogation, I found out that he has a workbook where he manages his monthly budget. Each tab consists of a month's data. The first tab is named January, the second tab is named February, etc.
What he needed was a function that would pull data from the previous month's sheet without having to specify the sheet name. Why? Because at the end of each month, he would add another sheet and he did want to re-jigger his formulas to change sheet names.
I could have suggested a database format, but he's pretty happy with his budget sheet. Plus, he's a pretty important client so I complied with his request and put together this function. You may be able to adapt it if needed.
-
Function PrevSheet(TargetCell As Range)
-
Dim SheetIndex As Integer
-
-
'Make sure the formula calculates even if moving sheets around
-
Application.Volatile
-
-
'Get the index of sheet where the caller formula resides
-
SheetIndex = Application.Caller.Parent.Index
-
-
'Let user know if the caller is on the first sheet
-
If SheetIndex = 1 Then
-
PrevSheet = "No Previous Sheet"
-
Exit Function
-
End If
-
-
'Return the target cell value in the previous workhseet
-
PrevSheet = Sheets(SheetIndex - 1).Range(TargetCell.Address).Value
-
-
End Function
With this function, you can enter a formula like =PrevSheet(A1) to get the value in cell A1 from the previous sheet - no matter what the sheet name is.
After I gave him my function, I found a better function on Walkenbach's site.
Walkenbach's function allows a bit more flexibility in that you can point to any sheet simply by resetting the offset number.
It’s amazing how many ways there are to accomplish any given tasks using VBA.
Is there a reason not to use ".Previous" and ".Next" properties of the parent worksheet?
Another (similar) way...
The Sheets in the Workbook can be in any order.
AlexJ: I've never used .Previous and .Next. How would you implement that?
Jim: Interesting...Do you have your sheet names in a Custom List?
datapig,
There are four identical built-in "custom" lists in xl97 thru xl2007...
1. Mon, Tue...
2. Monday, Tuesday...
3. Jan, Feb...
4. January, February...
Creating and using a (true) custom list in a User Designed Function would cause problems if the workbook were used on a different computer. A different computer would not have the custom list on it.
Using a variant containing an array, in the code, would be a simple alternative...
vShtNames = Array("Grape", "Lemon", "Orange", "Pear")
A code adjustment for using a 0 based variant array vs. the 1 based custom list array would be necessary.
I know you know all of this, however, others reading this may not.
datapig,
I was thinking of something like this, although I remember reading somewhere in the murky past (ok, so yesterday was murky, if you really need to know) that ".Previous" and ".Next" could cause problems...
@Jim Cone,
Where you suggest this...
vShtNames = Array("Grape", "Lemon", "Orange", "Pear")
I would suggest using this instead...
vShtNames = Split("Grape,Lemon,Orange,Pear", ",")
The reason is that the Array function produces a zero-based or one-based array depending on the Option Base setting a user might have implemented, so you would have to use LBound(vShtNames) to reference the starting index for the array and adjust the code accordingly (which is not a hard task, of course). On the other hand, the Split function **always** produces a zero-based array no matter what the Option Base setting is and so you can modify the code to use the known zero-based array's lower bound of zero.
Hi datapig,
Now, I may a bit less advanced than y'all, but I would have approached this as follows.
1. Call the first tab 'This Month'
2. Call the second tab 'Last Month'
3. Write 2 macros: the first to copy data from 'This Month' to 'Last Month' and the second to copy data from 'Last Month' to a tab named after the actual month.
In this way, you never need to change a single formula and your client gets what he wants. It's less elegant, but should get the job done and allow the client to support it easily themselves.
Of course, I don't know all the details behind your client's requirement so I'm wondering if this was thought of.
Thanks,
Loren
BTW, love the blog posts.
Your coding & easy explanation saved me 10 years trying to figure this crap out. THank you SO SO much! =))