Export selected Sheet in a new workbook with old workbook name!

[gr8guy]

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

 

[gr8guy]

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]

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

 

[gr8guy]

Thanks Dave,


Rgds,

Eijaz

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.

 

[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.

 

[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.