Excel Membership Data to Word Document

[Beaglebud]

Background: I have a membership roster in Excel with a requirement to extract member names who have reached various years of membership in 5-year groups and print in our newsletter. These groups range from 5-65 years. I have VBA code that creates an excel sheet and lists the members in these groups. The number of rows in the year groups vary each month with some groups being blank with no members.

To Date: I have been manually eliminating blank cells, rows, and columns from the extracted member sheet each month, rearranging year groups, then copying and pasting to word document. The word document usually requires further formatting to display in newsletter properly. This is a very cumbersome and time consuming task.

Requirement: I need VBA code to copy the non blank cells by year group and write to a word document. I have the code working to create word document. The code creates a two column document with the thought of listing 5-year to 65-year (if there are members) in order by columns, but not a requirement if better way to do it. I am stuck on the code to copy from excel and write to word document in the year groups without blank cells.

Any help or suggestions greatly appreciated.

 

[etaf]

i know this is not a vba solution - but on the new sheet in excel - can you use filter() and sort() which is available in version 2021 as your profile shows
which may then create in the sheet the view you then just copy into word, without blanks
sorry , not vba, sure other members may help - but just an idea

perhaps
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Beaglebud]

Background: I have a membership roster in Excel with a requirement to extract member names who have reached various years of membership in 5-year groups and print in our newsletter. These groups range from 5-65 years. I have VBA code that creates an excel sheet and lists the members in these groups. The number of rows in the year groups vary each month with some groups being blank with no members.

To Date: I have been manually eliminating blank cells, rows, and columns from the extracted member sheet each month, rearranging year groups, then copying and pasting to word document. The word document usually requires further formatting to display in newsletter properly. This is a very cumbersome and time consuming task.

Requirement: I need VBA code to copy the non blank cells by year group and write to a word document. I have the code working to create word document. The code creates a two column document with the thought of listing 5-year to 65-year (if there are members) in order by columns, but not a requirement if better way to do it. I am stuck on the code to copy from excel and write to word document in the year groups without blank cells. Screen shot of the results of extracting names into year groups.

Any help or suggestions greatly appreciated.

 

[Beaglebud]

i know this is not a vba solution - but on the new sheet in excel - can you use filter() and sort() which is available in version 2021 as your profile shows
which may then create in the sheet the view you then just copy into word, without blanks
sorry , not vba, sure other members may help - but just an idea

perhaps
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

Thank you sir. I uploaded a reduced size screen image of the process showing the year groups after extracting from the membership roster. These year groups change each month.

 

[etaf]

thanks for that info
in each section , are there enough rows to show all possible results
I'm just aware that if FILTER() is used - then if there are not enough rows to show - then you get a SPILL error
however it would be worth seeing the data source please

 

[Beaglebud]

thanks for that info
in each section , are there enough rows to show all possible results
I'm just aware that if FILTER() is used - then if there are not enough rows to show - then you get a SPILL error
however it would be worth seeing the data source please

Yes there is enough rows in each section to hold the varying numbers in the year groups. I have uploaded a small sample of the data source. There are 249 rows like the ones shown, however the last column (published years) changes based on their approval date. The code I have written now, reads through the source sheet and compares the days of service to the number of years in 5, 10, 15 etc. days. If their days of service changes, it writes their name to a different year group for publication. If there is already a year published and the days of service have not increased to the next year group, the code ignores them, so the number in each year group can be zero or more. Hope this makes sense.

 

[etaf]

how about using
=FILTER($A$2:$A$17,$G$2:$G$17=TEXTBEFORE(O1,"_")*1)
which uses the published years

i dont follow how this is working or what is required

If you need VBA - sorry i do not provide VBA solutions here

difficult to follow these images

a very rough mockup on a single sheet

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	first name	last name					published years				5_yrs		10_yrs		15_yrs
2	A1						5				A1		A3		A5
3	A2						5				A2		A4		A6
4	A3						10				A14		A15		A7
5	A4						10								A16
6	A5						15
7	A6						15
8	A7						15
9	A8						20
10	A9						20
11	A10						20
12	A11						20
13	A12						20
14	A13						20
15	A14						5
16	A15						10
17	A16						15
Sheet1

Cell Formulas
Range	Formula
K2:K4,O2:O5,M2:M4	K2	=FILTER($A$2:$A$17,$G$2:$G$17=TEXTBEFORE(K1,"_")*1)
Dynamic array formulas.

 

[Beaglebud]

Sorry for any confusion or lack of clarity. You say you don’t understand what is required or need for VBA. Here is my original question/requirement:

Requirement: I need VBA code to copy the non blank cells by year group and write to a word document. I have the code working to create word document. The code creates a two column document with the thought of listing 5-year to 65-year (if there are members) in order by columns, but not a requirement if better way to do it. I am stuck on the code to copy from excel and write to word document in the year groups without blank cells.

I have already filtered the data and written to a different sheet. The requirement is to write the filtered data to a word document is some sort of organized method avoiding blanks,