Another Quick Question. Thanks, guys!

D

Damil4real

This workbook that I have has about 20 worksheets in it. I want to
have a macro that when click will copy the active sheet to another
workbook and open up a save as dialog (C:\User\TA…xls) so the user can
choose how/which name to save as.

The purpose is not to send a really big workbook to someone when they
only need one worksheet out of everything. It takes forever to save
the workbook because it has too many formulas and worksheets. I just
want the user to be able to save the current/active sheet and be able
to save as.

Thanks!
 
B

Bernard Liengme

Do you want the formulas to reference the original workbook or to be
transformed into values
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

This workbook that I have has about 20 worksheets in it. I want to
have a macro that when click will copy the active sheet to another
workbook and open up a save as dialog (C:\User\TA…xls) so the user can
choose how/which name to save as.

The purpose is not to send a really big workbook to someone when they
only need one worksheet out of everything. It takes forever to save
the workbook because it has too many formulas and worksheets. I just
want the user to be able to save the current/active sheet and be able
to save as.

Thanks!
 
D

Damil4real

Do you want the formulas to reference the original workbook or to be
transformed into values
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email


This workbook that I have has about 20 worksheets in it. I want to
have a macro that when click will copy the active sheet to another
workbook and open up a save as dialog (C:\User\TA…xls) so the user can
choose how/which name to save as.

The purpose is not to send a really big workbook to someone when they
only need one worksheet out of everything. It takes forever to save
the workbook because it has too many formulas and worksheets. I just
want the user to be able to save the current/active sheet and be able
to save as.

Thanks!

To transform into values. They should not reference anything. They
should just show whatever is in there.

Thanks!
 
B

Bernard Liengme

Try this, then clean it up!

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 05/02/2009 by Bernard V Liengme
'

'
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
newname = Application.InputBox(prompt:="Enter file Name")

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Owner\My Documents\" & newname & ".xls",
FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWindow.Close
Range("A1").Select
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Do you want the formulas to reference the original workbook or to be
transformed into values
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email


This workbook that I have has about 20 worksheets in it. I want to
have a macro that when click will copy the active sheet to another
workbook and open up a save as dialog (C:\User\TA…xls) so the user can
choose how/which name to save as.

The purpose is not to send a really big workbook to someone when they
only need one worksheet out of everything. It takes forever to save
the workbook because it has too many formulas and worksheets. I just
want the user to be able to save the current/active sheet and be able
to save as.

Thanks!

To transform into values. They should not reference anything. They
should just show whatever is in there.

Thanks!
 
D

Damil4real

Try this, then clean it up!

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 05/02/2009 by Bernard V Liengme
'

'
    Cells.Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    newname = Application.InputBox(prompt:="Enter file Name")

    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Owner\My Documents\" & newname& ".xls",
FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
        False, CreateBackup:=False
    ActiveWindow.Close
    Range("A1").Select
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email








To transform into values. They should not reference anything. They
should just show whatever is in there.

Thanks!- Hide quoted text -

- Show quoted text -

Nope, code not working.

In red/error are the following lines in the code:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
-------
ActiveWorkbook.SaveAs Filename:= _
"R:\IA\Unitized\Temi A\" & newname & ".xls",
FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
 
G

Gord Dibben

Need more line-continuations "_" to keep long lines as one line.


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

ActiveWorkbook.SaveAs Filename:= _
"R:\IA\Unitized\Temi A\" & newname & ".xls", _
FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:= _
False, CreateBackup:=False


Gord Dibben MS Excel MVP
 
D

Damil4real

Need more line-continuations  "_"  to keep long lines as one line.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

ActiveWorkbook.SaveAs Filename:= _
        "R:\IA\Unitized\Temi A\" & newname & ".xls", _
FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:= _
        False, CreateBackup:=False

Gord Dibben  MS Excel MVP




- Show quoted text -

Code works great now, except it doesn't copy all the formatting,
reduction of cells & rows, merging of cells...etc. I want it to copy
exactly as the previous sheet is. You know, just like moving a sheet
from one workbook to another with no changes whatsover except that the
new sheet not reference the original workbook.

thanks for your continued assistance.
 
G

Gord Dibben

Added the xlpasteformats line

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 05/02/2009 by Bernard V Liengme

Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Range("A1").Select
newname = Application.InputBox(prompt:="Enter file Name")
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Gord\My Documents\" & newname & ".xls", _
FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:= _
False, CreateBackup:=False

ActiveWindow.Close
Range("A1").Select
End Sub


Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top