+ Reply to Thread
Results 1 to 51 of 51

save sheet for multiple PDF files based on each month

  1. #1
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    save sheet for multiple PDF files based on each month

    Hi
    I have multiple sheets(DATA,SOURCE,OUT.....) contain data start from column (A) : column (K) , the date is in column A (DD/MM/YYYY) . what I want split sheet into multiple files as PDF based on month . but should create folder by adding the year to the sheet name based on sheet name to become like this (DATA-2021,SOURCE-2022,OUT-2023)
    the folders names for years depends on DATE inside for each sheet based on column A
    when save every file as PDF , the data should relate for the month of year for each folder .
    should save file name JAN MONTH_2022 , FEB MONTH_2022 as pdf inside the folder is relating of the year and so on up to finish year 2022 inside the folders and start again from new year 2023 by save the files as PDF for each month for the folder is relating 2023 year to become JAN MONTH_2023 , FEB MONTH_2023 .... . and should replace the files inside the folders when run the macro every time .
    I hope this clear
    thanks
    Attached Files Attached Files
    Last edited by Mussa-A; 01-26-2023 at 06:37 AM.

  2. #2
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    I no know if what I ask for it could be possible .

  3. #3
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: save sheet for multiple PDF files based on each month

    Yes,
    It's possible to do.

    If you want to receive help, you should post a workbook containing a sample of your data to be splited.
    Read the yellow banner on top of this page
    Barriers are there for those who don't want to dream

  4. #4
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    Hi vichopalacios
    thanks for informing that
    I attached the file . I hope anybody can help

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    Hi,

    rather than a guessing challenge give at least expected full path details …

  6. #6
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    Hi Marc,
    do you want the path?
    should be in this path
    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    So that not matches your initial post where you wrote « DATA-2021 » so like D:\DATA-2021\ and as I asked for a « full path details » …
    Anyway I could post a VBA demonstration working on my side but you will have to fit it on your own.

  8. #8
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    So that not matches your initial post where you wrote « DATA-2021 » so like D:\DATA-2021\ and as I asked for a « full path details » …
    as to you misread or this is not clear for you .

    but should create folder by adding the year to the sheet name based on sheet name to become like this (DATA-2021,SOURCE-2022,OUT-2023)
    macro also supposes creating folder without interfere from me .

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    So not clear for anyone, good luck ! …

  10. #10
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    start gain :
    1- the full path is "D:\DATA\YEAR"
    2- when create the folder in path for each sheet should create folder based on year is in existed in column A for each sheet . as in example DATA sheet in column A contains 2021 year so when add the year to sheet name "DATA "
    the folder name will become DATA_2021 and will put in path to become "D:\DATA\YEAR\DATA_2021"
    3- when split data for each month for DATA sheet based on column A into multiple files PDF
    to become JAN MONTH_2022 , FEB MONTH_2022 as PDF and put them in folder DATA_2021 and so on for each sheet create folder name as the same way and split into multiple files as PDF and put in the created folder
    4- sometimes could adjusting data for sheets then should replace files have already existed and every time will add new sheet so the process should implement fo all of the sheets into file .
    thanks for your time .

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    1 - As it can not be obviously the full path ‼

    So without each full path name for each pdf file according to each worksheet expected since post #5
    so according to post #7 are you able to fit any VBA code any helper can share for the necessary complete full path name when saving each file ?

    Better : post your own VBA codeline for the complete file reference to create then it will be easy for any helper to include it in his code …

  12. #12
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    the full path become like this when save file for jan month
    Please Login or Register  to view this content.
    when create new folder for each sheet will put the folders in YEAR folder as show in path I posted
    the DATA\YEAR are folders as show in path .
    I hope to don't misunderstand you if you mean this .

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: save sheet for multiple PDF files based on each month


    So the full path should be "D:\DATA\YEAR\DATA_2021\JAN MONTH_2022.pdf" ?‼ According to which worksheet ?

    According to post #11 we are expecting each full path name according to each worksheet …

  14. #14
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    According to post #11 we are expecting each full path name according to each worksheet …
    yes
    for more example when save file for FEB month will become "D:\DATA\YEAR\DATA_2021\FEB MONTH_2022.pdf"
    SOURCE worksheet will create folder SOURCE-2022, for JAN month , then the path will be
    "D:\DATA\YEAR\SOURCE-2022\JAN MONTH_2022.pdf" and so on

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    Open your initial post attachment then according to each worksheet what should be each exact full path pdf name ?

  16. #16
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    sorry about the error for PDF file for DATA sheet in post #14 !
    DATA sheet
    "D:\DATA\YEAR\DATA_2021\JAN MONTH_2021.pdf"
    SOURCE sheet:
    "D:\DATA\YEAR\SOURCE_2022\FEB MONTH_2022.pdf"
    OUT sheet
    "D:\DATA\YEAR\OUT_2023\JAN MONTH_2023.pdf"
    note: ecah sheet contains months throught the year , just I give you one month for each sheet to how export the file for each month .

  17. #17
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    As it was a red evidence since post #13 …

    Now as the logic works I may automize the export of your workbook but
    notice each pdf file layout will be based on the actual worksheet print setup like when you save the workbook to pdf manually …

  18. #18
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: save sheet for multiple PDF files based on each month


    With each manual worksheet print setup you must achieve,
    another important point : does your Windows Regional Settings set to US or UK in order short month is 'Feb' for february ?

  19. #19
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    With each manual worksheet print setup you must achieve,
    understood
    another important point : does your Windows Regional Settings set to US or UK in order short month is 'Feb' for february ?
    Regional Settings is US

  20. #20
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !


    According to posts #1, 16 & 19 a starter VBA demonstration to paste to the top of a module :

    PHP Code: 
    Private Declare PtrSafe Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal DirPath$) As Boolean

    Sub Demo1
    ()
             
    Dim Ws As WorksheetVR&, F$
        For 
    Each Ws In Worksheets
            With Ws
    .[A1].CurrentRegion.Rows
                
    If .Count 1 Then
                    ReDim V
    (.Count 2)
                    For 
    2 To .Count:  V(2) = Application.EoMonth(.Cells(R1), 0):  Next
                
    Do
                   .
    Columns(1).AutoFilter 1, , 7, Array(1Format(V(0), "m/d/yyyy"))
                    
    "D:\DATA\YEAR\" & Ws.Name & Format(V(0), "_yyyy\\mmm"" month_""yyyy ")
                    If MakeSureDirectoryPathExists(F) Then .ExportAsFixedFormat 0, F, 0
                    V = Filter(V, V(0), False)
                Loop Until UBound(V) = -1
                   .AutoFilter
                End If
            End With
        Next
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  21. #21
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    thanks
    it gives error 1004 autofilter method of range class failed
    Please Login or Register  to view this content.
    despite of I change to dd/mm/yyyy based on my file , but the error still shows and my Regional Settings set to US also
    format DATE is DD/MM/YYYY

  22. #22
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    As on my side under XL 2010 Demo1 works as expected with your initial post attachment !

    Try the demonstration as it is with the same initial post attachment and if you get the same issue then
    activate the Macro Recorder and manually filter a sheet on a month in order to compare …

  23. #23
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    it shows
    Please Login or Register  to view this content.
    but when I write any date for any sheet will show dd/mm/yyyy and based on recorded macro show M/D/YYYY
    even if I changed format date inside the sheet as in the code . still the error show
    you can see the date format from Regional Settings
    Attached Images Attached Images

  24. #24
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: save sheet for multiple PDF files based on each month


    As on my side my dates Windows Regional setting is dd/mm/yyyy and as my demonstration works a treat so
    IMO it must be as it is like within my demonstration and like within the code generated by the Macro Recorder.
    If you have tested with exactly your initial post attachment as it is so I can't see any light …

    With the inital post attachment when the error rises what contains V(0) ?

  25. #25
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    Hi Marc
    in reality I really did based on inital post attachment but I no know what's the problem
    as you see in the picture dd/mm/yyyy but in recorded macro it's differenn
    actually I will test for another laptop at work and see how works, I will inform you tomorrow .
    thanks

  26. #26
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    You did not again answer to my question and as I won't guess anything as I'm very not a mind reader …


    Quote Originally Posted by Mussa-A View Post
    as you see in the picture dd/mm/yyyy but in recorded macro it's differenn
    Again that's normal 'cause the Excel / VBA engine is natively in english even with a local version, so very not a surprise !

    If any helper could try my post #20 demonstration with your initial post attachment …
    Last edited by Marc L; 01-28-2023 at 04:02 PM.

  27. #27
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    what I understand work for you without any problem . so the problem could be from Regional Settings in my laptop .
    as I siad I have to test with another laptop to see how work .

  28. #28
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    Read post #24 until the end …

  29. #29
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    With the inital post attachment when the error rises what contains V(0) ?
    this is what shows
    Attached Images Attached Images

  30. #30
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: save sheet for multiple PDF files based on each month


    That's correct as 44227 is 31/01/2021 …

    Try to add a $ to Format : Format$(V(0),

  31. #31
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    if you mean like this
    Please Login or Register  to view this content.
    also doesn't work .

  32. #32
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Exclamation Re: save sheet for multiple PDF files based on each month


    Yes but only according to my original codeline as it is and not on yours which can't work …

  33. #33
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    if you mean the date format should be "m/d/yyyy"
    also doesn't work .

  34. #34
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    Yes 'cause my codeline works on my side but not yours …

    Before the AutoFilter codeline in order to see the Format result insert this codeline : MsgBox Format(V(0), "m/d/yyyy")
    Or with Debug.Print in place of MsgBox, whatever …
    Last edited by Marc L; 01-28-2023 at 05:07 PM. Reason: bad format !

  35. #35
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    gives 31/01/2021

  36. #36
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    So I suppose that's 1/31/2021 as expected with "m/d/yyyy" …

  37. #37
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    based on your code , yes . but my Regional Settings in my laptop and inside the sheet dd/mm/yyyy.

  38. #38
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    Again : as I use exactly the same …

    According to the Macro Recorder let's try the VBA long syntax :

    .Columns(1).AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(1, Format(V(0), "m/d/yyyy"))

  39. #39
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    unfortunately this doesn't work . the same error .
    strangely works perfectly for another laptop with office version 2016 with format Regional Settings set to US also & date format (DD-MM-YYYY)
    this causes headache about current laptop with offic version 2019
    could reinstall win system fixes this problem?

  40. #40
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    No idea, the way used should work whatever the Regional Settings like on my side set as dd/mm/yyyy
    and I never met such issue with this Excel basics feature …

    An alternative could be to manually filter with >= first day of month and <= last day of month with the Macro Recorder
    then unfilter and try the generated code …

    Another alternative could be an advanced filter in place of the filter.

  41. #41
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    Check also within the Regional Settings the character as date delimiter / separator …

  42. #42
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    can you tell me what means seven number in this line ?
    Please Login or Register  to view this content.
    sorry if the question is silly , but I think the problem from it.
    so I need to understand what indicate to .
    what I understand it will filter based on first DATE column and will copy seven columns after DATE column but why shows error , despite of should finish up to column K, but if I decide finshing up to column H shouldn't show error .
    What I think the error if I indicate to empty column then can't filter and will show error.
    Last edited by Mussa-A; 01-29-2023 at 03:41 PM.

  43. #43
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    You are wrong as it's the constant xlFilterValues as a parameter like you can check in Range.AutoFilter VBA help and
    as a reminder it works as it is on my side and on your second laptop under 2016 version …
    And just reading the codeline it is based on first column only and as a filter does not copy anything !
    Last edited by Marc L; 01-29-2023 at 04:07 PM.

  44. #44
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    And just reading the codeline it is based on first column only and as a filter does not copy anything !
    ok
    but what if I tell you when change seven to other value , then will work without any error . that's why I ask you about seven number .

  45. #45
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    The reason why I wrote my posts #40 & 41 …

  46. #46
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    about seven number , does it indicate how count number character for d/m/yyyy? if it's so why is 7 not 8 ? as to me I changed to 10 works without any error

  47. #47
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    No as this is just a constant parameter as you can see in VBA help or in the code you have generated with the Macro Recorder …

  48. #48
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    No as this is just a constant parameter as you can see in VBA help
    but should show error if I change this number, and this doesn't what happens !

  49. #49
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    Not always as you can see in VBA help !

    Read post #40 & 41 then try an alternative …

  50. #50
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: save sheet for multiple PDF files based on each month

    Hi Marc ,
    I tested your code for another laptop with date format DD/MM/YYYY with office version 2010
    it works perfectly without any error .
    I bet my current laptop is no longer working at all
    Currently there is a hardware repair shop, and the engineer told me that the mother board is very likely to be no longer valid for work, so I am not surprised that some programming errors occurred when running some codes related to Excel .
    sorry for delaying .
    thanks very much for your help .

  51. #51
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: save sheet for multiple PDF files based on each month


    If you have only this unique issue with my code but all your other workbooks well work with some VBA procedures included
    so your laptop is good enough, no need to change it …

    A good upgrade if it uses a mechanic hardrive Sata-2 or 3 is to replace it with a SSD Sata-3 drive
    if you have the necessary to install Windows & software or just using a disk clone tool like Acronis, Macrium Reflect, whatever …
    Last edited by Marc L; 02-05-2023 at 08:05 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. filter files names based on extensions files & month
    By Hasson in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-14-2022, 04:25 AM
  2. [SOLVED] save files to specific folders and creates folders based on month
    By MKLAQ in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-21-2021, 05:11 PM
  3. Save multiple files from data in sheet
    By keaveneydan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2020, 10:48 AM
  4. [SOLVED] Import txt Files into sheet based last month (name file)
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-16-2014, 08:38 AM
  5. Open multiple text files to different sheets and save each sheet seperately
    By lance01 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-13-2014, 10:20 AM
  6. Macro to open multiple files, remove header and save multiple files in a new format
    By twocircles in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-17-2014, 05:24 PM
  7. Save Multiple Files based on List AND Populate a Cell with value
    By aetedford in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2013, 04:25 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1