SOLVED

help on VBA loop

%3CLINGO-SUB%20id%3D%22lingo-sub-2654269%22%20slang%3D%22en-US%22%3Ehelp%20on%20VBA%20loop%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2654269%22%20slang%3D%22en-US%22%3E%3CP%3ESo%2C%20I'm%20just%20floundering%20here.%26nbsp%3B%20I%20am%20new%20to%20all%20of%20this%20but%20i%20need%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERight%20now%2C%20I%20just%20want%20this%20to%20loop%20through%20a%20table%20and%20print%20the%20sheet%20for%20each%20person%20in%20the%20table.%26nbsp%3B%20We%20want%20to%20print%20out%20a%20whole%20month%20in%20advance%2C%20so%20it%20needs%20to%20know%20how%20many%20times%20to%20print%20the%20sheet%20for%20each%20name.%20(4%20or%205)%20There%20is%20a%20lot%20I'm%20not%20understanding%20but%20this%20is%20what%20I%20have%20so%20far.%26nbsp%3B%20any%20direction%20would%20be%20appreciated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20PrintEntireJDT()%3CBR%20%2F%3E%3CFONT%20color%3D%22%23339966%22%3E'%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23339966%22%3E'%20PrintSheet%20for%20each%20individual%20in%20JDT%20Macro%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23339966%22%3E'%20Dim%20Variables%3C%2FFONT%3E%3CBR%20%2F%3EDim%20MonthYear%20As%20Date%20%3CFONT%20color%3D%22%23339966%22%3E'This%20is%20the%20value%20of%20MonthYear%20from%20cell%20R3%20of%20Sheet%20Weekly%20Notes%20Master%3C%2FFONT%3E%3CBR%20%2F%3EDim%20WeekOfStart%20As%20Date%3CFONT%20color%3D%22%23339966%22%3E%20'This%20is%20the%20value%20of%20WeekOf%20from%20cell%20L3%20of%20Sheet%20Weekly%20Notes%20Master%3C%2FFONT%3E%3CBR%20%2F%3EDim%20WeekOf%20As%20Date%3CBR%20%2F%3EDim%20IndName%20As%20String%20%3CFONT%20color%3D%22%23339966%22%3E'This%20will%20be%20FirstName%20and%20LastName%20combined%20from%20Table%20IndData%20on%20sheet%20Names%3C%2FFONT%3E%3CBR%20%2F%3EDim%20NumberOfWeeks%20As%20Byte%3CBR%20%2F%3E%3CFONT%20color%3D%22%23339966%22%3E'This%20is%20how%20many%20weeks%20are%20in%20the%20month.%20Default%20is%205%20This%20sets%20the%20number%20for%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23339966%22%3E'how%20many%20times%20we%20loop%20for%20each%20IndName.(I'm%20thinking%20i%20set%20the%20loop%20to%20fixed%20number%2C%20but%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23339966%22%3E'i%20could%20also%20just%20check%20the%20values%20in%20an%20if%20test.%20Not%20sure%20which%20is%20the%20best%20way%3F)%3C%2FFONT%3E%3CBR%20%2F%3EDim%20Counter%20As%20Byte%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23339966%22%3E'this%20is%20where%20I%20get%20extra%20confused%20and%20start%20to%20beat%20my%20head%20against%20the%20wall%20because%20I%20am%20stupid%20and%20dont%26nbsp%3B%3C%2FFONT%3E%3CFONT%20color%3D%22%23339966%22%3Eunderstand%20why%20I%20cant%20get%20the%20syntax%20or%20the%20concept%20straight.%20I%20want%20to%20look%20up%20the%20data%20from%20Table%20IndData%20on%20Sheet%20Names.%26nbsp%3B%3C%2FFONT%3E%3CFONT%20color%3D%22%23339966%22%3EI%20am%20not%20sure%20what%20Variables%20i%20need%20for%20this.%20i%20think%20its%20something%20like%20this%3A%3C%2FFONT%3E%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20IndData%20As%20Range%20%3CFONT%20color%3D%22%23339966%22%3E'%20i%20just%20dont%20get%20this%20part.%20range%3F%20list%20object%3F%20should%20it%20be%20called%20something%20other%20than%20the%20table%20name%3F%20Why%20can't%20I%20just%20use%20the%20table%20name%3F%20why%20do%20I%20need%20to%20declare%20it%20I%20guess%20is%20my%20question.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20Row%20In%20IndData%3CBR%20%2F%3E%3CBR%20%2F%3E'%3CFONT%20color%3D%22%23339966%22%3EGet%20the%20first%20and%20last%20name%20from%20IndData%20and%20puts%20value%20in%20IndName%20(L1)%20of%20Weekly%20Notes%20Master%3C%2FFONT%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23339966%22%3E'Sets%20Counter%20to%201%3C%2FFONT%3E%3CBR%20%2F%3ECounter%20%3D%201%3CBR%20%2F%3E%3CBR%20%2F%3EDo%20While%20Counter%20%26lt%3B%3D%20NumberOfWeeks%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23339966%22%3E'Prints%20Sheet%20and%20increases%20Counter%20and%20WeekOf%3C%2FFONT%3E%3CBR%20%2F%3EActiveWindow.SelectedSheets.PrintOut%20Copies%3A%3D1%3CBR%20%2F%3EWeekOf%20%3D%20WeekOf%20%2B%207%3CBR%20%2F%3ECounter%20%3D%20Counter%20%2B%201%3CBR%20%2F%3ELoop%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23339966%22%3E'set%20WeekOf%20back%20to%20beginning%20of%20month%3C%2FFONT%3E%3CBR%20%2F%3EWeekOf%20%3D%20WeekOfStart%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20Row%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2654269%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2656431%22%20slang%3D%22en-US%22%3ERe%3A%20help%20on%20VBA%20loop%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2656431%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F848215%22%20target%3D%22_blank%22%3E%40Stretch93514%3C%2FA%3E%26nbsp%3BPerhaps%20this%20gets%20you%20started%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 Sub

2 Replies
best response confirmed by Stretch93514 (Occasional Contributor)
Solution

@Stretch93514 Perhaps this gets you started?

Thanks for the jumpstart. It’s working perfect.