I think I have an array question...
 

I have a workbook that starts off with two worksheets. The first worksheet is
called "Data" and the second worksheet is call "results". In column A rows 1
through XXX (undertermined number of rows) I have a list of product names. I
would like to kick off a procedure after all of the product names are entered
that would copy each of those names to a copy of the "results" sheet in cell
D7. Can someone give me some help?

I think I have an array question...
 

sub Addsheets
Dim cell as Range, rng as Range
with worksheets("Data")
set rng = .Range(.Cells(2,1),.cells(2,1).End(xldown))
end with
for each cell in rng
worksheets("Results").copy After:=worksheets(worksheets.count)
Activesheet.name = cell
next
End sub

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote:

I have a workbook that starts off with two worksheets. The first worksheet is
called "Data" and the second worksheet is call "results". In column A rows 1
through XXX (undertermined number of rows) I have a list of product names. I
would like to kick off a procedure after all of the product names are entered
that would copy each of those names to a copy of the "results" sheet in cell
D7. Can someone give me some help?

I think I have an array question...
 

Thanks Tom! That worked well except it did not put the name of the product in
D7. It names the sheet correctly though.

"Tom Ogilvy" wrote:

sub Addsheets
Dim cell as Range, rng as Range
with worksheets("Data")
set rng = .Range(.Cells(2,1),.cells(2,1).End(xldown))
end with
for each cell in rng
worksheets("Results").copy After:=worksheets(worksheets.count)
Activesheet.name = cell
next
End sub

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote:

I have a workbook that starts off with two worksheets. The first worksheet is
called "Data" and the second worksheet is call "results". In column A rows 1
through XXX (undertermined number of rows) I have a list of product names. I
would like to kick off a procedure after all of the product names are entered
that would copy each of those names to a copy of the "results" sheet in cell
D7. Can someone give me some help?

I think I have an array question...
 

Maybe ...

sub Addsheets
Dim cell as Range, rng as Range
with worksheets("Data")
set rng = .Range(.Cells(2,1),.cells(2,1).End(xldown))
end with
for each cell in rng
worksheets("Results").copy After:=worksheets(worksheets.count)
Activesheet.name = cell
activesheet.range("D7").value = cell.value
next
End sub

hshayh0rn wrote:

Thanks Tom! That worked well except it did not put the name of the product in
D7. It names the sheet correctly though.

"Tom Ogilvy" wrote:

sub Addsheets
Dim cell as Range, rng as Range
with worksheets("Data")
set rng = .Range(.Cells(2,1),.cells(2,1).End(xldown))
end with
for each cell in rng
worksheets("Results").copy After:=worksheets(worksheets.count)
Activesheet.name = cell
next
End sub

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote:

I have a workbook that starts off with two worksheets. The first worksheet is
called "Data" and the second worksheet is call "results". In column A rows 1
through XXX (undertermined number of rows) I have a list of product names. I
would like to kick off a procedure after all of the product names are entered
that would copy each of those names to a copy of the "results" sheet in cell
D7. Can someone give me some help?

--

Dave Peterson

I think I have an array question...
 

I missed that requirement:

sub Addsheets
Dim cell as Range, rng as Range
with worksheets("Data")
set rng = .Range(.Cells(2,1),.cells(2,1).End(xldown))
end with
for each cell in rng
worksheets("Results").copy After:=worksheets(worksheets.count)
Activesheet.name = cell
Activesheet.Range("D7").Value = cell.Value
next
End sub

--
Regards,
Tom Ogilvy



"hshayh0rn" wrote:

Thanks Tom! That worked well except it did not put the name of the product in
D7. It names the sheet correctly though.

"Tom Ogilvy" wrote:

sub Addsheets
Dim cell as Range, rng as Range
with worksheets("Data")
set rng = .Range(.Cells(2,1),.cells(2,1).End(xldown))
end with
for each cell in rng
worksheets("Results").copy After:=worksheets(worksheets.count)
Activesheet.name = cell
next
End sub

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote:

I have a workbook that starts off with two worksheets. The first worksheet is
called "Data" and the second worksheet is call "results". In column A rows 1
through XXX (undertermined number of rows) I have a list of product names. I
would like to kick off a procedure after all of the product names are entered
that would copy each of those names to a copy of the "results" sheet in cell
D7. Can someone give me some help?

I think I have an array question...
 

Thanks Tom and Dave. Problem solved!

"Tom Ogilvy" wrote:

I missed that requirement:

sub Addsheets
Dim cell as Range, rng as Range
with worksheets("Data")
set rng = .Range(.Cells(2,1),.cells(2,1).End(xldown))
end with
for each cell in rng
worksheets("Results").copy After:=worksheets(worksheets.count)
Activesheet.name = cell
Activesheet.Range("D7").Value = cell.Value
next
End sub

--
Regards,
Tom Ogilvy



"hshayh0rn" wrote:

Thanks Tom! That worked well except it did not put the name of the product in
D7. It names the sheet correctly though.

"Tom Ogilvy" wrote:

sub Addsheets
Dim cell as Range, rng as Range
with worksheets("Data")
set rng = .Range(.Cells(2,1),.cells(2,1).End(xldown))
end with
for each cell in rng
worksheets("Results").copy After:=worksheets(worksheets.count)
Activesheet.name = cell
next
End sub

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote:

I have a workbook that starts off with two worksheets. The first worksheet is
called "Data" and the second worksheet is call "results". In column A rows 1
through XXX (undertermined number of rows) I have a list of product names. I
would like to kick off a procedure after all of the product names are entered
that would copy each of those names to a copy of the "results" sheet in cell
D7. Can someone give me some help?