Macro code

coke1989

New Member
Joined
Nov 20, 2013
Messages
7
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:

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.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

DeBeuz

Active Member
Joined
Jun 6, 2012
Messages
448
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:
Code:
      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

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
Another way:

Code:
    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
 
Last edited:

coke1989

New Member
Joined
Nov 20, 2013
Messages
7
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

New Member
Joined
Nov 20, 2013
Messages
7
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

Active Member
Joined
Jun 6, 2012
Messages
448
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

Well-known Member
Joined
Nov 9, 2012
Messages
2,240
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,137
Messages
5,466,893
Members
406,507
Latest member
donwiss

This Week's Hot Topics

Top