Another Quick Question. Thanks, guys!

  • Thread starter Thread starter Damil4real
  • Start date Start date
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!
 
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!
 
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!
Click to expand...

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

Thanks!
 
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!
Click to expand...

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

Thanks!
 
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 -
Click to expand...

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
 
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
 
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 -
Click to expand...

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.
 
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
 
Back
Top