Macro code

[coke1989]

I am working with a macro.
It puts in data on a Sheet with Selection Criteria, and the report runs. It then prints the sheet with the results for that criteria.
It then goes to the next Product ID and does the same thing, over and over till the end.
The problem is that all of this must be hardcoded. It doens't have any logic built in to grab the criterial automatically, which is what I am trying to do.
Oh, BTW, I am new to macros.
Here is a sample of the code:

Range("B7").Select
    ActiveCell.FormulaR1C1 = "'912"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "007670-0621"
    Range("B5").Select
    Sheets("PRODUCTION REPORT").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Selection Criteria").Select
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "007780-0621"
    Range("B5").Select
    Sheets("PRODUCTION REPORT").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Selection Criteria").Select
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "007780-0928"
    Range("B5").Select
    Sheets("PRODUCTION REPORT").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False

I understand what it is doing, but I want to know if I can make it easier to maintain by either:

1) Querying a table and finding the Product Id's that meet the given criteria, Like Cost Center. It then populates the ""Selection Criteria" screen, one item at a time while printing out the "PRODUCTION REPORT" between different Product ID's.
-or-
2) Look at a list of Products, which is generated from a Pivot Table, and run each item in the list for each individual Cost Center.

I hope this is clear and makes sense.

 

[DeBeuz]

Hi,

If I read your code correctly, I suggest you out the resulting Product ID's on a saparate sheet (let's say 'List') from A1 downwards.
You could then use someting like to replace your code from the 3rd line on:

      Lrow = 1
      While Sheets("List").Cells(Lrow,"A").Value <> ""
          Sheets("Selection Criteria").Select
          Range("B4").Value = Sheets("List").Cells(Lrow,"A").Value
          Range("B5").Select 'Not sure you need this!
          Sheets("PRODUCTION REPORT").Select 
          ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
          Lrow = Lrow +1
      Wend

 

[shg]

Another way:

    Dim v As Variant

    Worksheets("Selection Criteria").Range("B7").Value = "'912"

    For Each v In Array("007670-0621", "007780-0621", "007780-0928")
        Worksheets("Selection Criteria").Range("B4").Value = v
        Worksheets("PRODUCTION REPORT").PrintOut
    Next v

 

[coke1989]

It appears the downside to this is you still have to manually update the code. Can this be done automatically by somehow making the Array so a query search or look at a list?

 

[coke1989]

As I am new to macros and code, I will have to play with this to figure out exactly what is going on. It appears that line 4 is getting the "List" and Lrow is telling what row on which to start. Then Lrow adds one to itself and goes back to the list. Should the "List" be a named range?

 

[DeBeuz]

Hi,

No, List is the name of the extra sheet I added and contains the result of the query, starting with the first value in cell A1.
The second value in A2 and so on.
If your query result sheet has another name, you should replace the word List between the quotes with your sheet name.
If your results do not start at row 1, but for example at row 5, you should set Lrow to 5 above the loop.
The incrementing of Lrow loops over all results in column A on sheet List untill the list is depleted.

Hope this clarifies things for you.
If not, please drop another line.

Succes,

Paul

 

[Teeroy]

I think you could pull the array of Product IDs from your spreadsheet then use this array in the same manner as @shg has given, but we'd need to see some representative data to give you some code to match your format.