Results 1 to 32 of 32

Thread: [RESOLVED] Paste value special

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Resolved [RESOLVED] Paste value special

    Hi.

    In the below code I create a excel workbook, save it to particular path and then attach the file to an email.

    However the file being copied and saved to the specified path is being copied with formula's. I.e not paste value special.

    Where do I insert the code that make sure the worksheet (Master FM)gets copied and pasted without formula's.

    Also the workbook remains open after the email is created. I want the workbook to be closed.



    Sub SendTemplate()

    Dim w As Worksheet, b As Workbook, ol As Object, msg As Object
    Dim mypath As String, myfile As String, scc As String, sto As String
    mypath = "W:\.Team Documents\Freehold Team\Freehold Managers\Reporting\Reports"
    With Sheets("Control")
    sto = Join(WorksheetFunction.Transpose(Range("Mail_to")), ";")
    scc = Join(WorksheetFunction.Transpose(Range("Mail_cc")), ";")
    End With
    Set w = Sheets("Master FM")
    Set b = Workbooks.Add
    w.Copy b.Sheets(1)
    myfile = mypath & Format(Date, "MMM") & " PM Template.xlsx"
    Application.DisplayAlerts = False
    For sh = b.Sheets.Count To 2 Step -1
    b.Sheets(sh).Delete
    Next
    Application.DisplayAlerts = True
    b.SaveAs myfile
    Set ol = CreateObject("outlook.application")
    Set msg = ol.Createitem(0)
    With msg
    .To = sto
    .cc = scc
    .Subject = "FM's PM Template"
    .Body = "Good Morning"
    .attachments.Add myfile
    .display
    End With

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Paste value special

    It's because you're copying the whole sheet:
    Code:
    w.Copy b.Sheets(1)
    If you want to copy just values, then you'll need to select the range you want to copy, then paste the values.

    As for closing it, you'll need to .Close the worksheet and .Close the workbook objects and set them to Nothing, then .Quit the excel object and set it to nothing

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Paste value special

    you can try

    Code:
    Set b = Workbooks.Add
    w.usedrange.copy
    b.sheets(1).range("a1").PasteSpecial xlPasteValues
    myfile = mypath & Format(Date, "MMM") & " PM Template.xlsx"
    myfile = mypath & Format(Date, "MMM") & " PM Template.xlsx"
    Application.DisplayAlerts = False
    For sh = b.Sheets.Count To 2 Step -1
    b.Sheets(sh).Delete
    Next
    Application.DisplayAlerts = True
    b.SaveAs myfile
    b.close
    the second line is changed, i have not tested, if your new workbook only has 1 worksheet, you can omit the part to delete extra sheets

    Application.DisplayAlerts = False
    For sh = b.Sheets.Count To 2 Step -1
    b.Sheets(sh).Delete
    Next
    Application.DisplayAlerts = True
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    Thanks for all the feedback everyone.

    The above worked in terms of removing the formula's as I specified. I should have also mentioned I wish to keep all the formats from the original file as well.

    Is this a seperate line of code I need to add or can I just add the xl.pasteformats on the end of xl.pastevalues piece of of code

  6. #6
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    Re: Paste value special

    Now you tell us.

    Possibly useful link:

    https://stackoverflow.com/questions/...rce-formatting

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Paste value special

    xl.pasteformats on the end of xl.pastevalues
    that should work, use OR to combine them
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    Ok. I've put the below code in and receiving Method'Pastespecial' of object 'range' failed.

    b.Sheets(1).Range("a1").PasteSpecial xlPasteValues Or xlPasteFormats

    Do I need to specify the range again for pasteformats?

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    I did try to enter the code in Red but when I ran the macro, the file size I was creating went from 2mb to 98mb. Why does the size increase so much when applying formats?


    Set w = Sheets("Master FM")
    Set b = Workbooks.Add
    w.UsedRange.Copy
    b.Sheets(1).Range("a1").PasteSpecial xlPasteValues
    b.Sheets(1).Range("a1").PasteSpecial xlPasteFormats
    myfile = mypath & Format(Date, "MMM") & " PM Template.xlsx"
    myfile = mypath & Format(Date, "MMM") & " PM Template.xlsx"
    b.SaveAs myfile
    b.Close

  10. #10
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    Re: Paste value special

    From my link above:

    There's a PasteSpecial option for this:
    ActiveSheet.Range("H10").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
    Operation:= xlNone, SkipBlanks:=False, Transpose:=False


  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Paste value special

    b.Sheets(1).Range("a1").PasteSpecial xlPasteValues
    b.Sheets(1).Range("a1").PasteSpecial xlPasteFormats
    the second will overwrite the first
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    Quote Originally Posted by jdc2000 View Post
    From my link above:

    I'm not sure if this would work though. What happens if cell A1 had a red background? Only the value and format of the font would be copied over.

    I want the value and the background to be copied over.

    Currently I'm either getting the value with no formats or getting the value and formats, but the file size increases 10 fold.

    Another question i have. When copying over the formats. Does this include row and column size? If not, how do i ensure these remain the same as the original file?

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    Ok. I've put the below code in and receiving Method'Pastespecial' of object 'range' failed.

    b.Sheets(1).Range("a1").PasteSpecial xlPasteValues Or xlPasteFormats

    Do I need to specify the range again for pasteformats?

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Paste value special

    isn't that a duplicate of post #8, answer in post #11

    did you try as suggested post #10? it does work without error and probably give the desired results
    if you also need to paste the column widths do that first separately then paste the values, the column widths should stay
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    Hello.

    Sorry for the delay in responding.

    I've added in the code as suggested in post#10.

    but as suspected it didn't have the desired effect I'm looking for.

    a) The attachment that's been created has increased in size to 74mb. Making it too big to attach to an email.
    b) The formats of the cells have not been copied. Only the formats of the fonts. I want formats of cells and fonts.




    Sub SendTemplate()

    Dim w As Worksheet, b As Workbook, ol As Object, msg As Object
    Dim mypath As String, myfile As String, scc As String, sto As String
    mypath = "W:\.Team Documents\Freehold Team\Freehold Managers\Reporting\Reports"
    With Sheets("Control")
    sto = Join(WorksheetFunction.Transpose(Range("Mail_to")), ";")
    scc = Join(WorksheetFunction.Transpose(Range("Mail_cc")), ";")
    End With
    Set w = Sheets("Master FM")
    Set b = Workbooks.Add
    w.UsedRange.Copy
    b.Sheets(1).Range("a1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, Skipblanks:=False, Transpose:=False
    myfile = mypath & Format(Date, "MMM") & " PM Template.xlsx"
    myfile = mypath & Format(Date, "MMM") & " PM Template.xlsx"
    b.SaveAs myfile
    b.Close

    Set ol = CreateObject("outlook.application")
    Set msg = ol.Createitem(0)
    With msg
    .To = sto
    .cc = scc
    .Subject = "FM's PM Template"
    .Body = "Good Morning"
    .attachments.Add myfile
    .display
    End With

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Paste value special

    i think the only way you can achieve the desired result is to copy the worksheet rather than the range, but i seem to remember you had some issue with that too

    you should test to see if you can copy to a new workbook and save manually with the correct formatting and not excessive size
    if the original code as posted #1 does copy the formatting as required, we could look at removing the formulas from the new workbook and saving

    post a sample workbook (zip first) with formatting etc. if you want someone to test
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Paste value special

    i think the only way you can achieve the desired result is to copy the worksheet rather than the range, but i seem to remember you had some issue with that too

    you should test to see if you can copy to a new workbook and save manually with the correct formatting and not excessive size
    if the original code as posted #1 does copy the formatting as required, we could look at removing the formulas from the new workbook and saving
    Code:
    b.sheets(1).usedrange.value = b.sheets(1).usedrange.text
    post a sample workbook (zip first) with formatting etc. if you want someone to test
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    Hi.

    This is proving to be a little tricky. :-)

    If for example. I did the below, manually without any coding then we should be able to replicate these actions via some coding.

    1. Copy the worksheet and paste special value into new file. This will eliminate the pasting of formulas.
    2. Copy paste special formats of worksheet into new file. This ensures the formats are correctly copied over.

    I have enclosed a smple.zip.

    Its thee master file I wish to copy with the same data and formats.


    thankssample.zip
    Last edited by kris01; Jan 14th, 2021 at 09:52 AM. Reason: add attachment

  19. #19
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Paste value special

    based on your sample workbook, I have made some changes to the original code that appears to work correctly
    the saved file appears to have the original formatting, no formulas and size of 29kb

    Code:
    Dim w As Worksheet, b As Workbook, ol As Object, msg As Object
    Dim mypath As String, myfile As String, scc As String, sto As String
    mypath = "W:\.Team Documents\Freehold Team\Freehold Managers\Reporting\Reports"
    With Sheets("Control")
    sto = Join(WorksheetFunction.Transpose(Range("Mail_to")), ";")
    scc = Join(WorksheetFunction.Transpose(Range("Mail_cc")), ";")
    End With
    Set w = Sheets("Master FM")
    Set b = Workbooks.Add
    with w
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
    end with
    b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value2
    
    myfile = mypath & Format(Date, "MMM") & " PM Template.xlsx"
    'Application.DisplayAlerts = False
    'For sh = b.Sheets.Count To 2 Step -1
    'b.Sheets(sh).Delete
    'Next
    'Application.DisplayAlerts = True
    b.SaveAs myfile
    Set ol = CreateObject("outlook.application")
    Set msg = ol.Createitem(0)
    With msg
    .To = sto
    .cc = scc
    .Subject = "FM's PM Template"
    .Body = "Good Morning"
    .attachments.Add myfile
    .display
    End With
    test and see how you go
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  20. #20

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    Thanks so much. That's worked a treat.

    How do I get the columns to auto re-size?

  21. #21
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Paste value special

    you can try to copy the column widths first
    like
    Code:
    With w
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Cells(1, 1).Resize(lr, lc).Copy
        b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
        .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
    End With
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  22. #22

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    Quote Originally Posted by westconn1 View Post
    you can try to copy the column widths first
    like
    Code:
    With w
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Cells(1, 1).Resize(lr, lc).Copy
        b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
        .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
    End With

    Hi. Get a compile error on the last line.

    Also. The attachment name is wrong. Need it to say - FM MMM PM Template.xlsx.

  23. #23
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Paste value special

    Get a compile error on the last line.
    what error are you getting?

    i did not save the sample book or code so i can not test at this time
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  24. #24

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    The error message is - Compile error: Syntax error.

    Breaks on this line.

    .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")

  25. #25
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Paste value special

    i have no idea why that should give a syntax error, i know i tested all the code in post #19 before i posted, it all ran without error
    that line is the same as other line in the excerpt you posted except the destination is included

    you can try to break it into 2 lines like
    Code:
    .Cells(1, 1).Resize(lr, lc).Copy 
    b.Sheets(1).Range("a1") .paste
    or may have to be pastespecial, that may affect the desired result, but at least it should determine where the syntax error is
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  26. #26

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    ok problem fixed. The l and the 1 look familiar.

    One more issue though and this is relating to the attachment name.

    myfile = mypath & Format(Date, "MMM") & " PM Template.xlsx"

    The above code saves the attachment name as....

    ReportsMMM PM Template.xlsx.

    How do we change the code so it saves the file as....

    MMM FM PM template.xlsx.

    I tried to remove the mypath from the above and code and it kept breaking.

  27. #27
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    Re: Paste value special

    You should not be removing mypath, instead change it from:

    Code:
    mypath = "W:\.Team Documents\Freehold Team\Freehold Managers\Reporting\Reports"
    to whatever the path is to the folder you want to save the files in.

  28. #28

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    The file path is not the problem. Its the name of the file that's being saved down. How do I change it?



    Also. Need a new macro using the below, but instead of attaching the file to an email. I wish to copy the worksheet and the existing format as per the below and save this in a new workbook called FM Report builder.

    Path is....W:\.Team Documents\Freehold Team\Freehold Managers\Reporting\

    Need the work sheet pasted on tab - FM

    Dim w As Worksheet, b As Workbook, ol As Object, msg As Object
    Dim mypath As String, myfile As String, scc As String, sto As String
    mypath = "W:\.Team Documents\Freehold Team\Freehold Managers\Reporting\Reports"
    With Sheets("Control")
    sto = Join(WorksheetFunction.Transpose(Range("Mail_to")), ";")
    scc = Join(WorksheetFunction.Transpose(Range("Mail_cc")), ";")
    End With
    Set w = Sheets("Master FM")
    Set b = Workbooks.Add
    with w
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
    end with
    b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 4, lc).Value2

  29. #29
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    Re: Paste value special

    According to the information in your post #26, change:

    Code:
    mypath = "W:\.Team Documents\Freehold Team\Freehold Managers\Reporting\Reports"
    to:

    Code:
    mypath = "W:\.Team Documents\Freehold Team\Freehold Managers\Reporting\"

  30. #30

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    oh ok. See what you mean now. Sorry. That worked.

    Any joy on the second part of my post #28

    Thanks

    Kris Morris

  31. #31
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Paste value special

    as you have not told us what the actual file name being saved it is a bit hard to know the solution, but it may be
    Code:
    myfile = mypath & "\" & Format(Date, "MMM") & " PM Template.xlsx"
    or do you just want the literal MMM rather than the month?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  32. #32

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Paste value special

    That part of post #28 is complete now.

    I'll start a new thread because I think you're getting confused.

Posting Permissions

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



Click Here to Expand Forum to Full Width