I need to automate a workbook to autofill some pages.
The layout is:

Page 1 is master sheet. basically the idea is:

a b c d e
f g h
1 name1 phone1 address1
2 name 2 phone 2 etc etc
3 name 3 phone 3 you get the idea
4 name 4 phone 4
5
6
7
7
8
9

pages 2-100 are individual reference sheets for each name.
sheet 2 would be:
a1 = a1 from master sheet
a2 = b1 from master
a3= c1 from master

What I would like to be able to do is find a way to automate the
process so that I can set up one sheet and every sheet i make
afetrwards would autopopulate with the information without having to
individually go to each sheet and copy in the formulas.

Looking around on other message boards I did learn the ability to use
JWalk's SHEETOFFSET function
(http://j-walk.com/ss/excel/tips/tip63.htm)
to reference the previous page and so instead of page 3 having to
manually reference back to the master sheet, it looks to the previous
page.
For example:

Sheet 3, A1= "look back at previous page and see where it got its data
from and take the data in the next cell down".

This can be done with:
=INDEX(Master!A:A,MATCH(SHEETOFFSET(-1,$A$1),Master!A:A,FALSE)+1)

However if you come up with duplicates like A10, A11, and A12 all being
"James Smith" the above formula will not work, because it doesnt really
understand going to the next row.

Any ideas, or better suggestions on how to accomplish this task?


--
shaneh
------------------------------------------------------------------------
shaneh's Profile: http://www.excelforum.com/member.php...o&userid=25033
View this thread: http://www.excelforum.com/showthread...hreadid=385612

Do you need to set these sheets up one at a time or can you do a whole bunch at
once?

I'm gonna assume that you do it one at at time...

I'd use a macro that inserted the new sheet and then copied over the
information.

Option Explicit
Sub testme()

Dim mstrWks As Worksheet
Dim newWks As Worksheet
Dim resp As Long
Dim CurRow As Long

Set mstrWks = ActiveSheet

With mstrWks

CurRow = ActiveCell.Row

If .Cells(CurRow, "A").Value = "" Then
MsgBox "Please select a row with something in column A"
Exit Sub
End If

resp = MsgBox(Prompt:="Create a new worksheet based on: " _
& .Cells(CurRow, "A").Value & "?", Buttons:=vbYesNo)

If resp = vbNo Then
Exit Sub
End If

Set newWks = Worksheets.Add

newWks.Range("a1").Value = .Cells(CurRow, "A").Value
newWks.Range("a2").Value = .Cells(CurRow, "B").Value
newWks.Range("a3").Value = .Cells(CurRow, "C").Value

On Error Resume Next
newWks.Name = .Cells(CurRow, "A").Value
If Err.Number < 0 Then
MsgBox "Please rename " & newWks.Name & " manually"
End If
On Error GoTo 0

End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

shaneh wrote:

I need to automate a workbook to autofill some pages.
The layout is:

Page 1 is master sheet. basically the idea is:

a b c d e
f g h
1 name1 phone1 address1
2 name 2 phone 2 etc etc
3 name 3 phone 3 you get the idea
4 name 4 phone 4
5
6
7
7
8
9

pages 2-100 are individual reference sheets for each name.
sheet 2 would be:
a1 = a1 from master sheet
a2 = b1 from master
a3= c1 from master

What I would like to be able to do is find a way to automate the
process so that I can set up one sheet and every sheet i make
afetrwards would autopopulate with the information without having to
individually go to each sheet and copy in the formulas.

Looking around on other message boards I did learn the ability to use
JWalk's SHEETOFFSET function
(http://j-walk.com/ss/excel/tips/tip63.htm)
to reference the previous page and so instead of page 3 having to
manually reference back to the master sheet, it looks to the previous
page.
For example:

Sheet 3, A1= "look back at previous page and see where it got its data
from and take the data in the next cell down".

This can be done with:
=INDEX(Master!A:A,MATCH(SHEETOFFSET(-1,$A$1),Master!A:A,FALSE)+1)

However if you come up with duplicates like A10, A11, and A12 all being
"James Smith" the above formula will not work, because it doesnt really
understand going to the next row.

Any ideas, or better suggestions on how to accomplish this task?

--
shaneh
------------------------------------------------------------------------
shaneh's Profile: http://www.excelforum.com/member.php...o&userid=25033
View this thread: http://www.excelforum.com/showthread...hreadid=385612

--

Dave Peterson