Hi,

I have a workbook with 3 sheets in it. middle sheet has table of values,
which i want to export as a new workbook having only 1 sheet(that sheet) &
this new workbook must be named as old workbook & sheet should have just the
table formats & values, no Formulas.

Also, wanted to add, if there is a way to export new worksheet as new
workbook, with old name & delete the old workbook simultaneously, to save
space in hard-disk.

Any help would be most appreciated!

Rgds,

Eijaz

Hi,

Once Again, I'll Explain:

From,
OldWorkbook (named Eijaz) - 3 sheets
Create,
New Workbook (named Eijaz&Now()) - 2nd sheet of OldWorkbook(named as say
"Sept", found in Cell "A1" on sheet)
Once Created,
Delete OldWorkbook (optional)

Rgds,

Eijaz



"gr8guy" wrote in message
...
Hi,

I have a workbook with 3 sheets in it. middle sheet has table of values,
which i want to export as a new workbook having only 1 sheet(that sheet) &
this new workbook must be named as old workbook & sheet should have just
the
table formats & values, no Formulas.

Also, wanted to add, if there is a way to export new worksheet as new
workbook, with old name & delete the old workbook simultaneously, to save
space in hard-disk.

Any help would be most appreciated!

Rgds,

Eijaz

Hi Dave,

First Because, I have a lot of workbooks, & second because I will be
attaching this code to a button on sheet3, Old workbook, for every old
workbooks.

Please note that the Delete idea is optional. Just Wanted to know if it can
be done!

Rgds,

Eijaz


"Dave Peterson" wrote in message
...
Why not just delete the other sheets
select all the cells
edit|copy
edit|paste special|values
and save it as the original name.



gr8guy wrote:

Hi,

Once Again, I'll Explain:

From,
OldWorkbook (named Eijaz) - 3 sheets
Create,
New Workbook (named Eijaz&Now()) - 2nd sheet of OldWorkbook(named as say
"Sept", found in Cell "A1" on sheet)
Once Created,
Delete OldWorkbook (optional)

Rgds,

Eijaz

"gr8guy" wrote in message
...
Hi,

I have a workbook with 3 sheets in it. middle sheet has table of
values,
which i want to export as a new workbook having only 1 sheet(that
sheet) &
this new workbook must be named as old workbook & sheet should have
just
the
table formats & values, no Formulas.

Also, wanted to add, if there is a way to export new worksheet as new
workbook, with old name & delete the old workbook simultaneously, to
save
space in hard-disk.

Any help would be most appreciated!

Rgds,

Eijaz




--

Dave Peterson

Thanks Dave,


Rgds,

Eijaz


"Dave Peterson" wrote in message
...
Yeah, I think it's possible.

But I would be hesitant to do this. By destroying the old workbook, don't
you
lose the abililty to go back and just double check that things worked.
(I'm
always afraid that something will go wrong and the data will be lost
forever.)

But if you really want, this seemed to work ok for me:

Option Explicit
Sub testme()

Dim keepWks As Worksheet
Dim NameToUse As String

Set keepWks = ThisWorkbook.Worksheets("sheet2")
NameToUse = ThisWorkbook.FullName

keepWks.Copy 'to a new workbook
Set keepWks = ActiveSheet 'in the new workbook

With keepWks
.UsedRange.Value = .UsedRange.Value
.Name = .Range("a1").Value
End With

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=Environ("temp") & "\deletemelater.xls"
Application.DisplayAlerts = True

Application.DisplayAlerts = False
keepWks.Parent.SaveAs Filename:=NameToUse
Application.DisplayAlerts = True

ThisWorkbook.Saved = True
ThisWorkbook.ChangeFileAccess xlReadOnly
Kill ThisWorkbook.FullName

ThisWorkbook.Close savechanges:=False

End Sub

I didn't do any validation. I expect that the value in A1 represents a
valid
worksheet name.

If I were testing this, I'd comment out that Kill statement. If things
didn't
work ok, then go to your windows temp folder and move deletemelater.xls to
a
nice spot to save the data.



gr8guy wrote:

Hi Dave,

First Because, I have a lot of workbooks, & second because I will be
attaching this code to a button on sheet3, Old workbook, for every old
workbooks.

Please note that the Delete idea is optional. Just Wanted to know if it
can
be done!

Rgds,

Eijaz

"Dave Peterson" wrote in message
...
Why not just delete the other sheets
select all the cells
edit|copy
edit|paste special|values
and save it as the original name.



gr8guy wrote:

Hi,

Once Again, I'll Explain:

From,
OldWorkbook (named Eijaz) - 3 sheets
Create,
New Workbook (named Eijaz&Now()) - 2nd sheet of OldWorkbook(named as
say
"Sept", found in Cell "A1" on sheet)
Once Created,
Delete OldWorkbook (optional)

Rgds,

Eijaz

"gr8guy" wrote in message
...
Hi,

I have a workbook with 3 sheets in it. middle sheet has table of
values,
which i want to export as a new workbook having only 1 sheet(that
sheet) &
this new workbook must be named as old workbook & sheet should
have
just
the
table formats & values, no Formulas.

Also, wanted to add, if there is a way to export new worksheet as
new
workbook, with old name & delete the old workbook simultaneously,
to
save
space in hard-disk.

Any help would be most appreciated!

Rgds,

Eijaz




--

Dave Peterson


--

Dave Peterson

Why not just delete the other sheets
select all the cells
edit|copy
edit|paste special|values
and save it as the original name.



gr8guy wrote:

Hi,

Once Again, I'll Explain:

From,
OldWorkbook (named Eijaz) - 3 sheets
Create,
New Workbook (named Eijaz&Now()) - 2nd sheet of OldWorkbook(named as say
"Sept", found in Cell "A1" on sheet)
Once Created,
Delete OldWorkbook (optional)

Rgds,

Eijaz

"gr8guy" wrote in message
...
Hi,

I have a workbook with 3 sheets in it. middle sheet has table of values,
which i want to export as a new workbook having only 1 sheet(that sheet) &
this new workbook must be named as old workbook & sheet should have just
the
table formats & values, no Formulas.

Also, wanted to add, if there is a way to export new worksheet as new
workbook, with old name & delete the old workbook simultaneously, to save
space in hard-disk.

Any help would be most appreciated!

Rgds,

Eijaz




--

Dave Peterson

Yeah, I think it's possible.

But I would be hesitant to do this. By destroying the old workbook, don't you
lose the abililty to go back and just double check that things worked. (I'm
always afraid that something will go wrong and the data will be lost forever.)

But if you really want, this seemed to work ok for me:

Option Explicit
Sub testme()

Dim keepWks As Worksheet
Dim NameToUse As String

Set keepWks = ThisWorkbook.Worksheets("sheet2")
NameToUse = ThisWorkbook.FullName

keepWks.Copy 'to a new workbook
Set keepWks = ActiveSheet 'in the new workbook

With keepWks
.UsedRange.Value = .UsedRange.Value
.Name = .Range("a1").Value
End With

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=Environ("temp") & "\deletemelater.xls"
Application.DisplayAlerts = True

Application.DisplayAlerts = False
keepWks.Parent.SaveAs Filename:=NameToUse
Application.DisplayAlerts = True

ThisWorkbook.Saved = True
ThisWorkbook.ChangeFileAccess xlReadOnly
Kill ThisWorkbook.FullName

ThisWorkbook.Close savechanges:=False

End Sub

I didn't do any validation. I expect that the value in A1 represents a valid
worksheet name.

If I were testing this, I'd comment out that Kill statement. If things didn't
work ok, then go to your windows temp folder and move deletemelater.xls to a
nice spot to save the data.



gr8guy wrote:

Hi Dave,

First Because, I have a lot of workbooks, & second because I will be
attaching this code to a button on sheet3, Old workbook, for every old
workbooks.

Please note that the Delete idea is optional. Just Wanted to know if it can
be done!

Rgds,

Eijaz

"Dave Peterson" wrote in message
...
Why not just delete the other sheets
select all the cells
edit|copy
edit|paste special|values
and save it as the original name.



gr8guy wrote:

Hi,

Once Again, I'll Explain:

From,
OldWorkbook (named Eijaz) - 3 sheets
Create,
New Workbook (named Eijaz&Now()) - 2nd sheet of OldWorkbook(named as say
"Sept", found in Cell "A1" on sheet)
Once Created,
Delete OldWorkbook (optional)

Rgds,

Eijaz

"gr8guy" wrote in message
...
Hi,

I have a workbook with 3 sheets in it. middle sheet has table of
values,
which i want to export as a new workbook having only 1 sheet(that
sheet) &
this new workbook must be named as old workbook & sheet should have
just
the
table formats & values, no Formulas.

Also, wanted to add, if there is a way to export new worksheet as new
workbook, with old name & delete the old workbook simultaneously, to
save
space in hard-disk.

Any help would be most appreciated!

Rgds,

Eijaz




--

Dave Peterson


--

Dave Peterson