create loop in macro for a list

[VAMS]

Howdy. My current macro generates a report for a salesperson (let's say,
"Smith, John") by going into each worksheet (there are about 6 - some with 2
pivot tables each) and selecting their name from the Pivot Field drop-down
list. What I have been doing is then going into VB and editting my macro
manually by replacing that saleperson's name with the next one (so "Smith,
John" becomes "Doe, Mary") and re-running the macro. So, I run the macro,
edit by replacing the name - until I've run through all 10 names. There's
got to be a better way? Thank you.

 

[MacGuy]

Quick and dirty:

Sub do_nothing()

x = 1
salesName = Cells(x, 1).Value
Do While salesName <> ""
salesName = Cells(x, 1).Value
If salesName <> "" Then _
MsgBox salesName '### put your code in place of msgbox ###
x = x + 1
Loop

End Sub

Given you have the names in a continuous list in a single column. The
Cells(r,c) is Cells(rowIndex, columnIndex) so x as 1 is row 1 which
increments each loop and colIndex of 1 is column A. There are other ways to
do this but as I said quick and dirty.
HTH...

 

[JLGWhiz]

There are several options. One is to use a cell on the sheet to enter a name
and then use that range reference in the macro. Frinstance: Instead of
"Smith, John", you would have Range("A1").Value without the outside quote
marks.

Another way is to use the InputBox.

myVar = InputBox("Enter a name", "Salesperson")
Set c = Sheets("Sales").Find(myVar, LookIn:=xlValues)

Since you didn't post your code, only this sample code is provided.

 

[VAMS]

Thank you. I'm obviously a novice. I hope it's okay that I post a subset of
my current macro, and then follow it with a section of my list file.
Appreciate your help so much.

Sub Q3SalesRpts()
' Q3SalesRpts Macro
' Q3 RSM Reports
' Keyboard Shortcut: Ctrl+Shift+Q
ActiveSheet.PivotTables("PivotTable1").PivotFields("SP").CurrentPage = _
"Yost, Christopher"
Range("B15").Select
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel,
True
Selection.Copy
Windows("dddddddd.xls").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("B:M").Select
Selection.Columns.AutoFit
Sheets("Cust $").Select
Windows("Item Customer Summary Data 2008 Q3.xls").Activate
Sheets("by customer").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("SP").CurrentPage = _
"Yost, Christopher"
Range("D25").Select
'my macro goes on & on…
ChDir "S:\Finance\Sales Support\FY08\Q3 FY08\Apr Sales Reports"
ActiveWorkbook.SaveAs Filename:= _
"S:\Finance\Sales Support\FY08\Q3 FY08\May Sales Reports\May 08
Sales - Yost, Christopher.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub


Than, in a separate file "list.xls", I have the salespeople listed:
Cell A1 Yost, Christopher
Cell A2 Baker, Ann
Cell A3 William, Phil

....and so forth through Cell A11