Getting a multiple sheets in excel to pull from other sheets 1

[kidnos]

Hi, I have about 30 different worksheet in an excel file. I also have insert a new worksheet for every old one I have and need to pull info from each worksheet. So, for example I will have sheet 1 and template 1, sheet 2 and template 2. Is there any way to set something up so for every template sheet it pulls from the corresponding sheet without having to type sheet 1 in all the rows of the template sheet? Maybe there is something where it points to that worksheet in general so I dont have to type out every row. Thanks much

 

[KenPuls]

Hi there,

While this isn't the VBA forum, I can give you a VBA solution. If you use it a ton, however, you may find that the worksheet will slow down drastically.

-To use this, press Alt+F11 to open the Visual Basic Editor (VBE)
-Press CTRL+R to open the Project Explorer (may be open) which looks like a windows tree
-Find you project and expand it till you can see all the child items in it
-Right click your project and choose Insert--> Module
-Paste the following code in the pane at right:

Public Function SheetOffset(rng As Range, lOffset As Long) As Variant
    Application.Volatile
    SheetOffset = Worksheets(rng.Parent.Index + lOffset).Range(rng.Address).Value
End Function

-Close the VBE and save your workbook

You'd use the formula by entering it the cell as follows:
=SheetOffset(B2,1)

Where B2 is the range you want from the other sheet, and 1 is the number of sheets you want to move to the right. If you want a sheet to the left of what you're on, use -1 (or -2, etc...)

Again, custom formulas can be very slow when you get a lot in there. It may be better to create your first Sheet1/Template1 formula set, copy them to Sheet2 and do a find/replace to replace all Sheet1 with Sheet2. Of course this only works if the sheets are identical.

There may also be a native formula route with this, but I'll leave that for someone else to explore.


Ken Puls, CMA

http://www.excelguru.ca

 

[firefytr]

Nice function Ken.

But without error handling, you'll get the #VALUE! error. This would happen if a sheet was referenced/offset that did not have a valid Index number, such as the formula going in the left-most sheet (I'll say "Sheet1") with this formula...

=SheetOffset(A1,-1)

This would look at the Index sheet 0 - which is not a valid sheet. You can add error handling with only a couple of lines ...

Public Function SheetOffset(rng As Range, lOffset As Long) As Variant
    Application.Volatile
    SheetOffset = "ERROR!"
    On Error Resume Next
    SheetOffset = Worksheets(rng.Parent.Index + lOffset).Range(rng.Address).Value
End Function

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[KenPuls]

Good point, Zack. Should have thrown that in there.

Ken Puls, CMA

http://www.excelguru.ca

 

[kidnos]

I thought I could use the indirect function but it keeps kicking me a #REF.

 

[KenPuls]

Actually, that should work, but you'd need to break down your references a bit. Assuming that you wanted Sheet2!B2, and you put the following in these cells:
A1: 2
B1: B
C1: 2

This should pull back the result: =INDIRECT("Sheet"&A1&"!"&B1&C1)

HTH,

Ken Puls, CMA

http://www.excelguru.ca