Using Indirect & Creating a worksheet Macro

B

Bill Healy

Worksheet is structured as follows:

Sheet 1 - called "Account Data"
Cell A1 = Account Title
Cell A2-A250 = Various Account titles

Sheet 2 - called "template"
Has various, Vlookup's & Indirect Functions, triggered from the sheet name

There are no other sheets in the workbook.

What I'd like to be able to do is run a marco which:

1) Creates a copy of the "template"
2) Re-names the "tab" with the name contained in cell A2 of sheet named
"Account Data"
3) Continues to do the above, moving down the list in "account data
(A3,A4,A5, ETC) until it reaches a blank cell, in which case the marco stops
running.

I can do each task individually from previous advice given via this site,
however it would be great if I could do both together.

Oh, one final thing, i'd like the marco to run which I do "CTRL X"

Hope one of you wizards out there can help!

Bill
 
E

ehntd

Option Explicit

Sub Macro1()
Dim i As Integer
i = 2

Do
Sheets("template").Select
Sheets("template").Copy Before:=Sheets(1)
Sheets("template (i)").Select
Sheets("template (i)").Name = Sheets("Accoun
Data").Range("A2").Value
i = i + 1
ActiveCell.Offset(1, 0).Activate
Loop Until IsEmpty(ActiveCell)

End Sub

I think this should do the trick. About the control x... don´t know..
lemme think about it
 
Top