Hello
Can someone please help me with this ?
I found a VBA code online that converts the file to PDF. But the range has to be manually defined.
How to define the range based on the month.(every new month)
Thank you !
Regards
Hello
Can someone please help me with this ?
I found a VBA code online that converts the file to PDF. But the range has to be manually defined.
How to define the range based on the month.(every new month)
Thank you !
Regards
Last edited by Fulminare; 10-01-2021 at 05:43 AM.
Please Login or Register to view this content.
Hi !
Yes it's converting fine. But how to define it by new month ?
(I apologise for not being clear in my previous post)
Only The column CDE's data of every month should get converted along with the headers.
i.e if it is October, all the entries in that month, in CDE columns, should get converted to PDF. Not the rest of the file. (The resulting PDF would only contain the entries of October)
The headers are in the first row. So 1st row of CDE columns + all the entries in that particular month (again in the columns CDE), each month.
I am sorry for causing additional trouble due to my poor wording in my first question.
Last edited by Fulminare; 10-01-2021 at 12:58 AM.
You want to save Columns C, D and E to PDF.
In your example, Column E is empty. Do you mean Columns B, C and D?
Administrative Note:
We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thank you for pointing it out. I must have accidentally typed "PDF" in the subject section, when it was meant for the "tags"
I have changed the Subject in my original post as per your advice. I will reframe my question and post it once again.
Sorry for the inconvenience.
Regards
Last edited by AliGW; 10-01-2021 at 05:51 AM. Reason: PLEASE don't quote unnecessarily!
Please do NOT start a new thread - if you do, it will be closed as a duplicate.
Just continue here.
Okay this is what I am trying to do.
Monthly data of excel should get converted to PDF.
The data should be taken only from columns : B C D.
Row 1 contains the headers.
All the entries in that month along with the headers should get converted. (the pdf should contain only that particular month's data. not the entire file.)
So every month The pdf must contain 1st row headers and that month's data (again, of columns B C D)
This should happen for all sheets other than the "Master Sheet". (in future if there are new sheets, even that data should get converted)
A new file is attached for reference.
I request the experts/gurus to kindly guide me.
Regards.
And what is your reply to post #2?
It's a good thing that AliGW forced you to go over your whole problem again.
Your first Post should have been like Post #8.
The requests between the 2 posts are quite a bit different, so for the future, please take some time to be to the point and cover all problem areas you want to achieve.
That way you will not waste helpers time.
To come back to your problem, try this.
Re: "I am confused." Join the club. I think it is age related so get used to it!!!!!!!Please Login or Register to view this content.
Last edited by jolivanes; 10-01-2021 at 11:40 AM.
Yes I know.
I made a big mess.
So that code creates PDFs for all the sheets other than the master sheet isn't it.
the file I am using already has macros. So I inserted a new module and pasted your code.
It appears to be running, but I am not able to figure out where the files are getting stored.
I have changed the locations from "C:\E-Mail Downloads\" to "C:\Users\user\Downloads". Still no results.
wait I think it is working.
I will post an update tomorrow.
Thanks a lot for your help and patience
Hi jolivanes !
Your code works.
couple of things I realised after running it :
1.
This code runs at the end of every month. So the last month's data should be printed not the current month's (I know I asked for the current month's data. only after I ran the code It struck to me that there will be no new data for the current month.)
I tried changing some values to -1. It's not working properly.
2.
In my actual file there are two sheets that need to be excluded. i.e "master sheet" + "another sheet"
I gave a shot at adding the "another sheet" 's name by using "&" "," e.t.c.
3.
How to have a fixed name for the files ? (not the name of the sheets) Like "Fixed Name" "Last Month".PDF ( I was able to assign the last month to the name of the PDF by MonthName(Month(Now) - 1) & ".PDF"
couldn't figure out how to get the "Fixed Name" though.
(I am thinking of a method to identify the files when all are having the same name.)
4 Lastly, how to specify the location for saving the files.
Please note, I tried to solve at least a couple of them by searching the net. I didn't want to trouble you unnecessarily. Only after failing to modify the code I am approaching you/the forum once again.
The answers, in addition to solving my problem also help me to understand and learn the code bit by bit.
Regards
Last edited by Fulminare; 10-02-2021 at 12:47 AM.
Change this
to thisPlease Login or Register to view this content.
Change thisPlease Login or Register to view this content.
to this for the previous monthPlease Login or Register to view this content.
If you use a fixed name, they will just be overwritten.Please Login or Register to view this content.
Location is as it shows in the code in Post #2
For Location, it is as shown previously.
[code]"C:\Some_Folder\Some_SubFolder\Some_Other_SubFolder\" & "Some_Name_Here " & MonthName(Month(Now)) & ".PDF"
Please Login or Register to view this content.
I did as you suggested. It is creating a blank PDF with nothing but the headers.
I understand that assigning a same name to all the files will result in they getting overwritten. So the sheets' name itself can be used. But how to save it to this folder : "C:\Users\user\Desktop\PDF"
pasting that into the code is starting the name of the pdfs with "PDF" and they are getting saved on the desktop when they are supposed to be saved in intended folder named "PDF"
If the files go into a Folder named "PDF" (without double quotationmarks) on the Desktop:
If the files go onto the Desktop:Please Login or Register to view this content.
Re: It is creating a blank PDF with nothing but the headers.Please Login or Register to view this content.
How is Column C formatted?
Do you have September, properly spelled, in Column C?
Attach a copy of the workbook, without changes, that does not save properly.
Last edited by jolivanes; 10-02-2021 at 01:28 PM.
I don't understand. The data of the file that has been uploaded here is taken from the original file. only the names are different.
It is working with the file I have uploaded here. i.e "TEST.xlsm" but not with the original file.
Column C is formatted as "date"
and yes all the spellings are correct as another program enters the data into the file (AutoHotkey)
I will upload a copy of the file with the names changed. Rest of the data will remain the same.
That it does not work tells us nothing
What does not work?
This works for me.
Please Login or Register to view this content.
it's creating a blank pdf with nothing but the headers.
could it be because my version of excel if different ? (2107)
2107 is just a release number. In your profile you say you have Excel 2019 - is that still correct? Or do you have MS365?
The package containing the product key has the following printed
Office Home & Student
1Pc/Mac
2019
Last edited by Fulminare; 10-03-2021 at 05:05 AM.
Thanks. Excel 2019 is correct, then. 2107 is just the realease of that product that you have installed - it is VERY unlikely to be relevant here.
If your data sheets that are to be saved in PDF format don't have September anywhere in Column C, you will end up with blank sheets.
I also noticed that you changed code that was provided, tested and worked.
All I can say from this end is that the code works on the example provided by you.
No C column has "September" in it.
I was trying to change some values to see if anything worked. But it didn't
I tested the workbook you have attached in post #21This works for me.
even that is not working.(i.e blank pdf with headers)
Is it working for you because of different excel version or something ?
If you read your Post #15 again where you will find this: So the last month's data should be printed not the current month's.
We are now in October so last month's data would be for September.
Now you say "No C column has "September" in it."
Does it surprise you that you get empty files?
Or am I missing something.
Are you saying that as there is no previous month i.e August, the PDF will be blank as there is no data of the previous month to print ?
The code does what you asked for.
If you need it different, please explain.
Okay. This is what I wanted.
The code should take the previous month's data of the columns BCD and convert it to pdf.
If I run the code on 1st of October, All the entries in the month of September should be targeted.
Even if there is October's data entered, the code should ignore that and only focus on the previous month, i.e September. (Same case if there is no October's data. Either way the month targeted should be September)
(other parameters are same. they work well with the code you already gave. My only problem is with the month selection)
See attached.
Notice that the three sheets, Barry, John and Leonard, have both data with September and October in Column C.
PDF files will be saved in a folder named "Classes" on the desktop.
If you don't have a folder with that name on your desktop, make it.
Button to export to PDF is on "Master Sheet" sheet.
In the attached, delete the code you don't want.
Please Login or Register to view this content.
Please Login or Register to view this content.
Since we don't seem to be able to agree on the month to be saved, have a look at the attached.
Takes away all the possible wrong months to be saved.
Select the month in the pop-up userform listbox.
Dear jolivanes.
Your code is working perfectly in the files you have attached. However, when I copy past the code in my actual workbook, the same case is getting repeated, blank pdf.
It is not right on my part to keep you engaged to this question any further.
First I will go through my file and try to understand why the data is not getting printed. Then I will post an update here.
I can't thank you enough for your patience and the trouble you have taken to help me out.
I will mark this topic as "SOLVED", as the solution to the main problem has been given. There seems to be something amiss with my file which has got nothing to do with your code / solution.
Thank you once again
Regards.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks