Results 1 to 7 of 7

Thread: Save worksheet with filter

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    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.

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

    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

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    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.

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

    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

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    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.

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

    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

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    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
  •  



Click Here to Expand Forum to Full Width