Here is the problem, I have a worksheet in which there are 3 columns.
Column A contains the names of new workbooks, Column B contains the
names of the new worksheets within the new workbook, and Column C
contains the data for each worksheet within the workbook. My data
looks something like the following...
A B C
1 Close F Street A3590 Removals
2 A3600 Road Exc.
3 Temp Tie In A3630 Removals
4 A3640 Road Exc.
5 Connect to D Street A3660 Removals.

Is what I am trying to do even possible? To re-explain this with the
example above...
I want this to create a new Workbook called Close F Street, that
workbook will have 2 worksheets called A3590 and A 3600, and The
column C data will be in Cell 18, so Sheet A3590 will have Removals in
Cell 18 and Sheet A3600 will have Road Exc. in Cell 18.

I hope this makes sense. I have tried to accomplish this on my own
but can't get anywhere.

Thanks for your response.

Ryan

Ryan,

Try the macro below, first selecting your entire table. I assumes by Cell 18 you meant cell A18....
Also, change the string myPath to the folder path where you want to save these workbooks.

HTH,
Bernie
MS Excel MVP


Sub TryNow()
Dim myName As String
Dim myCell As Range
Dim CellAdd As String
Dim myPath As String

myPath = "C:\Excel\"

CellAdd = "A18"

myName = ""

For Each myCell In Intersect(Range("A:A"), Selection)

If myCell.Value < "" Then
If myName < "" Then
ActiveWorkbook.SaveAs myName, xlNormal
ActiveWorkbook.Close False
End If
myName = myPath & myCell.Value & ".xls"
Workbooks.Add
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = myCell(1, 2).Value
ActiveSheet.Range(CellAdd).Value = myCell(1, 3).Value
Else
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = myCell(1, 2).Value
ActiveSheet.Range(CellAdd).Value = myCell(1, 3).Value
End If
Next myCell

ActiveWorkbook.SaveAs myName, xlNormal
ActiveWorkbook.Close False

End Sub

"Midget" wrote in message
ups.com...
Here is the problem, I have a worksheet in which there are 3 columns.
Column A contains the names of new workbooks, Column B contains the
names of the new worksheets within the new workbook, and Column C
contains the data for each worksheet within the workbook. My data
looks something like the following...
A B C
1 Close F Street A3590 Removals
2 A3600 Road Exc.
3 Temp Tie In A3630 Removals
4 A3640 Road Exc.
5 Connect to D Street A3660 Removals.

Is what I am trying to do even possible? To re-explain this with the
example above...
I want this to create a new Workbook called Close F Street, that
workbook will have 2 worksheets called A3590 and A 3600, and The
column C data will be in Cell 18, so Sheet A3590 will have Removals in
Cell 18 and Sheet A3600 will have Road Exc. in Cell 18.

I hope this makes sense. I have tried to accomplish this on my own
but can't get anywhere.

Thanks for your response.

Ryan

On May 4, 12:22 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Ryan,

Try the macro below, first selecting your entire table. I assumes by Cell 18 you meant cell A18....
Also, change the string myPath to the folder path where you want to save these workbooks.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myName As String
Dim myCell As Range
Dim CellAdd As String
Dim myPath As String

myPath = "C:\Excel\"

CellAdd = "A18"

myName = ""

For Each myCell In Intersect(Range("A:A"), Selection)

If myCell.Value < "" Then
If myName < "" Then
ActiveWorkbook.SaveAs myName, xlNormal
ActiveWorkbook.Close False
End If
myName = myPath & myCell.Value & ".xls"
Workbooks.Add
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = myCell(1, 2).Value
ActiveSheet.Range(CellAdd).Value = myCell(1, 3).Value
Else
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = myCell(1, 2).Value
ActiveSheet.Range(CellAdd).Value = myCell(1, 3).Value
End If
Next myCell

ActiveWorkbook.SaveAs myName, xlNormal
ActiveWorkbook.Close False

End Sub

"Midget" wrote in message

ups.com...

Here is the problem, I have a worksheet in which there are 3 columns.
Column A contains the names of new workbooks, Column B contains the
names of the new worksheets within the new workbook, and Column C
contains the data for each worksheet within the workbook. My data
looks something like the following...
A B C
1 Close F Street A3590 Removals
2 A3600 Road Exc.
3 Temp Tie In A3630 Removals
4 A3640 Road Exc.
5 Connect to D Street A3660 Removals.

Is what I am trying to do even possible? To re-explain this with the
example above...
I want this to create a new Workbook called Close F Street, that
workbook will have 2 worksheets called A3590 and A 3600, and The
column C data will be in Cell 18, so Sheet A3590 will have Removals in
Cell 18 and Sheet A3600 will have Road Exc. in Cell 18.

I hope this makes sense. I have tried to accomplish this on my own
but can't get anywhere.

Thanks for your response.

Ryan


Bernie! You are amazing, That is exactly what I was trying to
accomplish! Lunch is on me if you are ever in Vegas!

Ryan,

Bernie! You are amazing, That is exactly what I was trying to
accomplish! Lunch is on me if you are ever in Vegas!

What a small world.... I'm actually planning on attending the Kitchen and Bath Industry Show in
Vegas this Monday through Thursday - but I have a full schedule of meetings, breakfasts, lunches,
and dinners... Thanks for the offer, and thanks for letting me know that the code worked for you

Bernie
MS Excel MVP

On May 4, 2:37 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Ryan,

Bernie! You are amazing, That is exactly what I was trying to
accomplish! Lunch is on me if you are ever in Vegas!

What a small world.... I'm actually planning on attending the Kitchen and Bath Industry Show in
Vegas this Monday through Thursday - but I have a full schedule of meetings, breakfasts, lunches,
and dinners... Thanks for the offer, and thanks for letting me know that the code worked for you

Bernie
MS Excel MVP

Well have fun in Vegas, and just in case you wanted a bit more
trivia....
Instead of just creating a new sheet, I want to copy a template I have
for the data to be placed in, and use that. I was able to add a few
lines to delete the default Sheet1,Sheet2 etc, but I was unable to get
the copy template deal to work. I didn't ask before because I want to
be able to learn this stuff, and you can't learn it unless you have
some trial and error. Here is the current code I am working with,
and I want to use a template sheet called "TEMPLATE". Thanks again
for all your help Bernie!

Sub TryNow3()
Dim myName As String
Dim myCell As Range
Dim CellAdd1 As String
Dim CellAdd2 As String
Dim CellAdd3 As String
Dim myPath As String

myPath = "C:\Documents and Settings\r***" ' path changed

'CellAdd1 = "A16"
CellAdd2 = "A18"
CellAdd3 = "E18"

myName = ""

For Each myCell In Intersect(Range("A:A"), Selection)

If myCell.Value < "" Then
If myName < "" Then
ActiveWorkbook.SaveAs myName, xlNormal
ActiveWorkbook.Close False
End If
myName = myPath & myCell.Value & ".xls"
Workbooks.Add
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = myCell(1, 2).Value
'ActiveSheet.Range(CellAdd1).Value = myCell(1, 1).Value
ActiveSheet.Range(CellAdd2).Value = myCell(1, 2).Value
ActiveSheet.Range(CellAdd3).Value = myCell(1, 3).Value
Else
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = myCell(1, 2).Value
'ActiveSheet.Range(CellAdd1).Value = myCell(1, 1).Value
ActiveSheet.Range(CellAdd2).Value = myCell(1, 2).Value
ActiveSheet.Range(CellAdd3).Value = myCell(1, 3).Value
'Delete Unused Sheets
'Turn off run time errors and delete alert
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Sheet1").Delete
Worksheets("Sheet2").Delete
Worksheets("Sheet3").Delete
End If
Next myCell

ActiveWorkbook.SaveAs myName, xlNormal
ActiveWorkbook.Close False

End Sub

Ryan,

The Workbooks.Add method allows you to specify the template: save your
template file as a template (xlt extension) then use

Workbooks.Add "C:\Folderpath\Template.xlt"

If you want to use a specific worksheet, you could store the worksheet in
the file with the macro, then copy that sheet and add the copy to the new
workbook as the basis of your sheets.

HTH,
Bernie
MS Excel MVP


Here is the current code I am working with,
and I want to use a template sheet called "TEMPLATE". Thanks again
for all your help Bernie!

You could also store the sheet template as SHEET.XLT in your xlstart folder and
add/insert to new books by using this code.

Sub Add_Sheets11()
'uses SHEET.XLT if one available in xlstart folder
'otherwise uses default sheet
For i = 2 To 1 Step -1
Sheets.Add(Type:="Worksheet").Name = "mynewsheet" & i
Next
End Sub


Gord Dibben MS Excel MVP


On Fri, 4 May 2007 20:09:08 -0400, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

Ryan,

The Workbooks.Add method allows you to specify the template: save your
template file as a template (xlt extension) then use

Workbooks.Add "C:\Folderpath\Template.xlt"

If you want to use a specific worksheet, you could store the worksheet in
the file with the macro, then copy that sheet and add the copy to the new
workbook as the basis of your sheets.

HTH,
Bernie
MS Excel MVP


Here is the current code I am working with,
and I want to use a template sheet called "TEMPLATE". Thanks again
for all your help Bernie!

On May 4, 5:09 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Ryan,

The Workbooks.Add method allows you to specify the template: save your
template file as a template (xlt extension) then use

Workbooks.Add "C:\Folderpath\Template.xlt"

If you want to use a specific worksheet, you could store the worksheet in
the file with the macro, then copy that sheet and add the copy to the new
workbook as the basis of your sheets.

HTH,
Bernie
MS Excel MVP

Here is the current code I am working with,
and I want to use a template sheet called "TEMPLATE". Thanks again
for all your help Bernie!

All this seems to do is copy my template as an additional sheet in my
workbooks. It doesn't use it as the basis of the creation of the new
sheets. So I end up with whatever number of sheets is automatically
generated, then a sheet called TEMPLATE at the end.

Got it figured out! Thanks for the help everyone!