-
Oct 30th, 2020, 07:08 AM
#1
Thread Starter
Addicted Member
Save worksheet with filter
Hi.
I'd like to save a worksheet with a particular name with today's date as a new file, but before I save the file. I want to create the new file with only particular rows that has a certain filter. I.e Apply a filter then copy paste special into a new work book and save with a specified name along with todays date.
Actions to be taken.
1. Select master file
2. Filter on E&M portoflio. Select - TUSCOLA.
3. Copy paste special all columns and rows into new work book.
4. Rename tab name as Master
5. Save new file as 'EM tusk report wc - (Todays date - Format dd/mm/yyyy) . xlsx
6 New file must be saved in (W:\.Team Documents\Freehold Team\E&M Report\Weekly E&M folder.
See file attached
sample.xml
Thanks and regards.
-
Oct 30th, 2020, 11:47 PM
#2
Re: Save worksheet with filter
5. Save new file as 'EM tusk report wc - (Todays date - Format dd/mm/yyyy) . xlsx
you can not have / in filename, you would need to change the format, the rest is easy enough
Code:
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")
.AutoFilter
End With
mypath = "W:\.Team Documents\Freehold Team\E&M Report\Weekly E&M folder\"
wb.SaveAs (mypath & Format(Date, "dd-mm-yyyy") & ".xlsx")
wb.Close ' or not
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 2nd, 2020, 06:27 AM
#3
Thread Starter
Addicted Member
Re: Save worksheet with filter
Hello,
Almost works. However the code breaks on this line
wb.SaveAs (mypath & Format(Date, "dd-mm-yyyy") & ".xlsx")
Error message is
Run-time error '1004':
Method 'SaveAs' of object '_Workbook' Failed.
Is this because I have not assigned Wb.SaveAS as a variable?
Also. I have a further requirement which wasn't part of my original request. I need to ensure the row\width size is the same as the tab they are copied from. How do i code this?
Thanks.
-
Nov 2nd, 2020, 02:59 PM
#4
Re: Save worksheet with filter
Is this because I have not assigned Wb.SaveAS as a variable
no, saveas is a method of a workbook object, it worked correctly here, but to a different path
my best guess is your path is incorrect, possibly the . after w:\
I need to ensure the row\width size is the same as the tab they are copied from. How do i code this?
try changing like
Code:
.AutoFilter field:=2, Criteria1:="TUSCOLA"
.SpecialCells(xlCellTypeVisible).Copy
wb.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths Or xlPasteFormulasAndNumberFormats
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 3rd, 2020, 09:44 AM
#5
Thread Starter
Addicted Member
Re: Save worksheet with filter
Hello.
Save function now works and you were correct about the path. Thanks for that.
wb.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths Or xlPasteFormulasAndNumberFormats
When I open the file that's been saved down I noticed the formula's have been copied as well from the original file. I need the whole file to be paste special value only, thus taking away any formula's.
Also. I have a further a requirement. The headers in the original file have been coloured. I need to copy the formats on these headers and pasted into the new file as well.
-
Nov 4th, 2020, 02:26 AM
#6
Re: Save worksheet with filter
try this instead
Code:
wb.Sheets(1).Range("a1").PasteSpecial xlPasteFormats Or xlPasteColumnWidths Or xlPasteValues
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 4th, 2020, 05:07 AM
#7
Thread Starter
Addicted Member
Re: Save worksheet with filter
Hello.
This is my code now. Formats are working, however the file saved down is blank. i.e missing content. What have we missed?
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 xlPasteFormats Or xlPasteColumnWidthsOrxlPasteValues
.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
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
|