Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I have a workbook contains formulas and links with 2 Sheets named "Template" and "Data"
    The following macro copy everything on Sheet "Template" to a new workbook

    The worksheet being copied has VBA codes associated with it which
    I do not want copied to the new workbook. How can I copy just the sheet
    without the formulas , VBA code & links; i.e. copy only the Values and Formatting

    Private Sub CommandButton1_Click()


    Dim FN As String
    FN = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls),*.xls")
    If FN = "False" Then
    MsgBox "File Save Cancelled by User"
    Else
    Sheets("Template").Copy
    ActiveSheet.UsedRange.Formula = ActiveSheet.UsedRange.Value
    ActiveWorkbook.SaveAs Filename:=FN, _
    FileFormat:=xlNormal, Password:="", _
    WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=False
    End If
    End Sub

    Thanks
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Does the following help?
    Code:
    Sub PasteOnlyValuesAndFormat()
       '
    	   Cells.Select
    	   Selection.Copy
    	   Windows("Target").Activate
    	   Cells.Select
    	   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    			SkipBlanks:=False, Transpose:=False
    	   Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    		   SkipBlanks:=False, Transpose:=False
       End Sub
    Regards
    Don

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='792251' date='05-Sep-2009 19:51']Does the following help?
    Code:
    Sub PasteOnlyValuesAndFormat()
       '
    	   Cells.Select
    	   Selection.Copy
    	   Windows("Target").Activate
    	   Cells.Select
    	   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    			SkipBlanks:=False, Transpose:=False
    	   Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    		   SkipBlanks:=False, Transpose:=False
       End Sub
    [/quote]

    Thanks for looking into this.

    There's an error at this line
    Windows("Target").Activate


    What does this line suppose to do?

    thanks
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='franciz' post='792254' date='05-Sep-2009 08:05']Thanks for looking into this.

    There's an error at this line
    Windows("Target").Activate


    What does this line suppose to do?

    thanks[/quote]

    Assuming that you wish to paste the data into a file whose name is stored in the variable "FN" (without the quotes), the line will change to:

    Windows(FN).Activate

    The line of code activates the window where you will paste the data. You will also need to ensure that the appropriate sheet is active.
    Regards
    Don

  5. #5
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='792258' date='05-Sep-2009 20:36']Assuming that you wish to paste the data into a file whose name is stored in the variable "FN" (without the quotes), the line will change to:

    Windows(FN).Activate

    The line of code activates the window where you will paste the data. You will also need to ensure that the appropriate sheet is active.[/quote]

    How could the variables be known or come from?

    What if we need open a new workbook?
    How could we change this to
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='franciz' post='792259' date='05-Sep-2009 08:52']How could the variables be known or come from?

    What if we need open a new workbook?
    How could we change this to[/quote]

    The variables would be those assigned by you earlier in the project.

    If you need to open an existing workbook the code would change to:
    Code:
    Sub PasteOnlyValuesAndFormat()
    	'
    	 Cells.Select
    	 Selection.Copy
    	 Workbooks.Open Filename:="filespec"
    	 Sheets("Sheet1").Select
    	 Cells.Select
    	 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    		  SkipBlanks:=False, Transpose:=False
    	 Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    		 SkipBlanks:=False, Transpose:=False
    	End Sub
    If you need to open a New workbook the line
    Workbooks.Open Filename:="filespec"

    would be replaced with
    Workbooks.Add Template:="Workbook"
    Regards
    Don

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='792268' date='06-Sep-2009 00:24']The variables would be those assigned by you earlier in the project.

    If you need to open an existing workbook the code would change to:
    Code:
    Sub PasteOnlyValuesAndFormat()
    	'
    	 Cells.Select
    	 Selection.Copy
    	 Workbooks.Open Filename:="filespec"
    	 Sheets("Sheet1").Select
    	 Cells.Select
    	 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    		  SkipBlanks:=False, Transpose:=False
    	 Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    		 SkipBlanks:=False, Transpose:=False
    	End Sub
    If you need to open a New workbook the line
    Workbooks.Open Filename:="filespec"

    would be replaced with
    Workbooks.Add Template:="Workbook"[/quote]


    Thanks, this works.
    In addition, would I use this line at the end to
    end the copy mode in "Template"

    Application.CutCopyMode = False

    How could I save the new workbook with reference to the name in C5?

    Again thanks for your guide.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    [quote name='franciz' post='792310' date='06-Sep-2009 05:45']Thanks, this works.
    In addition, would I use this line at the end to
    end the copy mode in "Template"

    Application.CutCopyMode = False

    How could I save the new workbook with reference to the name in C5?

    Again thanks for your guide.[/quote]
    You could use code like:
    Code:
    Application.CutCopyMode = False
    Range("A1").Select
    To exit CutCopyMode, and re-select the first cell in the worksheet.

    You could use code like this to save the new workbook with a reference to a name in C5:
    Code:
       Dim NewWbk As Workbook
       Dim strFileName As String
       strFileName = Range("C5").Text
       Set NewWbk = Workbooks.Add(Template:="Workbook")
       NewWbk.SaveAs Filename:=strFileName
       Set NewWbk = Nothing
    Gary

  9. #9
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Gary Frieder' post='792318' date='06-Sep-2009 21:12']You could use code like:
    Code:
    Application.CutCopyMode = False
    Range("A1").Select
    To exit CutCopyMode, and re-select the first cell in the worksheet.

    You could use code like this to save the new workbook with a reference to a name in C5:
    Code:
       Dim NewWbk As Workbook
       Dim strFileName As String
       strFileName = Range("C5").Text
       Set NewWbk = Workbooks.Add(Template:="Workbook")
       NewWbk.SaveAs Filename:=strFileName
       Set NewWbk = Nothing
    Gary[/quote]


    Thanks.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  10. #10
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='franciz' post='792328' date='06-Sep-2009 23:54']Thanks.[/quote]


    Is there a different between running the macro from a commandbutton
    and from the workbook's macro

    The error happen at Cells.Select when I run it from a commandbutton
    but its work when using the workbook's macro.

    Code:
    Sub PasteOnlyValuesAndFormat()
    	'
    	 Cells.Select
    	 Selection.Copy
    	 Workbooks.Open Filename:="filespec"
    	 Sheets("Sheet1").Select
    	 Cells.Select
    	 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    		  SkipBlanks:=False, Transpose:=False
    	 Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    		 SkipBlanks:=False, Transpose:=False
    	End Sub
    TIA
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='franciz' post='792556' date='08-Sep-2009 13:58']Is there a different between running the macro from a commandbutton
    and from the workbook's macro

    The error happen at Cells.Select when I run it from a commandbutton
    but its work when using the workbook's macro.

    Code:
    Sub PasteOnlyValuesAndFormat()
    	   '
    		Cells.Select
    		Selection.Copy
    		Workbooks.Open Filename:="filespec"
    		Sheets("Sheet1").Select
    		Cells.Select
    		Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    			 SkipBlanks:=False, Transpose:=False
    		Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    			SkipBlanks:=False, Transpose:=False
    	   End Sub
    TIA[/quote]
    The only difference of which I am aware is the possibility of running different macros.
    1. Which Cells.Select is highlighted when the code fails?
    2. Where is the code stored which you run successfully?
    3. When the code fails; what message do you receive?
    4. When the code fails and you click "Debug"; what is displayed in the title bar of the Visual Basic Editor?[attachment=85448:TitleBar.gif]
    Attached Images Attached Images
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •