-
Jan 6th, 2021, 12:06 PM
#1
Thread Starter
Addicted Member
[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
-
Jan 6th, 2021, 12:26 PM
#2
Re: Paste value special
It's because you're copying the whole sheet:
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
-
Jan 6th, 2021, 02:29 PM
#3
-
Jan 6th, 2021, 03:09 PM
#4
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
-
Jan 7th, 2021, 09:32 AM
#5
Thread Starter
Addicted Member
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
-
Jan 7th, 2021, 02:06 PM
#6
-
Jan 7th, 2021, 03:10 PM
#7
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
-
Jan 8th, 2021, 05:05 AM
#8
Thread Starter
Addicted Member
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?
-
Jan 8th, 2021, 05:40 AM
#9
Thread Starter
Addicted Member
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
-
Jan 8th, 2021, 08:31 PM
#10
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
-
Jan 8th, 2021, 10:50 PM
#11
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
-
Jan 9th, 2021, 05:51 AM
#12
Thread Starter
Addicted Member
Re: Paste value special
Originally Posted by jdc2000
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?
-
Jan 9th, 2021, 05:52 AM
#13
Thread Starter
Addicted Member
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?
-
Jan 9th, 2021, 06:43 AM
#14
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
-
Jan 13th, 2021, 06:03 AM
#15
Thread Starter
Addicted Member
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
-
Jan 13th, 2021, 02:58 PM
#16
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
-
Jan 13th, 2021, 03:02 PM
#17
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
-
Jan 14th, 2021, 09:52 AM
#18
Thread Starter
Addicted Member
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
-
Jan 15th, 2021, 06:09 AM
#19
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
-
Jan 15th, 2021, 08:53 AM
#20
Thread Starter
Addicted Member
Re: Paste value special
Thanks so much. That's worked a treat.
How do I get the columns to auto re-size?
-
Jan 15th, 2021, 03:20 PM
#21
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
-
Feb 4th, 2021, 07:33 AM
#22
Thread Starter
Addicted Member
Re: Paste value special
Originally Posted by westconn1
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.
-
Feb 4th, 2021, 03:06 PM
#23
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
-
Feb 5th, 2021, 07:37 AM
#24
Thread Starter
Addicted Member
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")
-
Feb 5th, 2021, 03:28 PM
#25
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
-
Feb 9th, 2021, 11:41 AM
#26
Thread Starter
Addicted Member
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.
-
Feb 9th, 2021, 12:21 PM
#27
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.
-
Feb 9th, 2021, 12:43 PM
#28
Thread Starter
Addicted Member
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
-
Feb 9th, 2021, 01:25 PM
#29
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\"
-
Feb 9th, 2021, 02:20 PM
#30
Thread Starter
Addicted Member
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
-
Feb 9th, 2021, 02:57 PM
#31
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
-
Feb 9th, 2021, 03:01 PM
#32
Thread Starter
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|