Thank you GC!!!
10% Prepaid to Ozgrid
I am using a UDF to concatenate similar values based on criteria in columns in a worksheet (Review). The formula that calls the UDF is in a table on another sheet (Ratings). The table will show names in a cell based on matches in the criteria in the formula. Also, the UDF places each new value in the cell under each other similar to CHAR (10) to make it look clean. As values change based on input in columns from the Review sheet (Column Y, VCC Rating), the rows in the table do not want to expand or "Autofit" to account to more values being added or being removed from cells. Is there code that can be added to the UDF that will automatically autofit the rows of the table after values are changed in Column Y in the Review sheet. Every other column value that effects the table is static except for Column Y.
As long as it’s fits on one page we’ll be good.
Yes. I do. Will send now. Thanks!!
Perfect!! thank you!
Thank you GC!!!
I have a request is to create individual PDF files for every unique name in column A of a sheet in the attached file. I was think the macro logic would be as follows:
Worksheet = Sheet1
Header Row = Row 1 (need on each PDF report)
Data Range = A:E
Max rows per Name = 58
Look in Column A (Full Name) and for each unique name, create a PDF file with the Header along with each row of data. The folder location for the PDF files would be “C:\Users\jstriker\Documents\Alix Partners\PMO\PDF”. I need each PDF to be on a single page (since the max rows are 58 we should be good). Then after the 1st name report is created, then search for the 2nd unique name is Column A and repeat the PDF process, then repeat with names 3, 4, 5…... The attached is a test file with 2 names but the real file with have about 200 unique names. Also, could the PDF file names be customized from the Full Name value in column A and then add today’s date (John doe – 03 29 19.pdf).
The request is time sensitive and hope to have a solution ASAP. Thank you and reach out with any ?'s.
This solution is OUTSTANDING!!! Exactly what I was looking for. Thank you!!!!!!!!!!!!!!!!!
Ozgrid pre-payment sent....
I am looking for a macro that will run on 2 sheets in the same workbook (see attached) that will Find values in specific columns and and replace with user defined values located in separate tables. The 2 sheets (Board Wins_Losses - Month and Board Wins_Losses - Quarter) are identical except for the Quarter tab has more rows...columns are the same. The sheet (FindReplaceTables) has 4 individual tables that associate with 4 columns in the two Board Wins_Losses tabs. Want the macro to look in Column B of both of the Board Wins_Losses tabs and then look into the Table named "Client" and see if there is a match in the "Find" column. If there is a match, replace that value with the associated value in the "Replace" column. For example, if the text string "Corporation" is found in column B of the 2 sheets, want that to be changed to "Corp." Also, if there is a match found in the "Find" column and the associated value in the "Replace" column is blank, would like to have that text string deleted (If "Limited" or "LLC" are matched, I would like those deleted). Then I would like the macro to continue to search the next column (F) and it's associated table = Industry to Find/Replace based on the table values. When finished continue to search Column H (against Table = Reason), then finally search Column J (against Table = Competitor).
Basically want to give the user the flexibility to add and update the values to be replaces by using Tables in the FindReplaceTables tab.
Let me know if you have any ?'s. THANK YOU!
So if my table is in a sheet named 'Snapshot', the code would be ??Code
- Sub FormatTable()
- Dim lo As ListObject
- Application.ScreenUpdating = False
- Set lo = ThisWorkbook.Sheets("Snapshot").ListObjects("Table1")
- lo.DataBodyRange.Resize(1).Offset(1, 0).Copy
- lo.DataBodyRange.PasteSpecial xlPasteFormats
- Application.CutCopyMode = False
- lo.DataBodyRange.Cells(1, 1).Select
- Application.ScreenUpdating = True
- End Sub
Oh wow. I really appreciate that. Code works perfectly.
Thank you GC!!!!!
Pre-payment has been sent.
I have a table (Table1) located in the 'Snapshot' tab of the attached file. Table1 begins on row 11 (header row on row 10). I am looking for a macro that will copy the Format of the 2nd row of the table (row 12) and apply that format to the remaining rows of Table1. The reason for this is due to having another macro that imports new data into the table and when it does, some rows end have a different formatting than the existing rows (Wrapped Text, column A not bolded). I normally use the Format Painter option and manually adjust the data. Was hoping to accomplish this with a macro. Attached sample file for reference.
Thank you and let me know if you have any ?'s
oh yes...this is much better. love it. I will keep testing and if something pops up I will get back to you. Thank you again. Much appreciated!!
I ran a few tests and had a few ?'s. I have columns in the Snapshot tab Table1 table (CJ:CZ) that have formulas and after running the macro, it appears those columns are getting overwritten when the procedure runs and being pushed out of the table range. If the procedure could only paste in data up to column CH in the Snapshot Export file I think we will be there.
Thank you!!!! I will let you know if I have any ?'s. Now go get some rest!!!