-
Nov 5th, 2020, 12:17 PM
#1
Thread Starter
Addicted Member
[RESOLVED] Pastespecial Paste Columnwidths code break
Hello.
Does anyone know why I keep getting a 'Compile error - expected: end of statement on the highlighted line? it breaks when I add .pastespecial xlpasteColumnwidths
Sub Save()
Dim wb As Workbook, mypath As String
With Worksheets("Master").UsedRange
Set wb = Workbooks.Add
wb.Sheets(1).Name = "Master"
.AutoFilter field:=2, Criteria1:="TUSCOLA"
.SpecialCells(xlCellTypeVisible).Copy wb.Sheets(1).Range("a1").Pastespecial xlPasteColumnwidths
.AutoFilter
End With
mypath = "W:\.Team Documents\Freehold Team\E&M Report\Weekly E&M\EM Tusk report WC "
wb.SaveAs (mypath & Format(Date, "dd-mm-yyyy") & ".xlsx")
wb.Close ' or not
Range("Save").Value = Format(Now, "dd/mm/yyy HH:mm:SS")
-
Nov 5th, 2020, 12:31 PM
#2
Re: Pastespecial Paste Columnwidths code break
Try this:
Code:
Sub Save()
Dim wb As Workbook, mypath As String
With Worksheets("Master").UsedRange
Set wb = Workbooks.Add
wb.Sheets(1).Name = "Master"
.AutoFilter field:=2, Criteria1:="TUSCOLA"
.SpecialCells(xlCellTypeVisible).Copy wb.Sheets(1).Range("a1").PasteSpecial Paste:=xlPasteColumnwidths
.AutoFilter
End With
mypath = "W:\.Team Documents\Freehold Team\E&M Report\Weekly E&M\EM Tusk report WC "
wb.SaveAs (mypath & Format(Date, "dd-mm-yyyy") & ".xlsx")
wb.Close ' or not
Range("Save").Value = Format(Now, "dd/mm/yyy HH:mm:SS")
https://docs.microsoft.com/en-us/off...e.pastespecial
https://www.automateexcel.com/vba/va...-pastespecial/
https://stackoverflow.com/questions/...of-the-columns
-
Nov 5th, 2020, 03:51 PM
#3
Thread Starter
Addicted Member
Re: Pastespecial Paste Columnwidths code break
Tried it but same error message and breaks on the word Paste=xl
.SpecialCells(xlCellTypeVisible).Copy wb.Sheets(1).Range("a1").PasteSpecial Paste:=xlPasteColumnwidths
-
Nov 6th, 2020, 03:02 AM
#4
Re: Pastespecial Paste Columnwidths code break
you can not do pastespecial on the same line as the copy, split it onto 2 lines
Code:
.SpecialCells(xlCellTypeVisible).Copy
wb.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
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
-
Nov 6th, 2020, 05:11 AM
#5
Thread Starter
Addicted Member
Re: Pastespecial Paste Columnwidths code break
ok, I've done that. Split them across two lines. but now the file is being saved down as a blank file.
Thanks
Here's my code.
Sub Save()
Dim wb As Workbook, mypath As String
With Worksheets("Master").UsedRange
Set wb = Workbooks.Add
wb.Sheets(1).Name = "Master"
.AutoFilter field:=2, Criteria1:="TUSCOLA"
.SpecialCells(xlCellTypeVisible).Copy
wb.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
.AutoFilter
End With
mypath = "W:\.Team Documents\Freehold Team\E&M Report\Weekly E&M\EM Tusk report WC "
wb.SaveAs (mypath & Format(Date, "dd-mm-yyyy") & ".xlsx")
wb.Close ' or not
Range("Save").Value = Format(Now, "dd/mm/yyy HH:mm:SS")
End Sub
-
Nov 7th, 2020, 03:17 AM
#6
Re: Pastespecial Paste Columnwidths code break
of course, because you are only telling to paste the column widths, you need to paste multiple items, which would have include values and formats if required
i am sure i posted what you required in your other thread
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
-
Nov 9th, 2020, 05:28 AM
#7
Thread Starter
Addicted Member
Re: Pastespecial Paste Columnwidths code break
Morning.
Yes you did, but the code you supplied didn't work.
Ive added the below to the existing code and it still doesn't work.
wb.Sheets(1).Range("a1").PasteSpecial xlPasteFormats Or xlPasteColumnWidths xlPasteValues.
The error message is 'PasteSpecial method of Range class failed.
What am i doing wrong?
-
Nov 10th, 2020, 03:35 AM
#8
Re: Pastespecial Paste Columnwidths code break
you can try this variation, seems do all you want
Code:
wb.Sheets(1).Name = "Master"
.AutoFilter field:=2, Criteria1:="TUSCOLA"
.SpecialCells(xlCellTypeVisible).Copy
wb.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
.SpecialCells(xlCellTypeVisible).Copy wb.Sheets(1).Range("a1")
.AutoFilter
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
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
|