can we copy our "sheetnames" too?

Hi,
The below code copies the content of our workbook sheets seperately as new
workbooks, but saves the sheets with default "Sheet1", "Sheet2" name format.
Can we also copy the used sheet names while copying?
TIA



0
ayes (38)
5/20/2004 3:14:08 PM
excel 39879 articles. 2 followers. Follow

5 Replies
403 Views

Similar Articles

[PageSpeed] 55

Hi
no code attached :-)

-- 
Regards
Frank Kabel
Frankfurt, Germany


Martyn wrote:
> Hi,
> The below code copies the content of our workbook sheets seperately
> as new workbooks, but saves the sheets with default "Sheet1",
> "Sheet2" name format. Can we also copy the used sheet names while
> copying? 
> TIA
0
frank.kabel (11126)
5/20/2004 3:17:16 PM
Hi,
This is one of the mistakes not "many" people can do....8-)))
I was so tired list night that I forgot to paste it completely. Sorry Frank.
Here goes
=============================
Private Sub Workbook_Open()
' Can we add Application.DisplayAlerts = False here?
Set shOrig = ActiveSheet
For Each sh In ActiveWorkbook.Worksheets
        sh.Copy
        ActiveWorkbook.SaveAs Filename:="C:\" & sh.Name & ".xls"
        ActiveWorkbook.Close
    Next sh
shOrig.Activate
End Sub
=============================
Martyn

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:#Om#01nPEHA.2876@TK2MSFTNGP09.phx.gbl...
> Hi
> no code attached :-)
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> Martyn wrote:
> > Hi,
> > The below code copies the content of our workbook sheets seperately
> > as new workbooks, but saves the sheets with default "Sheet1",
> > "Sheet2" name format. Can we also copy the used sheet names while
> > copying?
> > TIA


0
ayes (38)
5/20/2004 7:04:38 PM
You sure you didn't test against a workbook that had those vanilla names.

Your code worked ok for me.

Martyn wrote:
> 
> Hi,
> This is one of the mistakes not "many" people can do....8-)))
> I was so tired list night that I forgot to paste it completely. Sorry Frank.
> Here goes
> =============================
> Private Sub Workbook_Open()
> ' Can we add Application.DisplayAlerts = False here?
> Set shOrig = ActiveSheet
> For Each sh In ActiveWorkbook.Worksheets
>         sh.Copy
>         ActiveWorkbook.SaveAs Filename:="C:\" & sh.Name & ".xls"
>         ActiveWorkbook.Close
>     Next sh
> shOrig.Activate
> End Sub
> =============================
> Martyn
> 
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:#Om#01nPEHA.2876@TK2MSFTNGP09.phx.gbl...
> > Hi
> > no code attached :-)
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> >
> > Martyn wrote:
> > > Hi,
> > > The below code copies the content of our workbook sheets seperately
> > > as new workbooks, but saves the sheets with default "Sheet1",
> > > "Sheet2" name format. Can we also copy the used sheet names while
> > > copying?
> > > TIA

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/20/2004 10:29:09 PM
Sorry Dave, I guess you are right. I think there was a problem with the test
file I was working on.
Now I've used the macro on another file and it works OK!.
Martyn


"Dave Peterson" <ec35720@msn.com> wrote in message
news:40AD3135.B137FAE1@msn.com...
> You sure you didn't test against a workbook that had those vanilla names.
>
> Your code worked ok for me.
>
> Martyn wrote:
> >
> > Hi,
> > This is one of the mistakes not "many" people can do....8-)))
> > I was so tired list night that I forgot to paste it completely. Sorry
Frank.
> > Here goes
> > =============================
> > Private Sub Workbook_Open()
> > ' Can we add Application.DisplayAlerts = False here?
> > Set shOrig = ActiveSheet
> > For Each sh In ActiveWorkbook.Worksheets
> >         sh.Copy
> >         ActiveWorkbook.SaveAs Filename:="C:\" & sh.Name & ".xls"
> >         ActiveWorkbook.Close
> >     Next sh
> > shOrig.Activate
> > End Sub
> > =============================
> > Martyn
> >
> > "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> > news:#Om#01nPEHA.2876@TK2MSFTNGP09.phx.gbl...
> > > Hi
> > > no code attached :-)
> > >
> > > --
> > > Regards
> > > Frank Kabel
> > > Frankfurt, Germany
> > >
> > >
> > > Martyn wrote:
> > > > Hi,
> > > > The below code copies the content of our workbook sheets seperately
> > > > as new workbooks, but saves the sheets with default "Sheet1",
> > > > "Sheet2" name format. Can we also copy the used sheet names while
> > > > copying?
> > > > TIA
>
> -- 
>
> Dave Peterson
> ec35720@msn.com


0
ayes (38)
5/21/2004 9:43:32 AM
Glad you got it sorted out.


Martyn wrote:
> 
> Sorry Dave, I guess you are right. I think there was a problem with the test
> file I was working on.
> Now I've used the macro on another file and it works OK!.
> Martyn
> 
> "Dave Peterson" <ec35720@msn.com> wrote in message
> news:40AD3135.B137FAE1@msn.com...
> > You sure you didn't test against a workbook that had those vanilla names.
> >
> > Your code worked ok for me.
> >
> > Martyn wrote:
> > >
> > > Hi,
> > > This is one of the mistakes not "many" people can do....8-)))
> > > I was so tired list night that I forgot to paste it completely. Sorry
> Frank.
> > > Here goes
> > > =============================
> > > Private Sub Workbook_Open()
> > > ' Can we add Application.DisplayAlerts = False here?
> > > Set shOrig = ActiveSheet
> > > For Each sh In ActiveWorkbook.Worksheets
> > >         sh.Copy
> > >         ActiveWorkbook.SaveAs Filename:="C:\" & sh.Name & ".xls"
> > >         ActiveWorkbook.Close
> > >     Next sh
> > > shOrig.Activate
> > > End Sub
> > > =============================
> > > Martyn
> > >
> > > "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> > > news:#Om#01nPEHA.2876@TK2MSFTNGP09.phx.gbl...
> > > > Hi
> > > > no code attached :-)
> > > >
> > > > --
> > > > Regards
> > > > Frank Kabel
> > > > Frankfurt, Germany
> > > >
> > > >
> > > > Martyn wrote:
> > > > > Hi,
> > > > > The below code copies the content of our workbook sheets seperately
> > > > > as new workbooks, but saves the sheets with default "Sheet1",
> > > > > "Sheet2" name format. Can we also copy the used sheet names while
> > > > > copying?
> > > > > TIA
> >
> > --
> >
> > Dave Peterson
> > ec35720@msn.com

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/21/2004 10:14:11 PM
Reply:

Similar Artilces:

can we copy our "sheetnames" too?
Hi, The below code copies the content of our workbook sheets seperately as new workbooks, but saves the sheets with default "Sheet1", "Sheet2" name format. Can we also copy the used sheet names while copying? TIA Hi no code attached :-) -- Regards Frank Kabel Frankfurt, Germany Martyn wrote: > Hi, > The below code copies the content of our workbook sheets seperately > as new workbooks, but saves the sheets with default "Sheet1", > "Sheet2" name format. Can we also copy the used sheet names while > copying? > TIA Hi, This is on...

Sheetnames in formula
I have 3 sheets (named 2001, 2002, 2003 representing different years) and a sheet named variance. All have identical row/column headings. In the sheet variance, I'd like to supply names of two sheets and calculate the variance between them. I could create a different sheet for each variance (2003-2002, 2002-2001, 2003-2001) but I don't want to do that. Any ideas? Hi Frank With 2002 in A1 and 2003 in A2, this formula sums their cell C7: =INDIRECT(A1&"!C7")+INDIRECT(A2&"!C7") HTH. Best wishes Harald "Frank" <anonymous@discussions.micr...

Sheetnames in Excel
Hello: When I get the sheetnames from Excel, I get two refrences to a sheet. For example, I will get Sheet1$ Sheet1$_ What is the second one. Is it refrencing the same table? Thanks Prasun Are you referring to ADOX? -- HTH RP (remove nothere from the email address if mailing direct) "Prasun" <prasunp_@csufresno.edu> wrote in message news:egE1t4yWFHA.3828@TK2MSFTNGP10.phx.gbl... > Hello: > > When I get the sheetnames from Excel, I get two refrences to a sheet. > > For example, I will get > > Sheet1$ > Sheet1$_ > > What is the second ...