vba refer previous sheet not by name

[grapevine]

I have the following code which works correctly, but I will need to replicate it and alter it slightly 15 times to refer to different pages. Is it possible to alter this code so that it looks at the previous sheet to the one that I am on. My actual spreadsheet consists of 5 core pages and I need to extract information from those pages onto 15 other sheets. The call routines will be very similar and where possible I want to store routine tasks in sub routines that I can easily call when needed. This coding refers to column on sheet 7 which is obtaining information from sheet 6. later I will be creating sheets 8 and 9 and will need to lookup data on sheet 8 that needs to be placed into sheet 9. I hope this makes sense.

I have copied the full code below for information but the key element that need adapting is

VLOOKUP(RC[-14],'W & G List'!R1C13:R22C14,2,FALSE

I would be grateful fo any assistance is how I could adapt this.
Kind regards
Marion

full code for information is
Sub preparelabels()
Dim rw As Long
rw = Range("c65536").End(xlUp).row
Range("R1") = "Code"
Range("R2:r" & rw).FormulaR1C1 = "=if(iserror(VLOOKUP(RC[-14],'W & G List'!R1C13:R22C14,2,FALSE)),"""",vLOOKUP(RC[-14],'W & G List'!R1C13:R22C14,2,FALSE))"
End Sub

 

[g4cox]

I have the following code which works correctly, but I will need to replicate it and alter it slightly 15 times to refer to different pages. Is it possible to alter this code so that it looks at the previous sheet to the one that I am on. My actual spreadsheet consists of 5 core pages and I need to extract information from those pages onto 15 other sheets. The call routines will be very similar and where possible I want to store routine tasks in sub routines that I can easily call when needed. This coding refers to column on sheet 7 which is obtaining information from sheet 6. later I will be creating sheets 8 and 9 and will need to lookup data on sheet 8 that needs to be placed into sheet 9. I hope this makes sense.

I have copied the full code below for information but the key element that need adapting is

VLOOKUP(RC[-14],'W & G List'!R1C13:R22C14,2,FALSE

I would be grateful fo any assistance is how I could adapt this.
Kind regards
Marion

full code for information is
Sub preparelabels()
Dim rw As Long
rw = Range("c65536").End(xlUp).row
Range("R1") = "Code"
Range("R2:r" & rw).FormulaR1C1 = "=if(iserror(VLOOKUP(RC[-14],'W & G List'!R1C13:R22C14,2,FALSE)),"""",vLOOKUP(RC[-14],'W & G List'!R1C13:R22C14,2,FALSE))"
End Sub

Am I right in thinking that you want the VLOOKUP to go to the previous page instead of (in your example) W & G List?

 

[grapevine]

Yes, I want it look at the previous page to the one that the macro is currently looking at so that I can reuse the code. The page will stilll be called W&J, then the next two pages will have another company name etc.

I was hoping there was some code that would somehow say (current sheet -1) so that I could call the same macro when I am updating the data in the W&J (company 1) then when I update the information for company 2 etc. As the macro will know which page it is in because of other sub macros I am running, it will save altering the code if I can just say look at information on previous page rather than specifying a sheet name or number.

I hope that makes sense now.
Kind regards
Marion

 

[shg]

You could use these two functions:

Function OtherSheet(ByVal vRng As Variant, _
                    Optional ByVal iIndex As Long = -1, _
                    Optional bRelative As Boolean = True) As Range
    ' shg 2010
    ' UDF Only!
 
    ' Returns Range(vRng) on
    '   Worksheets(iIndex)                                      if bRelative = False
    '   Worksheets(iIndex + Application.Caller.Worksheet.Index) if bRelative = true
 
    ' Defaults to the range on Application.Caller.Worksheet.Next
 
    ' If the referenced range doesn't overlap the calling range, just pass the range:
    '   =SUM(OtherSheet(A1:A3))
 
    ' If they do, pass the range reference in quotes to avoid a circular reference error:
    '   =SUM(OtherSheet("A1:A3"))
 
    ' To pass multi-area ranges, enclose in parens or quotes:
    '   =SUM(OtherSheet((A1:A10, C1:C5), 2, False))
 
    ' No error checking -- it's a UDF _only_
 
    Application.Volatile True
    If TypeOf vRng Is Range Then vRng = vRng.Address
 
    With Application.Caller.Worksheet
        If bRelative Then iIndex = .Index + iIndex
        Set OtherSheet = .Parent.Worksheets(iIndex).Range(vRng)
    End With
End Function
 
Function IFERROR(ToEvaluate As Variant, Default As Variant) As Variant
    If IsArray(ToEvaluate) Then
        IFERROR = IIf(IsError(ToEvaluate(1)), Default, ToEvaluate)
    Else
        IFERROR = IIf(IsError(ToEvaluate), Default, ToEvaluate)
    End If
End Function

And change the formula to

=IFERROR(VLOOKUP(RC[-14], OtherSheet("$M$1:$N$22", -1, TRUE), 2, FALSE), "")

 

[grapevine]

Thank you very much for this. I will not be able to look closely at this until Saturday morning when I will try and understand the code and learn from the example.

I look forward to trying this out tomorrow as this will potentially save a tremendous amount of coding. I will probably have a few more questions, but I will post anyway to let you know how I got on.

Once again, many thanks, your help is really appreciated.

 

[grapevine]

Just to say, thank you very much. I managed to get my macro working just as I need to.
Your help was much appreciated.

 

[brettdj]

fwiw you can use
.Previous
to refer to the sheet to the immediate left (suggest with error handling in case this sheet isn't accessible)

so

Sub Test()
MsgBox ActiveSheet.Previous.Name
End Sub

will return the name of the sheet to the immediate left of the activesheet

cheers

Dave

 

[shg]

Your help was much appreciated.

You're welcome, glad it worked for you.

 

[grapevine]

Thank you for the .previous information, this will really come in handy and is so easy to use.
kind regards

 

[shg]

Great!

This is wrong:

' Defaults to the range on Application.Caller.Worksheet.[COLOR=red]Next[/COLOR]

It should say:

' Defaults to the range on Application.Caller.Worksheet.[COLOR=red]Previous[/COLOR]

And given that, your formula could be just

=IFERROR(VLOOKUP(RC[-14], OtherSheet("$M$1:$N$22"), 2, FALSE), "")