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
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.
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 .
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
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 .
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
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 .
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
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 ?
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 Worksheet, V, R&, F$ For Each Ws In Worksheets With Ws.[A1].CurrentRegion.Rows If .Count > 1 Then ReDim V(.Count - 2) For R = 2 To .Count: V(R - 2) = Application.EoMonth(.Cells(R, 1), 0): Next Do .Columns(1).AutoFilter 1, , 7, Array(1, Format(V(0), "m/d/yyyy")) F = "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 » ! ◄ ◄
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
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
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) ?
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
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 .
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 !
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?
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.
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.
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 !
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
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 .
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
Bookmarks