Is it possible to make multple worksheets from a selection of multiple cells?
This would mean a selection of 10 cells would generate 10 sheets titled with
the cell conent.
Is it possible to make multple worksheets from a selection of multiple cells?
This would mean a selection of 10 cells would generate 10 sheets titled with
the cell conent.
Here's some code that will go down from A1 to A? (until it runs into an empty
cell) on the "Master" sheet and will create and name sheet according to what
is in each cell.
HTH
JonR
Sub NewSheet()
'This assumes the cells you wish to name the sheets after are in Column A
'on the "Master" worksheet.
'inX is a variable used to count down the rows.
Dim inX As Integer
Dim stName As String
inX = 1
Do Until Cells(inX, 1).Value = ""
stName = Cells(inX, 1).Value
Sheets.Add
ActiveSheet.Name = stName
Worksheets("Master").Activate
inX = inX + 1
Loop
End Sub
"Robert Maddox" wrote:
> Is it possible to make multple worksheets from a selection of multiple cells?
> This would mean a selection of 10 cells would generate 10 sheets titled with
> the cell conent.
How can this be modified to "look" at only a specific list of names to create
the sheets?
Thanks,
anna
"JonR" wrote:
> Here's some code that will go down from A1 to A? (until it runs into an empty
> cell) on the "Master" sheet and will create and name sheet according to what
> is in each cell.
>
> HTH
>
> JonR
>
> Sub NewSheet()
>
> 'This assumes the cells you wish to name the sheets after are in Column A
> 'on the "Master" worksheet.
> 'inX is a variable used to count down the rows.
>
>
> Dim inX As Integer
>
> Dim stName As String
>
> inX = 1
>
> Do Until Cells(inX, 1).Value = ""
>
> stName = Cells(inX, 1).Value
>
> Sheets.Add
>
> ActiveSheet.Name = stName
>
> Worksheets("Master").Activate
>
> inX = inX + 1
>
> Loop
>
> End Sub
>
>
>
> "Robert Maddox" wrote:
>
> > Is it possible to make multple worksheets from a selection of multiple cells?
> > This would mean a selection of 10 cells would generate 10 sheets titled with
> > the cell conent.
The way this is written, it goes down Column A one cell at a time. Cell
references in VBA are Cells(row,column), and you can see in the do While loop
that the row is being incremented as each sheet is created.
If you have certain criteria that you want to meet, you could do it with If
statements or maybe a Select Case argument, depending on your needs. It
might help if you could send an example of what you're trying to do so I can
help meet your needs.
--
HTH
JonR
"anna" wrote:
> How can this be modified to "look" at only a specific list of names to create
> the sheets?
>
> Thanks,
> anna
>
> "JonR" wrote:
>
> > Here's some code that will go down from A1 to A? (until it runs into an empty
> > cell) on the "Master" sheet and will create and name sheet according to what
> > is in each cell.
> >
> > HTH
> >
> > JonR
> >
> > Sub NewSheet()
> >
> > 'This assumes the cells you wish to name the sheets after are in Column A
> > 'on the "Master" worksheet.
> > 'inX is a variable used to count down the rows.
> >
> >
> > Dim inX As Integer
> >
> > Dim stName As String
> >
> > inX = 1
> >
> > Do Until Cells(inX, 1).Value = ""
> >
> > stName = Cells(inX, 1).Value
> >
> > Sheets.Add
> >
> > ActiveSheet.Name = stName
> >
> > Worksheets("Master").Activate
> >
> > inX = inX + 1
> >
> > Loop
> >
> > End Sub
> >
> >
> >
> > "Robert Maddox" wrote:
> >
> > > Is it possible to make multple worksheets from a selection of multiple cells?
> > > This would mean a selection of 10 cells would generate 10 sheets titled with
> > > the cell conent.
Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
the list starting in A5. Other users would input their data here so the list
could vary in length. I would like separate sheets generated with these names
so then the users could input results per "person" in their respective tabs.
This would include 3 columns... such as column one = "date" , column 2 =
"time", and column 3 = "result data". I think I figured out how to summarize
the data in a separate "summary" sheet by referring to the sheet name and
meeting multiple criteria (using this very helpful site), I just need this
last bit of info and I'm set! Of course if you can think of a better way to
do this, then please share. Thanks so much for you help!!
Anna
"JonR" wrote:
> The way this is written, it goes down Column A one cell at a time. Cell
> references in VBA are Cells(row,column), and you can see in the do While loop
> that the row is being incremented as each sheet is created.
>
> If you have certain criteria that you want to meet, you could do it with If
> statements or maybe a Select Case argument, depending on your needs. It
> might help if you could send an example of what you're trying to do so I can
> help meet your needs.
> --
> HTH
>
> JonR
>
>
> "anna" wrote:
>
> > How can this be modified to "look" at only a specific list of names to create
> > the sheets?
> >
> > Thanks,
> > anna
> >
> > "JonR" wrote:
> >
> > > Here's some code that will go down from A1 to A? (until it runs into an empty
> > > cell) on the "Master" sheet and will create and name sheet according to what
> > > is in each cell.
> > >
> > > HTH
> > >
> > > JonR
> > >
> > > Sub NewSheet()
> > >
> > > 'This assumes the cells you wish to name the sheets after are in Column A
> > > 'on the "Master" worksheet.
> > > 'inX is a variable used to count down the rows.
> > >
> > >
> > > Dim inX As Integer
> > >
> > > Dim stName As String
> > >
> > > inX = 1
> > >
> > > Do Until Cells(inX, 1).Value = ""
> > >
> > > stName = Cells(inX, 1).Value
> > >
> > > Sheets.Add
> > >
> > > ActiveSheet.Name = stName
> > >
> > > Worksheets("Master").Activate
> > >
> > > inX = inX + 1
> > >
> > > Loop
> > >
> > > End Sub
> > >
> > >
> > >
> > > "Robert Maddox" wrote:
> > >
> > > > Is it possible to make multple worksheets from a selection of multiple cells?
> > > > This would mean a selection of 10 cells would generate 10 sheets titled with
> > > > the cell conent.
Replace the line inX=1 with inX=5. Then when the code gets to the statement
Do While Cells(inX,1)...., it will start at the cell on Row(inX) Column(1),
which is A5.
The way it's written, this code will run until it hits a blank in column A.
If you need to skip a blank, or want to stop, start, or skip certian cells,
you'll need to add some If statements.
--
HTH
JonR
"anna" wrote:
> Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
> the list starting in A5. Other users would input their data here so the list
> could vary in length. I would like separate sheets generated with these names
> so then the users could input results per "person" in their respective tabs.
> This would include 3 columns... such as column one = "date" , column 2 =
> "time", and column 3 = "result data". I think I figured out how to summarize
> the data in a separate "summary" sheet by referring to the sheet name and
> meeting multiple criteria (using this very helpful site), I just need this
> last bit of info and I'm set! Of course if you can think of a better way to
> do this, then please share. Thanks so much for you help!!
> Anna
>
> "JonR" wrote:
>
> > The way this is written, it goes down Column A one cell at a time. Cell
> > references in VBA are Cells(row,column), and you can see in the do While loop
> > that the row is being incremented as each sheet is created.
> >
> > If you have certain criteria that you want to meet, you could do it with If
> > statements or maybe a Select Case argument, depending on your needs. It
> > might help if you could send an example of what you're trying to do so I can
> > help meet your needs.
> > --
> > HTH
> >
> > JonR
> >
> >
> > "anna" wrote:
> >
> > > How can this be modified to "look" at only a specific list of names to create
> > > the sheets?
> > >
> > > Thanks,
> > > anna
> > >
> > > "JonR" wrote:
> > >
> > > > Here's some code that will go down from A1 to A? (until it runs into an empty
> > > > cell) on the "Master" sheet and will create and name sheet according to what
> > > > is in each cell.
> > > >
> > > > HTH
> > > >
> > > > JonR
> > > >
> > > > Sub NewSheet()
> > > >
> > > > 'This assumes the cells you wish to name the sheets after are in Column A
> > > > 'on the "Master" worksheet.
> > > > 'inX is a variable used to count down the rows.
> > > >
> > > >
> > > > Dim inX As Integer
> > > >
> > > > Dim stName As String
> > > >
> > > > inX = 1
> > > >
> > > > Do Until Cells(inX, 1).Value = ""
> > > >
> > > > stName = Cells(inX, 1).Value
> > > >
> > > > Sheets.Add
> > > >
> > > > ActiveSheet.Name = stName
> > > >
> > > > Worksheets("Master").Activate
> > > >
> > > > inX = inX + 1
> > > >
> > > > Loop
> > > >
> > > > End Sub
> > > >
> > > >
> > > >
> > > > "Robert Maddox" wrote:
> > > >
> > > > > Is it possible to make multple worksheets from a selection of multiple cells?
> > > > > This would mean a selection of 10 cells would generate 10 sheets titled with
> > > > > the cell conent.
I must be missing something or just a Visual Basic dummy... I copied the text
from "Sub NewSheet()" to "End Sub" and replaced the "inX = 1" to "inX = 5" in
a Visual Basic module in my workbook. Now what? Dumb question, but how do I
get it to work?
Thanks in advanced for not laughing at me,
anna
"JonR" wrote:
> Replace the line inX=1 with inX=5. Then when the code gets to the statement
> Do While Cells(inX,1)...., it will start at the cell on Row(inX) Column(1),
> which is A5.
>
> The way it's written, this code will run until it hits a blank in column A.
> If you need to skip a blank, or want to stop, start, or skip certian cells,
> you'll need to add some If statements.
>
> --
> HTH
>
> JonR
>
>
> "anna" wrote:
>
> > Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
> > the list starting in A5. Other users would input their data here so the list
> > could vary in length. I would like separate sheets generated with these names
> > so then the users could input results per "person" in their respective tabs.
> > This would include 3 columns... such as column one = "date" , column 2 =
> > "time", and column 3 = "result data". I think I figured out how to summarize
> > the data in a separate "summary" sheet by referring to the sheet name and
> > meeting multiple criteria (using this very helpful site), I just need this
> > last bit of info and I'm set! Of course if you can think of a better way to
> > do this, then please share. Thanks so much for you help!!
> > Anna
> >
> > "JonR" wrote:
> >
> > > The way this is written, it goes down Column A one cell at a time. Cell
> > > references in VBA are Cells(row,column), and you can see in the do While loop
> > > that the row is being incremented as each sheet is created.
> > >
> > > If you have certain criteria that you want to meet, you could do it with If
> > > statements or maybe a Select Case argument, depending on your needs. It
> > > might help if you could send an example of what you're trying to do so I can
> > > help meet your needs.
> > > --
> > > HTH
> > >
> > > JonR
> > >
> > >
> > > "anna" wrote:
> > >
> > > > How can this be modified to "look" at only a specific list of names to create
> > > > the sheets?
> > > >
> > > > Thanks,
> > > > anna
> > > >
> > > > "JonR" wrote:
> > > >
> > > > > Here's some code that will go down from A1 to A? (until it runs into an empty
> > > > > cell) on the "Master" sheet and will create and name sheet according to what
> > > > > is in each cell.
> > > > >
> > > > > HTH
> > > > >
> > > > > JonR
> > > > >
> > > > > Sub NewSheet()
> > > > >
> > > > > 'This assumes the cells you wish to name the sheets after are in Column A
> > > > > 'on the "Master" worksheet.
> > > > > 'inX is a variable used to count down the rows.
> > > > >
> > > > >
> > > > > Dim inX As Integer
> > > > >
> > > > > Dim stName As String
> > > > >
> > > > > inX = 1
> > > > >
> > > > > Do Until Cells(inX, 1).Value = ""
> > > > >
> > > > > stName = Cells(inX, 1).Value
> > > > >
> > > > > Sheets.Add
> > > > >
> > > > > ActiveSheet.Name = stName
> > > > >
> > > > > Worksheets("Master").Activate
> > > > >
> > > > > inX = inX + 1
> > > > >
> > > > > Loop
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > >
> > > > > "Robert Maddox" wrote:
> > > > >
> > > > > > Is it possible to make multple worksheets from a selection of multiple cells?
> > > > > > This would mean a selection of 10 cells would generate 10 sheets titled with
> > > > > > the cell conent.
At the top of your VB window, you'll see some buttons that look like controls
for the media player, with an arrow (to run), a square (to stop) and one with
two parallel bars to puasse (that one should be disabled if the code isn't
running).. Just click the "play" button.
--
HTH
JonR
"anna" wrote:
> I must be missing something or just a Visual Basic dummy... I copied the text
> from "Sub NewSheet()" to "End Sub" and replaced the "inX = 1" to "inX = 5" in
> a Visual Basic module in my workbook. Now what? Dumb question, but how do I
> get it to work?
> Thanks in advanced for not laughing at me,
> anna
>
> "JonR" wrote:
>
> > Replace the line inX=1 with inX=5. Then when the code gets to the statement
> > Do While Cells(inX,1)...., it will start at the cell on Row(inX) Column(1),
> > which is A5.
> >
> > The way it's written, this code will run until it hits a blank in column A.
> > If you need to skip a blank, or want to stop, start, or skip certian cells,
> > you'll need to add some If statements.
> >
> > --
> > HTH
> >
> > JonR
> >
> >
> > "anna" wrote:
> >
> > > Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
> > > the list starting in A5. Other users would input their data here so the list
> > > could vary in length. I would like separate sheets generated with these names
> > > so then the users could input results per "person" in their respective tabs.
> > > This would include 3 columns... such as column one = "date" , column 2 =
> > > "time", and column 3 = "result data". I think I figured out how to summarize
> > > the data in a separate "summary" sheet by referring to the sheet name and
> > > meeting multiple criteria (using this very helpful site), I just need this
> > > last bit of info and I'm set! Of course if you can think of a better way to
> > > do this, then please share. Thanks so much for you help!!
> > > Anna
> > >
> > > "JonR" wrote:
> > >
> > > > The way this is written, it goes down Column A one cell at a time. Cell
> > > > references in VBA are Cells(row,column), and you can see in the do While loop
> > > > that the row is being incremented as each sheet is created.
> > > >
> > > > If you have certain criteria that you want to meet, you could do it with If
> > > > statements or maybe a Select Case argument, depending on your needs. It
> > > > might help if you could send an example of what you're trying to do so I can
> > > > help meet your needs.
> > > > --
> > > > HTH
> > > >
> > > > JonR
> > > >
> > > >
> > > > "anna" wrote:
> > > >
> > > > > How can this be modified to "look" at only a specific list of names to create
> > > > > the sheets?
> > > > >
> > > > > Thanks,
> > > > > anna
> > > > >
> > > > > "JonR" wrote:
> > > > >
> > > > > > Here's some code that will go down from A1 to A? (until it runs into an empty
> > > > > > cell) on the "Master" sheet and will create and name sheet according to what
> > > > > > is in each cell.
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > JonR
> > > > > >
> > > > > > Sub NewSheet()
> > > > > >
> > > > > > 'This assumes the cells you wish to name the sheets after are in Column A
> > > > > > 'on the "Master" worksheet.
> > > > > > 'inX is a variable used to count down the rows.
> > > > > >
> > > > > >
> > > > > > Dim inX As Integer
> > > > > >
> > > > > > Dim stName As String
> > > > > >
> > > > > > inX = 1
> > > > > >
> > > > > > Do Until Cells(inX, 1).Value = ""
> > > > > >
> > > > > > stName = Cells(inX, 1).Value
> > > > > >
> > > > > > Sheets.Add
> > > > > >
> > > > > > ActiveSheet.Name = stName
> > > > > >
> > > > > > Worksheets("Master").Activate
> > > > > >
> > > > > > inX = inX + 1
> > > > > >
> > > > > > Loop
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Robert Maddox" wrote:
> > > > > >
> > > > > > > Is it possible to make multple worksheets from a selection of multiple cells?
> > > > > > > This would mean a selection of 10 cells would generate 10 sheets titled with
> > > > > > > the cell conent.
Cool. Thanks!
I don't suppose there is something you could write in this VB macro that
will put the headers in the sheets also? Such as A1 ="Date", B1="Time",
C1="Results" when the new sheets are created?
anna
"JonR" wrote:
> At the top of your VB window, you'll see some buttons that look like controls
> for the media player, with an arrow (to run), a square (to stop) and one with
> two parallel bars to puasse (that one should be disabled if the code isn't
> running).. Just click the "play" button.
> --
> HTH
>
> JonR
>
>
> "anna" wrote:
>
> > I must be missing something or just a Visual Basic dummy... I copied the text
> > from "Sub NewSheet()" to "End Sub" and replaced the "inX = 1" to "inX = 5" in
> > a Visual Basic module in my workbook. Now what? Dumb question, but how do I
> > get it to work?
> > Thanks in advanced for not laughing at me,
> > anna
> >
> > "JonR" wrote:
> >
> > > Replace the line inX=1 with inX=5. Then when the code gets to the statement
> > > Do While Cells(inX,1)...., it will start at the cell on Row(inX) Column(1),
> > > which is A5.
> > >
> > > The way it's written, this code will run until it hits a blank in column A.
> > > If you need to skip a blank, or want to stop, start, or skip certian cells,
> > > you'll need to add some If statements.
> > >
> > > --
> > > HTH
> > >
> > > JonR
> > >
> > >
> > > "anna" wrote:
> > >
> > > > Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
> > > > the list starting in A5. Other users would input their data here so the list
> > > > could vary in length. I would like separate sheets generated with these names
> > > > so then the users could input results per "person" in their respective tabs.
> > > > This would include 3 columns... such as column one = "date" , column 2 =
> > > > "time", and column 3 = "result data". I think I figured out how to summarize
> > > > the data in a separate "summary" sheet by referring to the sheet name and
> > > > meeting multiple criteria (using this very helpful site), I just need this
> > > > last bit of info and I'm set! Of course if you can think of a better way to
> > > > do this, then please share. Thanks so much for you help!!
> > > > Anna
> > > >
> > > > "JonR" wrote:
> > > >
> > > > > The way this is written, it goes down Column A one cell at a time. Cell
> > > > > references in VBA are Cells(row,column), and you can see in the do While loop
> > > > > that the row is being incremented as each sheet is created.
> > > > >
> > > > > If you have certain criteria that you want to meet, you could do it with If
> > > > > statements or maybe a Select Case argument, depending on your needs. It
> > > > > might help if you could send an example of what you're trying to do so I can
> > > > > help meet your needs.
> > > > > --
> > > > > HTH
> > > > >
> > > > > JonR
> > > > >
> > > > >
> > > > > "anna" wrote:
> > > > >
> > > > > > How can this be modified to "look" at only a specific list of names to create
> > > > > > the sheets?
> > > > > >
> > > > > > Thanks,
> > > > > > anna
> > > > > >
> > > > > > "JonR" wrote:
> > > > > >
> > > > > > > Here's some code that will go down from A1 to A? (until it runs into an empty
> > > > > > > cell) on the "Master" sheet and will create and name sheet according to what
> > > > > > > is in each cell.
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > JonR
> > > > > > >
> > > > > > > Sub NewSheet()
> > > > > > >
> > > > > > > 'This assumes the cells you wish to name the sheets after are in Column A
> > > > > > > 'on the "Master" worksheet.
> > > > > > > 'inX is a variable used to count down the rows.
> > > > > > >
> > > > > > >
> > > > > > > Dim inX As Integer
> > > > > > >
> > > > > > > Dim stName As String
> > > > > > >
> > > > > > > inX = 1
> > > > > > >
> > > > > > > Do Until Cells(inX, 1).Value = ""
> > > > > > >
> > > > > > > stName = Cells(inX, 1).Value
> > > > > > >
> > > > > > > Sheets.Add
> > > > > > >
> > > > > > > ActiveSheet.Name = stName
> > > > > > >
> > > > > > > Worksheets("Master").Activate
> > > > > > >
> > > > > > > inX = inX + 1
> > > > > > >
> > > > > > > Loop
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Robert Maddox" wrote:
> > > > > > >
> > > > > > > > Is it possible to make multple worksheets from a selection of multiple cells?
> > > > > > > > This would mean a selection of 10 cells would generate 10 sheets titled with
> > > > > > > > the cell conent.
Insert these lines between the lines 'Activesheet.Name = stName' and
'Worksheets("Master").Activate'
Cells(1,1).Value = "Date"
Cells(1,2).Value = "Time"
Cells(1,3).Value = "Results"
--
HTH
JonR
"anna" wrote:
> Cool. Thanks!
> I don't suppose there is something you could write in this VB macro that
> will put the headers in the sheets also? Such as A1 ="Date", B1="Time",
> C1="Results" when the new sheets are created?
> anna
>
> "JonR" wrote:
>
> > At the top of your VB window, you'll see some buttons that look like controls
> > for the media player, with an arrow (to run), a square (to stop) and one with
> > two parallel bars to puasse (that one should be disabled if the code isn't
> > running).. Just click the "play" button.
> > --
> > HTH
> >
> > JonR
> >
> >
> > "anna" wrote:
> >
> > > I must be missing something or just a Visual Basic dummy... I copied the text
> > > from "Sub NewSheet()" to "End Sub" and replaced the "inX = 1" to "inX = 5" in
> > > a Visual Basic module in my workbook. Now what? Dumb question, but how do I
> > > get it to work?
> > > Thanks in advanced for not laughing at me,
> > > anna
> > >
> > > "JonR" wrote:
> > >
> > > > Replace the line inX=1 with inX=5. Then when the code gets to the statement
> > > > Do While Cells(inX,1)...., it will start at the cell on Row(inX) Column(1),
> > > > which is A5.
> > > >
> > > > The way it's written, this code will run until it hits a blank in column A.
> > > > If you need to skip a blank, or want to stop, start, or skip certian cells,
> > > > you'll need to add some If statements.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > JonR
> > > >
> > > >
> > > > "anna" wrote:
> > > >
> > > > > Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
> > > > > the list starting in A5. Other users would input their data here so the list
> > > > > could vary in length. I would like separate sheets generated with these names
> > > > > so then the users could input results per "person" in their respective tabs.
> > > > > This would include 3 columns... such as column one = "date" , column 2 =
> > > > > "time", and column 3 = "result data". I think I figured out how to summarize
> > > > > the data in a separate "summary" sheet by referring to the sheet name and
> > > > > meeting multiple criteria (using this very helpful site), I just need this
> > > > > last bit of info and I'm set! Of course if you can think of a better way to
> > > > > do this, then please share. Thanks so much for you help!!
> > > > > Anna
> > > > >
> > > > > "JonR" wrote:
> > > > >
> > > > > > The way this is written, it goes down Column A one cell at a time. Cell
> > > > > > references in VBA are Cells(row,column), and you can see in the do While loop
> > > > > > that the row is being incremented as each sheet is created.
> > > > > >
> > > > > > If you have certain criteria that you want to meet, you could do it with If
> > > > > > statements or maybe a Select Case argument, depending on your needs. It
> > > > > > might help if you could send an example of what you're trying to do so I can
> > > > > > help meet your needs.
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > JonR
> > > > > >
> > > > > >
> > > > > > "anna" wrote:
> > > > > >
> > > > > > > How can this be modified to "look" at only a specific list of names to create
> > > > > > > the sheets?
> > > > > > >
> > > > > > > Thanks,
> > > > > > > anna
> > > > > > >
> > > > > > > "JonR" wrote:
> > > > > > >
> > > > > > > > Here's some code that will go down from A1 to A? (until it runs into an empty
> > > > > > > > cell) on the "Master" sheet and will create and name sheet according to what
> > > > > > > > is in each cell.
> > > > > > > >
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > JonR
> > > > > > > >
> > > > > > > > Sub NewSheet()
> > > > > > > >
> > > > > > > > 'This assumes the cells you wish to name the sheets after are in Column A
> > > > > > > > 'on the "Master" worksheet.
> > > > > > > > 'inX is a variable used to count down the rows.
> > > > > > > >
> > > > > > > >
> > > > > > > > Dim inX As Integer
> > > > > > > >
> > > > > > > > Dim stName As String
> > > > > > > >
> > > > > > > > inX = 1
> > > > > > > >
> > > > > > > > Do Until Cells(inX, 1).Value = ""
> > > > > > > >
> > > > > > > > stName = Cells(inX, 1).Value
> > > > > > > >
> > > > > > > > Sheets.Add
> > > > > > > >
> > > > > > > > ActiveSheet.Name = stName
> > > > > > > >
> > > > > > > > Worksheets("Master").Activate
> > > > > > > >
> > > > > > > > inX = inX + 1
> > > > > > > >
> > > > > > > > Loop
> > > > > > > >
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Robert Maddox" wrote:
> > > > > > > >
> > > > > > > > > Is it possible to make multple worksheets from a selection of multiple cells?
> > > > > > > > > This would mean a selection of 10 cells would generate 10 sheets titled with
> > > > > > > > > the cell conent.
It only changed the cells in the "Master" worksheet.
Sorry for trouble.
anna
"JonR" wrote:
> Insert these lines between the lines 'Activesheet.Name = stName' and
> 'Worksheets("Master").Activate'
>
> Cells(1,1).Value = "Date"
> Cells(1,2).Value = "Time"
> Cells(1,3).Value = "Results"
>
> --
> HTH
>
> JonR
>
>
> "anna" wrote:
>
> > Cool. Thanks!
> > I don't suppose there is something you could write in this VB macro that
> > will put the headers in the sheets also? Such as A1 ="Date", B1="Time",
> > C1="Results" when the new sheets are created?
> > anna
> >
> > "JonR" wrote:
> >
> > > At the top of your VB window, you'll see some buttons that look like controls
> > > for the media player, with an arrow (to run), a square (to stop) and one with
> > > two parallel bars to puasse (that one should be disabled if the code isn't
> > > running).. Just click the "play" button.
> > > --
> > > HTH
> > >
> > > JonR
> > >
> > >
> > > "anna" wrote:
> > >
> > > > I must be missing something or just a Visual Basic dummy... I copied the text
> > > > from "Sub NewSheet()" to "End Sub" and replaced the "inX = 1" to "inX = 5" in
> > > > a Visual Basic module in my workbook. Now what? Dumb question, but how do I
> > > > get it to work?
> > > > Thanks in advanced for not laughing at me,
> > > > anna
> > > >
> > > > "JonR" wrote:
> > > >
> > > > > Replace the line inX=1 with inX=5. Then when the code gets to the statement
> > > > > Do While Cells(inX,1)...., it will start at the cell on Row(inX) Column(1),
> > > > > which is A5.
> > > > >
> > > > > The way it's written, this code will run until it hits a blank in column A.
> > > > > If you need to skip a blank, or want to stop, start, or skip certian cells,
> > > > > you'll need to add some If statements.
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > JonR
> > > > >
> > > > >
> > > > > "anna" wrote:
> > > > >
> > > > > > Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
> > > > > > the list starting in A5. Other users would input their data here so the list
> > > > > > could vary in length. I would like separate sheets generated with these names
> > > > > > so then the users could input results per "person" in their respective tabs.
> > > > > > This would include 3 columns... such as column one = "date" , column 2 =
> > > > > > "time", and column 3 = "result data". I think I figured out how to summarize
> > > > > > the data in a separate "summary" sheet by referring to the sheet name and
> > > > > > meeting multiple criteria (using this very helpful site), I just need this
> > > > > > last bit of info and I'm set! Of course if you can think of a better way to
> > > > > > do this, then please share. Thanks so much for you help!!
> > > > > > Anna
> > > > > >
> > > > > > "JonR" wrote:
> > > > > >
> > > > > > > The way this is written, it goes down Column A one cell at a time. Cell
> > > > > > > references in VBA are Cells(row,column), and you can see in the do While loop
> > > > > > > that the row is being incremented as each sheet is created.
> > > > > > >
> > > > > > > If you have certain criteria that you want to meet, you could do it with If
> > > > > > > statements or maybe a Select Case argument, depending on your needs. It
> > > > > > > might help if you could send an example of what you're trying to do so I can
> > > > > > > help meet your needs.
> > > > > > > --
> > > > > > > HTH
> > > > > > >
> > > > > > > JonR
> > > > > > >
> > > > > > >
> > > > > > > "anna" wrote:
> > > > > > >
> > > > > > > > How can this be modified to "look" at only a specific list of names to create
> > > > > > > > the sheets?
> > > > > > > >
> > > > > > > > Thanks,
> > > > > > > > anna
> > > > > > > >
> > > > > > > > "JonR" wrote:
> > > > > > > >
> > > > > > > > > Here's some code that will go down from A1 to A? (until it runs into an empty
> > > > > > > > > cell) on the "Master" sheet and will create and name sheet according to what
> > > > > > > > > is in each cell.
> > > > > > > > >
> > > > > > > > > HTH
> > > > > > > > >
> > > > > > > > > JonR
> > > > > > > > >
> > > > > > > > > Sub NewSheet()
> > > > > > > > >
> > > > > > > > > 'This assumes the cells you wish to name the sheets after are in Column A
> > > > > > > > > 'on the "Master" worksheet.
> > > > > > > > > 'inX is a variable used to count down the rows.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Dim inX As Integer
> > > > > > > > >
> > > > > > > > > Dim stName As String
> > > > > > > > >
> > > > > > > > > inX = 1
> > > > > > > > >
> > > > > > > > > Do Until Cells(inX, 1).Value = ""
> > > > > > > > >
> > > > > > > > > stName = Cells(inX, 1).Value
> > > > > > > > >
> > > > > > > > > Sheets.Add
> > > > > > > > >
> > > > > > > > > ActiveSheet.Name = stName
> > > > > > > > >
> > > > > > > > > Worksheets("Master").Activate
> > > > > > > > >
> > > > > > > > > inX = inX + 1
> > > > > > > > >
> > > > > > > > > Loop
> > > > > > > > >
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Robert Maddox" wrote:
> > > > > > > > >
> > > > > > > > > > Is it possible to make multple worksheets from a selection of multiple cells?
> > > > > > > > > > This would mean a selection of 10 cells would generate 10 sheets titled with
> > > > > > > > > > the cell conent.
it shouldn't have done that, but here's a fix
put this line right after ActiveSheet.Name = stName
Worksheets(stName).Activate
--
HTH
JonR
"anna" wrote:
> It only changed the cells in the "Master" worksheet.
> Sorry for trouble.
> anna
>
> "JonR" wrote:
>
> > Insert these lines between the lines 'Activesheet.Name = stName' and
> > 'Worksheets("Master").Activate'
> >
> > Cells(1,1).Value = "Date"
> > Cells(1,2).Value = "Time"
> > Cells(1,3).Value = "Results"
> >
> > --
> > HTH
> >
> > JonR
> >
> >
> > "anna" wrote:
> >
> > > Cool. Thanks!
> > > I don't suppose there is something you could write in this VB macro that
> > > will put the headers in the sheets also? Such as A1 ="Date", B1="Time",
> > > C1="Results" when the new sheets are created?
> > > anna
> > >
> > > "JonR" wrote:
> > >
> > > > At the top of your VB window, you'll see some buttons that look like controls
> > > > for the media player, with an arrow (to run), a square (to stop) and one with
> > > > two parallel bars to puasse (that one should be disabled if the code isn't
> > > > running).. Just click the "play" button.
> > > > --
> > > > HTH
> > > >
> > > > JonR
> > > >
> > > >
> > > > "anna" wrote:
> > > >
> > > > > I must be missing something or just a Visual Basic dummy... I copied the text
> > > > > from "Sub NewSheet()" to "End Sub" and replaced the "inX = 1" to "inX = 5" in
> > > > > a Visual Basic module in my workbook. Now what? Dumb question, but how do I
> > > > > get it to work?
> > > > > Thanks in advanced for not laughing at me,
> > > > > anna
> > > > >
> > > > > "JonR" wrote:
> > > > >
> > > > > > Replace the line inX=1 with inX=5. Then when the code gets to the statement
> > > > > > Do While Cells(inX,1)...., it will start at the cell on Row(inX) Column(1),
> > > > > > which is A5.
> > > > > >
> > > > > > The way it's written, this code will run until it hits a blank in column A.
> > > > > > If you need to skip a blank, or want to stop, start, or skip certian cells,
> > > > > > you'll need to add some If statements.
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > JonR
> > > > > >
> > > > > >
> > > > > > "anna" wrote:
> > > > > >
> > > > > > > Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
> > > > > > > the list starting in A5. Other users would input their data here so the list
> > > > > > > could vary in length. I would like separate sheets generated with these names
> > > > > > > so then the users could input results per "person" in their respective tabs.
> > > > > > > This would include 3 columns... such as column one = "date" , column 2 =
> > > > > > > "time", and column 3 = "result data". I think I figured out how to summarize
> > > > > > > the data in a separate "summary" sheet by referring to the sheet name and
> > > > > > > meeting multiple criteria (using this very helpful site), I just need this
> > > > > > > last bit of info and I'm set! Of course if you can think of a better way to
> > > > > > > do this, then please share. Thanks so much for you help!!
> > > > > > > Anna
> > > > > > >
> > > > > > > "JonR" wrote:
> > > > > > >
> > > > > > > > The way this is written, it goes down Column A one cell at a time. Cell
> > > > > > > > references in VBA are Cells(row,column), and you can see in the do While loop
> > > > > > > > that the row is being incremented as each sheet is created.
> > > > > > > >
> > > > > > > > If you have certain criteria that you want to meet, you could do it with If
> > > > > > > > statements or maybe a Select Case argument, depending on your needs. It
> > > > > > > > might help if you could send an example of what you're trying to do so I can
> > > > > > > > help meet your needs.
> > > > > > > > --
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > JonR
> > > > > > > >
> > > > > > > >
> > > > > > > > "anna" wrote:
> > > > > > > >
> > > > > > > > > How can this be modified to "look" at only a specific list of names to create
> > > > > > > > > the sheets?
> > > > > > > > >
> > > > > > > > > Thanks,
> > > > > > > > > anna
> > > > > > > > >
> > > > > > > > > "JonR" wrote:
> > > > > > > > >
> > > > > > > > > > Here's some code that will go down from A1 to A? (until it runs into an empty
> > > > > > > > > > cell) on the "Master" sheet and will create and name sheet according to what
> > > > > > > > > > is in each cell.
> > > > > > > > > >
> > > > > > > > > > HTH
> > > > > > > > > >
> > > > > > > > > > JonR
> > > > > > > > > >
> > > > > > > > > > Sub NewSheet()
> > > > > > > > > >
> > > > > > > > > > 'This assumes the cells you wish to name the sheets after are in Column A
> > > > > > > > > > 'on the "Master" worksheet.
> > > > > > > > > > 'inX is a variable used to count down the rows.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > Dim inX As Integer
> > > > > > > > > >
> > > > > > > > > > Dim stName As String
> > > > > > > > > >
> > > > > > > > > > inX = 1
> > > > > > > > > >
> > > > > > > > > > Do Until Cells(inX, 1).Value = ""
> > > > > > > > > >
> > > > > > > > > > stName = Cells(inX, 1).Value
> > > > > > > > > >
> > > > > > > > > > Sheets.Add
> > > > > > > > > >
> > > > > > > > > > ActiveSheet.Name = stName
> > > > > > > > > >
> > > > > > > > > > Worksheets("Master").Activate
> > > > > > > > > >
> > > > > > > > > > inX = inX + 1
> > > > > > > > > >
> > > > > > > > > > Loop
> > > > > > > > > >
> > > > > > > > > > End Sub
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "Robert Maddox" wrote:
> > > > > > > > > >
> > > > > > > > > > > Is it possible to make multple worksheets from a selection of multiple cells?
> > > > > > > > > > > This would mean a selection of 10 cells would generate 10 sheets titled with
> > > > > > > > > > > the cell conent.
Same problem. here's the macro (believe it or not I combined it with a button
on my "master" sheet... could this be casing the problem?)
Private Sub CommandButton1_Click()
'This assumes the cells you wish to name the sheets after are in Column A
'starting on row5 on the "Inputs" worksheet.
'inX is a variable used to count down the rows.
Dim inX As Integer
Dim stName As String
inX = 5
Do Until Cells(inX, 1).Value = ""
stName = Cells(inX, 1).Value
Sheets.Add
ActiveSheet.Name = stName
Worksheets(stName).Activate
Cells(1, 1).Value = "Date"
Cells(1, 2).Value = "Time"
Cells(1, 3).Value = "Results"
Worksheets("Inputs").Activate
inX = inX + 1
Loop
End Sub
Anna
"JonR" wrote:
> it shouldn't have done that, but here's a fix
>
> put this line right after ActiveSheet.Name = stName
>
>
> Worksheets(stName).Activate
>
>
> --
> HTH
>
> JonR
>
>
> "anna" wrote:
>
> > It only changed the cells in the "Master" worksheet.
> > Sorry for trouble.
> > anna
> >
> > "JonR" wrote:
> >
> > > Insert these lines between the lines 'Activesheet.Name = stName' and
> > > 'Worksheets("Master").Activate'
> > >
> > > Cells(1,1).Value = "Date"
> > > Cells(1,2).Value = "Time"
> > > Cells(1,3).Value = "Results"
> > >
> > > --
> > > HTH
> > >
> > > JonR
> > >
> > >
> > > "anna" wrote:
> > >
> > > > Cool. Thanks!
> > > > I don't suppose there is something you could write in this VB macro that
> > > > will put the headers in the sheets also? Such as A1 ="Date", B1="Time",
> > > > C1="Results" when the new sheets are created?
> > > > anna
> > > >
> > > > "JonR" wrote:
> > > >
> > > > > At the top of your VB window, you'll see some buttons that look like controls
> > > > > for the media player, with an arrow (to run), a square (to stop) and one with
> > > > > two parallel bars to puasse (that one should be disabled if the code isn't
> > > > > running).. Just click the "play" button.
> > > > > --
> > > > > HTH
> > > > >
> > > > > JonR
> > > > >
> > > > >
> > > > > "anna" wrote:
> > > > >
> > > > > > I must be missing something or just a Visual Basic dummy... I copied the text
> > > > > > from "Sub NewSheet()" to "End Sub" and replaced the "inX = 1" to "inX = 5" in
> > > > > > a Visual Basic module in my workbook. Now what? Dumb question, but how do I
> > > > > > get it to work?
> > > > > > Thanks in advanced for not laughing at me,
> > > > > > anna
> > > > > >
> > > > > > "JonR" wrote:
> > > > > >
> > > > > > > Replace the line inX=1 with inX=5. Then when the code gets to the statement
> > > > > > > Do While Cells(inX,1)...., it will start at the cell on Row(inX) Column(1),
> > > > > > > which is A5.
> > > > > > >
> > > > > > > The way it's written, this code will run until it hits a blank in column A.
> > > > > > > If you need to skip a blank, or want to stop, start, or skip certian cells,
> > > > > > > you'll need to add some If statements.
> > > > > > >
> > > > > > > --
> > > > > > > HTH
> > > > > > >
> > > > > > > JonR
> > > > > > >
> > > > > > >
> > > > > > > "anna" wrote:
> > > > > > >
> > > > > > > > Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
> > > > > > > > the list starting in A5. Other users would input their data here so the list
> > > > > > > > could vary in length. I would like separate sheets generated with these names
> > > > > > > > so then the users could input results per "person" in their respective tabs.
> > > > > > > > This would include 3 columns... such as column one = "date" , column 2 =
> > > > > > > > "time", and column 3 = "result data". I think I figured out how to summarize
> > > > > > > > the data in a separate "summary" sheet by referring to the sheet name and
> > > > > > > > meeting multiple criteria (using this very helpful site), I just need this
> > > > > > > > last bit of info and I'm set! Of course if you can think of a better way to
> > > > > > > > do this, then please share. Thanks so much for you help!!
> > > > > > > > Anna
> > > > > > > >
> > > > > > > > "JonR" wrote:
> > > > > > > >
> > > > > > > > > The way this is written, it goes down Column A one cell at a time. Cell
> > > > > > > > > references in VBA are Cells(row,column), and you can see in the do While loop
> > > > > > > > > that the row is being incremented as each sheet is created.
> > > > > > > > >
> > > > > > > > > If you have certain criteria that you want to meet, you could do it with If
> > > > > > > > > statements or maybe a Select Case argument, depending on your needs. It
> > > > > > > > > might help if you could send an example of what you're trying to do so I can
> > > > > > > > > help meet your needs.
> > > > > > > > > --
> > > > > > > > > HTH
> > > > > > > > >
> > > > > > > > > JonR
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "anna" wrote:
> > > > > > > > >
> > > > > > > > > > How can this be modified to "look" at only a specific list of names to create
> > > > > > > > > > the sheets?
> > > > > > > > > >
> > > > > > > > > > Thanks,
> > > > > > > > > > anna
> > > > > > > > > >
> > > > > > > > > > "JonR" wrote:
> > > > > > > > > >
> > > > > > > > > > > Here's some code that will go down from A1 to A? (until it runs into an empty
> > > > > > > > > > > cell) on the "Master" sheet and will create and name sheet according to what
> > > > > > > > > > > is in each cell.
> > > > > > > > > > >
> > > > > > > > > > > HTH
> > > > > > > > > > >
> > > > > > > > > > > JonR
> > > > > > > > > > >
> > > > > > > > > > > Sub NewSheet()
> > > > > > > > > > >
> > > > > > > > > > > 'This assumes the cells you wish to name the sheets after are in Column A
> > > > > > > > > > > 'on the "Master" worksheet.
> > > > > > > > > > > 'inX is a variable used to count down the rows.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > Dim inX As Integer
> > > > > > > > > > >
> > > > > > > > > > > Dim stName As String
> > > > > > > > > > >
> > > > > > > > > > > inX = 1
> > > > > > > > > > >
> > > > > > > > > > > Do Until Cells(inX, 1).Value = ""
> > > > > > > > > > >
> > > > > > > > > > > stName = Cells(inX, 1).Value
> > > > > > > > > > >
> > > > > > > > > > > Sheets.Add
> > > > > > > > > > >
> > > > > > > > > > > ActiveSheet.Name = stName
> > > > > > > > > > >
> > > > > > > > > > > Worksheets("Master").Activate
> > > > > > > > > > >
> > > > > > > > > > > inX = inX + 1
> > > > > > > > > > >
> > > > > > > > > > > Loop
> > > > > > > > > > >
> > > > > > > > > > > End Sub
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > "Robert Maddox" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > Is it possible to make multple worksheets from a selection of multiple cells?
> > > > > > > > > > > > This would mean a selection of 10 cells would generate 10 sheets titled with
> > > > > > > > > > > > the cell conent.
It's possible. What I would do is put the macro into a separate module,
naming it something else like Sub New_Sheet()
then in the code for your command button, you'll just call the sub.
Private Sub CommandButton1_Click()
New_Sheet
End Sub
When you have a subroutine denoted as 'private' the only sheet or module
that can "see" it is the one that it is attached to, in this case your master
sheet. That's probably what's messing you up.
--
HTH
JonR
"anna" wrote:
> Same problem. here's the macro (believe it or not I combined it with a button
> on my "master" sheet... could this be casing the problem?)
>
> Private Sub CommandButton1_Click()
>
> 'This assumes the cells you wish to name the sheets after are in Column A
> 'starting on row5 on the "Inputs" worksheet.
> 'inX is a variable used to count down the rows.
>
>
> Dim inX As Integer
>
> Dim stName As String
>
> inX = 5
>
> Do Until Cells(inX, 1).Value = ""
>
> stName = Cells(inX, 1).Value
>
> Sheets.Add
>
> ActiveSheet.Name = stName
>
> Worksheets(stName).Activate
> Cells(1, 1).Value = "Date"
> Cells(1, 2).Value = "Time"
> Cells(1, 3).Value = "Results"
>
> Worksheets("Inputs").Activate
>
> inX = inX + 1
>
> Loop
>
> End Sub
>
> Anna
>
> "JonR" wrote:
>
> > it shouldn't have done that, but here's a fix
> >
> > put this line right after ActiveSheet.Name = stName
> >
> >
> > Worksheets(stName).Activate
> >
> >
> > --
> > HTH
> >
> > JonR
> >
> >
> > "anna" wrote:
> >
> > > It only changed the cells in the "Master" worksheet.
> > > Sorry for trouble.
> > > anna
> > >
> > > "JonR" wrote:
> > >
> > > > Insert these lines between the lines 'Activesheet.Name = stName' and
> > > > 'Worksheets("Master").Activate'
> > > >
> > > > Cells(1,1).Value = "Date"
> > > > Cells(1,2).Value = "Time"
> > > > Cells(1,3).Value = "Results"
> > > >
> > > > --
> > > > HTH
> > > >
> > > > JonR
> > > >
> > > >
> > > > "anna" wrote:
> > > >
> > > > > Cool. Thanks!
> > > > > I don't suppose there is something you could write in this VB macro that
> > > > > will put the headers in the sheets also? Such as A1 ="Date", B1="Time",
> > > > > C1="Results" when the new sheets are created?
> > > > > anna
> > > > >
> > > > > "JonR" wrote:
> > > > >
> > > > > > At the top of your VB window, you'll see some buttons that look like controls
> > > > > > for the media player, with an arrow (to run), a square (to stop) and one with
> > > > > > two parallel bars to puasse (that one should be disabled if the code isn't
> > > > > > running).. Just click the "play" button.
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > JonR
> > > > > >
> > > > > >
> > > > > > "anna" wrote:
> > > > > >
> > > > > > > I must be missing something or just a Visual Basic dummy... I copied the text
> > > > > > > from "Sub NewSheet()" to "End Sub" and replaced the "inX = 1" to "inX = 5" in
> > > > > > > a Visual Basic module in my workbook. Now what? Dumb question, but how do I
> > > > > > > get it to work?
> > > > > > > Thanks in advanced for not laughing at me,
> > > > > > > anna
> > > > > > >
> > > > > > > "JonR" wrote:
> > > > > > >
> > > > > > > > Replace the line inX=1 with inX=5. Then when the code gets to the statement
> > > > > > > > Do While Cells(inX,1)...., it will start at the cell on Row(inX) Column(1),
> > > > > > > > which is A5.
> > > > > > > >
> > > > > > > > The way it's written, this code will run until it hits a blank in column A.
> > > > > > > > If you need to skip a blank, or want to stop, start, or skip certian cells,
> > > > > > > > you'll need to add some If statements.
> > > > > > > >
> > > > > > > > --
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > JonR
> > > > > > > >
> > > > > > > >
> > > > > > > > "anna" wrote:
> > > > > > > >
> > > > > > > > > Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
> > > > > > > > > the list starting in A5. Other users would input their data here so the list
> > > > > > > > > could vary in length. I would like separate sheets generated with these names
> > > > > > > > > so then the users could input results per "person" in their respective tabs.
> > > > > > > > > This would include 3 columns... such as column one = "date" , column 2 =
> > > > > > > > > "time", and column 3 = "result data". I think I figured out how to summarize
> > > > > > > > > the data in a separate "summary" sheet by referring to the sheet name and
> > > > > > > > > meeting multiple criteria (using this very helpful site), I just need this
> > > > > > > > > last bit of info and I'm set! Of course if you can think of a better way to
> > > > > > > > > do this, then please share. Thanks so much for you help!!
> > > > > > > > > Anna
> > > > > > > > >
> > > > > > > > > "JonR" wrote:
> > > > > > > > >
> > > > > > > > > > The way this is written, it goes down Column A one cell at a time. Cell
> > > > > > > > > > references in VBA are Cells(row,column), and you can see in the do While loop
> > > > > > > > > > that the row is being incremented as each sheet is created.
> > > > > > > > > >
> > > > > > > > > > If you have certain criteria that you want to meet, you could do it with If
> > > > > > > > > > statements or maybe a Select Case argument, depending on your needs. It
> > > > > > > > > > might help if you could send an example of what you're trying to do so I can
> > > > > > > > > > help meet your needs.
> > > > > > > > > > --
> > > > > > > > > > HTH
> > > > > > > > > >
> > > > > > > > > > JonR
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "anna" wrote:
> > > > > > > > > >
> > > > > > > > > > > How can this be modified to "look" at only a specific list of names to create
> > > > > > > > > > > the sheets?
> > > > > > > > > > >
> > > > > > > > > > > Thanks,
> > > > > > > > > > > anna
> > > > > > > > > > >
> > > > > > > > > > > "JonR" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > Here's some code that will go down from A1 to A? (until it runs into an empty
> > > > > > > > > > > > cell) on the "Master" sheet and will create and name sheet according to what
> > > > > > > > > > > > is in each cell.
> > > > > > > > > > > >
> > > > > > > > > > > > HTH
> > > > > > > > > > > >
> > > > > > > > > > > > JonR
> > > > > > > > > > > >
> > > > > > > > > > > > Sub NewSheet()
> > > > > > > > > > > >
> > > > > > > > > > > > 'This assumes the cells you wish to name the sheets after are in Column A
> > > > > > > > > > > > 'on the "Master" worksheet.
> > > > > > > > > > > > 'inX is a variable used to count down the rows.
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > Dim inX As Integer
> > > > > > > > > > > >
> > > > > > > > > > > > Dim stName As String
> > > > > > > > > > > >
> > > > > > > > > > > > inX = 1
> > > > > > > > > > > >
> > > > > > > > > > > > Do Until Cells(inX, 1).Value = ""
> > > > > > > > > > > >
> > > > > > > > > > > > stName = Cells(inX, 1).Value
> > > > > > > > > > > >
> > > > > > > > > > > > Sheets.Add
> > > > > > > > > > > >
> > > > > > > > > > > > ActiveSheet.Name = stName
> > > > > > > > > > > >
> > > > > > > > > > > > Worksheets("Master").Activate
> > > > > > > > > > > >
> > > > > > > > > > > > inX = inX + 1
> > > > > > > > > > > >
> > > > > > > > > > > > Loop
> > > > > > > > > > > >
> > > > > > > > > > > > End Sub
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > "Robert Maddox" wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > > Is it possible to make multple worksheets from a selection of multiple cells?
> > > > > > > > > > > > > This would mean a selection of 10 cells would generate 10 sheets titled with
> > > > > > > > > > > > > the cell conent.
Thanks! That works great!!!
Anna
"JonR" wrote:
> It's possible. What I would do is put the macro into a separate module,
> naming it something else like Sub New_Sheet()
>
> then in the code for your command button, you'll just call the sub.
>
> Private Sub CommandButton1_Click()
>
> New_Sheet
>
> End Sub
>
> When you have a subroutine denoted as 'private' the only sheet or module
> that can "see" it is the one that it is attached to, in this case your master
> sheet. That's probably what's messing you up.
> --
> HTH
>
> JonR
>
>
> "anna" wrote:
>
> > Same problem. here's the macro (believe it or not I combined it with a button
> > on my "master" sheet... could this be casing the problem?)
> >
> > Private Sub CommandButton1_Click()
> >
> > 'This assumes the cells you wish to name the sheets after are in Column A
> > 'starting on row5 on the "Inputs" worksheet.
> > 'inX is a variable used to count down the rows.
> >
> >
> > Dim inX As Integer
> >
> > Dim stName As String
> >
> > inX = 5
> >
> > Do Until Cells(inX, 1).Value = ""
> >
> > stName = Cells(inX, 1).Value
> >
> > Sheets.Add
> >
> > ActiveSheet.Name = stName
> >
> > Worksheets(stName).Activate
> > Cells(1, 1).Value = "Date"
> > Cells(1, 2).Value = "Time"
> > Cells(1, 3).Value = "Results"
> >
> > Worksheets("Inputs").Activate
> >
> > inX = inX + 1
> >
> > Loop
> >
> > End Sub
> >
> > Anna
> >
> > "JonR" wrote:
> >
> > > it shouldn't have done that, but here's a fix
> > >
> > > put this line right after ActiveSheet.Name = stName
> > >
> > >
> > > Worksheets(stName).Activate
> > >
> > >
> > > --
> > > HTH
> > >
> > > JonR
> > >
> > >
> > > "anna" wrote:
> > >
> > > > It only changed the cells in the "Master" worksheet.
> > > > Sorry for trouble.
> > > > anna
> > > >
> > > > "JonR" wrote:
> > > >
> > > > > Insert these lines between the lines 'Activesheet.Name = stName' and
> > > > > 'Worksheets("Master").Activate'
> > > > >
> > > > > Cells(1,1).Value = "Date"
> > > > > Cells(1,2).Value = "Time"
> > > > > Cells(1,3).Value = "Results"
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > JonR
> > > > >
> > > > >
> > > > > "anna" wrote:
> > > > >
> > > > > > Cool. Thanks!
> > > > > > I don't suppose there is something you could write in this VB macro that
> > > > > > will put the headers in the sheets also? Such as A1 ="Date", B1="Time",
> > > > > > C1="Results" when the new sheets are created?
> > > > > > anna
> > > > > >
> > > > > > "JonR" wrote:
> > > > > >
> > > > > > > At the top of your VB window, you'll see some buttons that look like controls
> > > > > > > for the media player, with an arrow (to run), a square (to stop) and one with
> > > > > > > two parallel bars to puasse (that one should be disabled if the code isn't
> > > > > > > running).. Just click the "play" button.
> > > > > > > --
> > > > > > > HTH
> > > > > > >
> > > > > > > JonR
> > > > > > >
> > > > > > >
> > > > > > > "anna" wrote:
> > > > > > >
> > > > > > > > I must be missing something or just a Visual Basic dummy... I copied the text
> > > > > > > > from "Sub NewSheet()" to "End Sub" and replaced the "inX = 1" to "inX = 5" in
> > > > > > > > a Visual Basic module in my workbook. Now what? Dumb question, but how do I
> > > > > > > > get it to work?
> > > > > > > > Thanks in advanced for not laughing at me,
> > > > > > > > anna
> > > > > > > >
> > > > > > > > "JonR" wrote:
> > > > > > > >
> > > > > > > > > Replace the line inX=1 with inX=5. Then when the code gets to the statement
> > > > > > > > > Do While Cells(inX,1)...., it will start at the cell on Row(inX) Column(1),
> > > > > > > > > which is A5.
> > > > > > > > >
> > > > > > > > > The way it's written, this code will run until it hits a blank in column A.
> > > > > > > > > If you need to skip a blank, or want to stop, start, or skip certian cells,
> > > > > > > > > you'll need to add some If statements.
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > HTH
> > > > > > > > >
> > > > > > > > > JonR
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "anna" wrote:
> > > > > > > > >
> > > > > > > > > > Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
> > > > > > > > > > the list starting in A5. Other users would input their data here so the list
> > > > > > > > > > could vary in length. I would like separate sheets generated with these names
> > > > > > > > > > so then the users could input results per "person" in their respective tabs.
> > > > > > > > > > This would include 3 columns... such as column one = "date" , column 2 =
> > > > > > > > > > "time", and column 3 = "result data". I think I figured out how to summarize
> > > > > > > > > > the data in a separate "summary" sheet by referring to the sheet name and
> > > > > > > > > > meeting multiple criteria (using this very helpful site), I just need this
> > > > > > > > > > last bit of info and I'm set! Of course if you can think of a better way to
> > > > > > > > > > do this, then please share. Thanks so much for you help!!
> > > > > > > > > > Anna
> > > > > > > > > >
> > > > > > > > > > "JonR" wrote:
> > > > > > > > > >
> > > > > > > > > > > The way this is written, it goes down Column A one cell at a time. Cell
> > > > > > > > > > > references in VBA are Cells(row,column), and you can see in the do While loop
> > > > > > > > > > > that the row is being incremented as each sheet is created.
> > > > > > > > > > >
> > > > > > > > > > > If you have certain criteria that you want to meet, you could do it with If
> > > > > > > > > > > statements or maybe a Select Case argument, depending on your needs. It
> > > > > > > > > > > might help if you could send an example of what you're trying to do so I can
> > > > > > > > > > > help meet your needs.
> > > > > > > > > > > --
> > > > > > > > > > > HTH
> > > > > > > > > > >
> > > > > > > > > > > JonR
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > "anna" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > How can this be modified to "look" at only a specific list of names to create
> > > > > > > > > > > > the sheets?
> > > > > > > > > > > >
> > > > > > > > > > > > Thanks,
> > > > > > > > > > > > anna
> > > > > > > > > > > >
> > > > > > > > > > > > "JonR" wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > > Here's some code that will go down from A1 to A? (until it runs into an empty
> > > > > > > > > > > > > cell) on the "Master" sheet and will create and name sheet according to what
> > > > > > > > > > > > > is in each cell.
> > > > > > > > > > > > >
> > > > > > > > > > > > > HTH
> > > > > > > > > > > > >
> > > > > > > > > > > > > JonR
> > > > > > > > > > > > >
> > > > > > > > > > > > > Sub NewSheet()
> > > > > > > > > > > > >
> > > > > > > > > > > > > 'This assumes the cells you wish to name the sheets after are in Column A
> > > > > > > > > > > > > 'on the "Master" worksheet.
> > > > > > > > > > > > > 'inX is a variable used to count down the rows.
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > Dim inX As Integer
> > > > > > > > > > > > >
> > > > > > > > > > > > > Dim stName As String
> > > > > > > > > > > > >
> > > > > > > > > > > > > inX = 1
> > > > > > > > > > > > >
> > > > > > > > > > > > > Do Until Cells(inX, 1).Value = ""
> > > > > > > > > > > > >
> > > > > > > > > > > > > stName = Cells(inX, 1).Value
> > > > > > > > > > > > >
> > > > > > > > > > > > > Sheets.Add
> > > > > > > > > > > > >
> > > > > > > > > > > > > ActiveSheet.Name = stName
> > > > > > > > > > > > >
> > > > > > > > > > > > > Worksheets("Master").Activate
> > > > > > > > > > > > >
> > > > > > > > > > > > > inX = inX + 1
> > > > > > > > > > > > >
> > > > > > > > > > > > > Loop
> > > > > > > > > > > > >
> > > > > > > > > > > > > End Sub
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > "Robert Maddox" wrote:
> > > > > > > > > > > > >
> > > > > > > > > > > > > > Is it possible to make multple worksheets from a selection of multiple cells?
> > > > > > > > > > > > > > This would mean a selection of 10 cells would generate 10 sheets titled with
> > > > > > > > > > > > > > the cell conent.
Glad to help
--
HTH
JonR
"anna" wrote:
> Thanks! That works great!!!
> Anna
>
> "JonR" wrote:
>
> > It's possible. What I would do is put the macro into a separate module,
> > naming it something else like Sub New_Sheet()
> >
> > then in the code for your command button, you'll just call the sub.
> >
> > Private Sub CommandButton1_Click()
> >
> > New_Sheet
> >
> > End Sub
> >
> > When you have a subroutine denoted as 'private' the only sheet or module
> > that can "see" it is the one that it is attached to, in this case your master
> > sheet. That's probably what's messing you up.
> > --
> > HTH
> >
> > JonR
> >
> >
> > "anna" wrote:
> >
> > > Same problem. here's the macro (believe it or not I combined it with a button
> > > on my "master" sheet... could this be casing the problem?)
> > >
> > > Private Sub CommandButton1_Click()
> > >
> > > 'This assumes the cells you wish to name the sheets after are in Column A
> > > 'starting on row5 on the "Inputs" worksheet.
> > > 'inX is a variable used to count down the rows.
> > >
> > >
> > > Dim inX As Integer
> > >
> > > Dim stName As String
> > >
> > > inX = 5
> > >
> > > Do Until Cells(inX, 1).Value = ""
> > >
> > > stName = Cells(inX, 1).Value
> > >
> > > Sheets.Add
> > >
> > > ActiveSheet.Name = stName
> > >
> > > Worksheets(stName).Activate
> > > Cells(1, 1).Value = "Date"
> > > Cells(1, 2).Value = "Time"
> > > Cells(1, 3).Value = "Results"
> > >
> > > Worksheets("Inputs").Activate
> > >
> > > inX = inX + 1
> > >
> > > Loop
> > >
> > > End Sub
> > >
> > > Anna
> > >
> > > "JonR" wrote:
> > >
> > > > it shouldn't have done that, but here's a fix
> > > >
> > > > put this line right after ActiveSheet.Name = stName
> > > >
> > > >
> > > > Worksheets(stName).Activate
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > JonR
> > > >
> > > >
> > > > "anna" wrote:
> > > >
> > > > > It only changed the cells in the "Master" worksheet.
> > > > > Sorry for trouble.
> > > > > anna
> > > > >
> > > > > "JonR" wrote:
> > > > >
> > > > > > Insert these lines between the lines 'Activesheet.Name = stName' and
> > > > > > 'Worksheets("Master").Activate'
> > > > > >
> > > > > > Cells(1,1).Value = "Date"
> > > > > > Cells(1,2).Value = "Time"
> > > > > > Cells(1,3).Value = "Results"
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > JonR
> > > > > >
> > > > > >
> > > > > > "anna" wrote:
> > > > > >
> > > > > > > Cool. Thanks!
> > > > > > > I don't suppose there is something you could write in this VB macro that
> > > > > > > will put the headers in the sheets also? Such as A1 ="Date", B1="Time",
> > > > > > > C1="Results" when the new sheets are created?
> > > > > > > anna
> > > > > > >
> > > > > > > "JonR" wrote:
> > > > > > >
> > > > > > > > At the top of your VB window, you'll see some buttons that look like controls
> > > > > > > > for the media player, with an arrow (to run), a square (to stop) and one with
> > > > > > > > two parallel bars to puasse (that one should be disabled if the code isn't
> > > > > > > > running).. Just click the "play" button.
> > > > > > > > --
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > JonR
> > > > > > > >
> > > > > > > >
> > > > > > > > "anna" wrote:
> > > > > > > >
> > > > > > > > > I must be missing something or just a Visual Basic dummy... I copied the text
> > > > > > > > > from "Sub NewSheet()" to "End Sub" and replaced the "inX = 1" to "inX = 5" in
> > > > > > > > > a Visual Basic module in my workbook. Now what? Dumb question, but how do I
> > > > > > > > > get it to work?
> > > > > > > > > Thanks in advanced for not laughing at me,
> > > > > > > > > anna
> > > > > > > > >
> > > > > > > > > "JonR" wrote:
> > > > > > > > >
> > > > > > > > > > Replace the line inX=1 with inX=5. Then when the code gets to the statement
> > > > > > > > > > Do While Cells(inX,1)...., it will start at the cell on Row(inX) Column(1),
> > > > > > > > > > which is A5.
> > > > > > > > > >
> > > > > > > > > > The way it's written, this code will run until it hits a blank in column A.
> > > > > > > > > > If you need to skip a blank, or want to stop, start, or skip certian cells,
> > > > > > > > > > you'll need to add some If statements.
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > HTH
> > > > > > > > > >
> > > > > > > > > > JonR
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "anna" wrote:
> > > > > > > > > >
> > > > > > > > > > > Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
> > > > > > > > > > > the list starting in A5. Other users would input their data here so the list
> > > > > > > > > > > could vary in length. I would like separate sheets generated with these names
> > > > > > > > > > > so then the users could input results per "person" in their respective tabs.
> > > > > > > > > > > This would include 3 columns... such as column one = "date" , column 2 =
> > > > > > > > > > > "time", and column 3 = "result data". I think I figured out how to summarize
> > > > > > > > > > > the data in a separate "summary" sheet by referring to the sheet name and
> > > > > > > > > > > meeting multiple criteria (using this very helpful site), I just need this
> > > > > > > > > > > last bit of info and I'm set! Of course if you can think of a better way to
> > > > > > > > > > > do this, then please share. Thanks so much for you help!!
> > > > > > > > > > > Anna
> > > > > > > > > > >
> > > > > > > > > > > "JonR" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > The way this is written, it goes down Column A one cell at a time. Cell
> > > > > > > > > > > > references in VBA are Cells(row,column), and you can see in the do While loop
> > > > > > > > > > > > that the row is being incremented as each sheet is created.
> > > > > > > > > > > >
> > > > > > > > > > > > If you have certain criteria that you want to meet, you could do it with If
> > > > > > > > > > > > statements or maybe a Select Case argument, depending on your needs. It
> > > > > > > > > > > > might help if you could send an example of what you're trying to do so I can
> > > > > > > > > > > > help meet your needs.
> > > > > > > > > > > > --
> > > > > > > > > > > > HTH
> > > > > > > > > > > >
> > > > > > > > > > > > JonR
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > "anna" wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > > How can this be modified to "look" at only a specific list of names to create
> > > > > > > > > > > > > the sheets?
> > > > > > > > > > > > >
> > > > > > > > > > > > > Thanks,
> > > > > > > > > > > > > anna
> > > > > > > > > > > > >
> > > > > > > > > > > > > "JonR" wrote:
> > > > > > > > > > > > >
> > > > > > > > > > > > > > Here's some code that will go down from A1 to A? (until it runs into an empty
> > > > > > > > > > > > > > cell) on the "Master" sheet and will create and name sheet according to what
> > > > > > > > > > > > > > is in each cell.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > HTH
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > JonR
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Sub NewSheet()
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > 'This assumes the cells you wish to name the sheets after are in Column A
> > > > > > > > > > > > > > 'on the "Master" worksheet.
> > > > > > > > > > > > > > 'inX is a variable used to count down the rows.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Dim inX As Integer
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Dim stName As String
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > inX = 1
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Do Until Cells(inX, 1).Value = ""
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > stName = Cells(inX, 1).Value
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Sheets.Add
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > ActiveSheet.Name = stName
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Worksheets("Master").Activate
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > inX = inX + 1
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Loop
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > End Sub
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > "Robert Maddox" wrote:
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Is it possible to make multple worksheets from a selection of multiple cells?
> > > > > > > > > > > > > > > This would mean a selection of 10 cells would generate 10 sheets titled with
> > > > > > > > > > > > > > > the cell conent.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks