Excel macro loops

Excel macro loops

Didn't find your answer?

Can anybody supply a VBA macro to print from Excel a series of ranges?

The spreadsheet is a Price List, which is customer specific. The prices are =VLOOKUP formulae, dependant on the Customer.

With over 100 customers, I need a macro to insert the next customer from the list into the Price List and then Print. Then it must loop/repeat until all the customers price lists are printed.

Any ideas?
Robert Goldwater

Replies (1)

Please login or register to join the discussion.

avatar
By listerramjet
22nd Apr 2005 07:18

here is
assuming that toplist is the top of the list of customer references held in sheet2, and custlkup is the cell that holds the customer reference on sheet1 used in the vlookups, and assuming that sheet1 is the pricelist and is ready to print.

Sub printCustList()

Dim TopList As String
Dim WtaBList As String
Dim PrnTab As String
Dim CustLKup As String

TopList = "B4"
CustLKup = "a1"
WtaBList = "sheet2"
PrnTab = "sheet1"

Worksheets.WtaBList.Activate
Range(TopList).Activate

Do While avtivecell <> ""
Worksheets(PrnTab).Range(CustLKup) = Worksheets(WtaBList).ActiveCell
Worksheets(PrnTab).Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Worksheets(WtaBList).Activate
ActiveCell.Offset(1, 0).Activate
Loop

End Sub

Thanks (0)