Issues with Macro to create individual pdf files for each item on item validation list

**maldo81** · 06-10-2021, 10:12 AM

I have a dashboard that I need to export to pdf for each data validation item on the list. I have some code that does this but I am having some issues with it. I wrote an xlookup so that each loop through the data validation list it will populate the file name in a different cell. I then want to use that cell when I write the file name. The m= tells it that's where the data validation list is at, while I thought the c.Value that is used for sName to write the file name. When I change that from E3(where data validation list is) to Q3(where the xlookup to get the file name I want to use) and I run it again, it takes the value in cell Q3 and rewrites it with what should be in cell E3 and the value in E3 stays the same so it ends up saving the same data over and over with different names. I want it to parse through each individual name on the list in E3 and save it using the value that is returned in the xlookup in cell Q3

Please Login or Register  to view this content.

**Vraag en antwoord** · 06-10-2021, 10:20 AM

Please post an example file. See the yellow banner at the top of this site

**maldo81** · 06-10-2021, 10:45 AM

I apologize, I hope I added the attachment correctly.

**Vraag en antwoord** · 06-10-2021, 11:08 AM

Merged cells and especially in combination with VBA is never a good idea. Maybe you mean this?

Please Login or Register  to view this content.

**maldo81** · 06-10-2021, 11:45 AM

The pdf files have to be sent to different providers and they use a program that automatically catches the files for each individual and sends them their files. The format that each of those files has to follow is
RVU_Bonus_Report_
Previous Month as yyyymm
_
the provider name as listed in column B of the File names sheet

so RVU_Bonus_Report_PreviousMonth_ColumnBProvName

The provider name has to be as column B because they use just last name if there are no other doctors with that last name, and if there are other doctors with the same last name then last name first two letters of first name. The problem is there are hundreds of doctors with and only some of them get a bonus so those last names have to be written as in column b.

The reason for the merge was so that I could catch the static portion (RVU_Bonus_Report_), the previous month (which changes every month) and the static Provider name using their full names.

I was then trying to do an XLookup on that merge based on the provider name and put that in cell Q3 so that I could use that as the file name.

Both of those codes work great at creating a pdf for each individual provider, but they both use the full provider name to create the file name.

What I need is for the file name when saving to show up as:

RVU_Bonus_Report_202105_Aly

The month to use the previous month so I can run the report each month

And to use the shorter version of the providers name that is used in the file names sheet

**Vraag en antwoord** · 06-10-2021, 11:52 AM

Long story. You can still easily change the file name here:

Please Login or Register  to view this content.

**maldo81** · 06-10-2021, 12:44 PM

Sorry I finally got what you mean logically. Basically it was to do the cell merge in the VBA code where you pointed out, in case anyone works with this I copy pasted how I wanted the names from the File Names sheet into the Providers sheet and under the ExportAsFixedFormat I told it to find the name under column 2 instead of column 1 where the regular names are at. Then I defined the last month as lm below the ar definition and referenced that as well under the ExportAsFixedFormat line. The resulting code is below.

Thank you Vraag en antwoord for your help! I am gonna mark the thread as solved. I am new to this forum so I will look to see if there is a place to leave you a good review or something that gives you credit for helping solve the issue.

Please Login or Register  to view this content.

**Vraag en antwoord** · 06-10-2021, 02:58 PM

Thanks for the feedback. There is an add reputation button. But that doesn't bother me.

Issues with Macro to create individual pdf files for each item on item validation list

LinkBack

Thread Tools

Rate This Thread

Display

Issues with Macro to create individual pdf files for each item on item validation list

Re: Issues with Macro to create individual pdf files for each item on item validation list

Re: Issues with Macro to create individual pdf files for each item on item validation list

Re: Issues with Macro to create individual pdf files for each item on item validation list

Re: Issues with Macro to create individual pdf files for each item on item validation list

Re: Issues with Macro to create individual pdf files for each item on item validation list

Re: Issues with Macro to create individual pdf files for each item on item validation list

Re: Issues with Macro to create individual pdf files for each item on item validation list

Thread Information

Users Browsing this Thread

Similar Threads

Multipul What if Formul. B=20 C=List Item 2 +B=22 C=List Item 2 (Deeper Data Dive/Table)

Macro that runs through and enters each item in a Data Validation List

[SOLVED] Running A Macro When Any Validation List Item is Selected

macro to select each item in data validation list

Macro to select an item from a data validation list

Macro to select an item in a Validation List

Create new sheet for each individual item

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions