Challange<<Bet Nobody can do it?>> MATCH & p

[dspa0712]

Can anyone help

On a workbook I have the following code:
________________________________

Public Sub CommandButton1_Click()
Worksheets("Service").Visible = True
Worksheets("Service").Copy before:=Worksheets("data")
Worksheets("Service").Visible = False
ActiveSheet.Name = "Service(1)"
Dim I As Long
For I = 1 To Worksheets("Provider").Range("D25") - 1
Worksheets("Service").Visible = True
Worksheets("Service").Copy before:=Worksheets("data")
Worksheets("Service").Visible = False
Next I
Worksheets("data").Select
End Sub
____________________________________


I also have a control sheet of data called "data service" consisting of ColumnA "Name of Provider" A2:A69 and ColumnB"Service Number" B2:B69

Column B are unique numbers
Column A may contain duplicates of the same provider name

From the code listed above I need to pre-populate the service number on each new sheet created where the Provide Name on the newly created worksheet = Column A value on the "data service" worksheet and the service number is populated on the newly created worksheet cell "D7"


eg

If the Provider Name was "logic"
data service worksheet

Column A.........................................Column B
Name of Provider.............................Service No

logic................................................120
nre..................................................699
locic.................................................47
gwe.................................................220
nre..................................................156
logic................................................200


When the code is executed the code will run 3x as there are 3 occurances of "logic"

On "Service (1)" worksheet cell "D7" would equal 120
On "Service (2)" worksheet cell "D7" would equal 47
On "Service (3)" worksheet cell "D7" would equal 200



HELP !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[Norie]

And what would D26 hold?

The number of sheets to create?

A date?

An aardvark?

Sub create_serv
Dim I As Long
   For I = 1 To Worksheets("Service").Range("D26")
          Worksheets("Service").Visible = True 
          Worksheets("Service").Copy before:=Worksheets("data") 
          Worksheets("Service").Visible = False 
   Next I
End Sub

 

[dspa0712]

Updated requirements and no animals, bird or creatures involved !!!!

 

[D_Spark]

Can this be done ????
Only one reply, think this has beat the boffs !!!!!
If anyone can acheive the aim would like to find out

 

[dspa0712]

Me too and not sure it can be but wish to find out if it is acheivable....think its beyond my Excel capacity

 

[Norie]

Well I'm positive this can be done.

But I've a few problems - the requirement seems to have changed.

First D26 was mentioned, now you are referring to columns A & B and D7.

 

[dspa0712]

No sure Norie......

Your initial code works for the looping part although I had to change it slightly to reflect the requirement.

The prepopulating part is what I have problems with.

Can you assist????

Cheers

 

[Norie]

Could you please clarify what you actually want to do?

 

[dspa0712]

The correct brief as per what is recorded on my posting:

Wed Jul 25, 2007 2:06 pm

Where a peice of code will create a worksheet x amount of times taken from a master called "Service"

Then pre-populate a field on the newly created sheet "D7" with a value drawn from a table on a worksheet called "data service"

The number in cell "D7" is to be different for each occurance when a new sheet is created.

Cell "D6" referred to in my request is a common field between a sheet called "Provider" where a user selects a Provider Name and Column A values on "data service" worksheet where the lookup values are drawn from?

Hope this helps

 

[Norie]

Sorry but it doesn't make sense, to me anway.

What is the connection between D26, which seems to contain the no of sheets to create, and the amount of data in the data service sheet?

Surely what you actually want to do is create a copy of the Service worksheet for each item of data in the table in data service.