User Profile
Stretch93514
Copper Contributor
Joined 6 years ago
User Widgets
Recent Discussions
- 1.1KViews0likes0Comments
help on VBA loop
So, I'm just floundering here. I am new to all of this but i need help. Right now, I just want this to loop through a table and print the sheet for each person in the table. We want to print out a whole month in advance, so it needs to know how many times to print the sheet for each name. (4 or 5) There is a lot I'm not understanding but this is what I have so far. any direction would be appreciated Sub PrintEntireJDT() ' ' PrintSheet for each individual in JDT Macro ' Dim Variables Dim MonthYear As Date 'This is the value of MonthYear from cell R3 of Sheet Weekly Notes Master Dim WeekOfStart As Date 'This is the value of WeekOf from cell L3 of Sheet Weekly Notes Master Dim WeekOf As Date Dim IndName As String 'This will be FirstName and LastName combined from Table IndData on sheet Names Dim NumberOfWeeks As Byte 'This is how many weeks are in the month. Default is 5 This sets the number for 'how many times we loop for each IndName.(I'm thinking i set the loop to fixed number, but 'i could also just check the values in an if test. Not sure which is the best way?) Dim Counter As Byte 'this is where I get extra confused and start to beat my head against the wall because I am stupid and dont understand why I cant get the syntax or the concept straight. I want to look up the data from Table IndData on Sheet Names. I am not sure what Variables i need for this. i think its something like this: Dim IndData As Range ' i just dont get this part. range? list object? should it be called something other than the table name? Why can't I just use the table name? why do I need to declare it I guess is my question. For Each Row In IndData 'Get the first and last name from IndData and puts value in IndName (L1) of Weekly Notes Master 'Sets Counter to 1 Counter = 1 Do While Counter <= NumberOfWeeks 'Prints Sheet and increases Counter and WeekOf ActiveWindow.SelectedSheets.PrintOut Copies:=1 WeekOf = WeekOf + 7 Counter = Counter + 1 Loop 'set WeekOf back to beginning of month WeekOf = WeekOfStart Next Row End SubSolved1.2KViews0likes2CommentsConditional Cells based on Gender and Using forms to create sheets?
I am trying to design a spreadsheet to help our medical coordinator keep track of when the last annual appointment was for the individuals we serve. I had a few ideas and i wasn't sure if they're possible or even how to do them. So here I go, any help would be much appreciated. I thought about using Forms so she could enter the data via her laptop or phone. i would need it to do the 2 following things. when initially setting up, create a new sheet for unique individuals or put data in a specific sheet. after initial set up, overwrite previous data with new updated information. for example if the last annual physical was July 2, 2020 and the individual gets another physical on July 13, 2021 and she fills out the form can it replace the July 2, 2021 date with the new date? Just a thought but not crucial... Under the specialty Procedures area are things that are gender specific. I thought it might be nice if, only the ones for a specific gender showed. Every 3 months, we need to send a report showing this information for each individual. I just was curious as to the best way to approach this? I am totally new to this idea. I will be watching videos on this as well I would also like for it to automatically let her know when an individuals annual appointments are approaching. say 2 months before for standard, but 3 months before for specialty. Right now I am envisioning Using a separate sheet for each individual. Might it be better to have 1 sheet with the individuals names on the left and then columns or tables for the different appointment types and dates. I am trying to convince her that this will be a better more convenient option than a paper version. Our company is too small(cheap) to provide Microsoft 365 for business for us. I am using my Personal Paid version and I believe she has a student version that she uses, if that makes any difference. thanks for taking a look at this.1.1KViews0likes2Commentsdates and auto populate data
I am trying to do a couple of things with these documents. Both are Excel versions of paperwork that are filled out by hand. Both are weekly forms but get turned in monthly. We have been having problems with staff not filling out the correct information i.e. dates and identifying information. I want to fill out as much as possible for them. In the Weekly Notes file, I have been able to force it to do the dates but i wonder if there isn't a better way, plus with my way I have 10 pages (for 5 weeks) and if there are only 4 weeks in the month I would have an extra page printing for every instance (Person or Place). So, here are my questions and/or request for how to do things. Is there a better, more elegant way to make the days and dates auto populate. Is there a way to just put in the month and have the first day of the month determined without needing to enter the date to begin the count from? (Like Weekly Notes file) Right now the Staff Daily Duties form has 2 pages that would cover the 1st week of the month. Can it be configured to copy? the pages the correct amount of times for the whole month and fill for the number of weeks and stop at 4 or 5 weeks as needed. I have been told that in order for the identifying info to be populated I would need to use mail merge. If I create a word version of these documents, can I pass the day and date info at the same time and the conditional formatting. (Greying out dates not in the month) I would appreciate any help with this.675Views1like0CommentsRe: Printing a document with data from a table.
This was great Information. But, I am still not seeing how to achierve my goal of printing 1 weekly master notes for each and every individual on the names sheet. I havent actually tried to print it. I have just been using the print preview. Would that make a difference. Do i need to create a Word Document and use mail merge? I really thought doing it in Excel only would be simpler.2KViews0likes3CommentsPrinting a document with data from a table.
I know this should be simple, but it's escaping me right now. In the attached file are 2 sheets. i want to be able to print the Master sheet with the data from the Names sheet/table. I really don't understand why I am having so much trouble grasping this, but any help would be appreciated. I would also like a link to the appropriate help topic. Thanks in advance.2.2KViews0likes7Commentstoggle controls to create pivot table?
I need help understanding controls in excel 365. I have an idea for creating pivot tables to use for mail merge with word and excel paperwork. I have a bunch of individuals who live in different houses (Group Homes). Sadly our staff seem incapable of correctly writing names of individuals or the house name on the paperwork they need to fill out, so now I guess I need to do it for them. I want to be able to have a column/field that is just a check mark or toggle in each cell that is selected if the individual needs that form. (like a blood sugar record for diabetics) so then I can have a pivot table that is only for diabetic monitoring that I can then use with word to mail merge. I see the add controls from developer tab, but it seems like a clunky way to insert the control into the cell so I'm wondering if there is a better way to do this. I have around 75 different forms that are used for the 37 individuals or the 8 houses we have. I would like to make setting this up as painless as possible and then the running/printing as efficient too. So Ideally, what i want is to be able to pick house 1 , have a table created that gives me all the individuals who live in that house and what documentation requirements each individual has. then I run a macro? that does the mail merge for all the forms that are checked and print them by house so I don't spend all day trying to sort every form. If it will help i can show a file that kind of shows what i am trying or at least the column names. I hopoe ive explained this well enough, and I hope some of you brilliant minds can help me solve this.1.2KViews0likes2Comments
Recent Blog Articles
No content to show