Repeat form printing with new data by macro
 

Hi,

Slightly more complex one this time.......

I have a workbook with 4 worksheets titled as follows..

"Stage 1 - Visit diary" - relevant range E4:E58

"Stage 1 - Visit form"

"Formulas" - Relevant range P5:P59

"Customer List" - relevant range Column A

I have written a macro that does the following,

1. Copies the user selected (by filter) records from the "Customer
list" worksheet.
2. Pastes the above records into the "Formulas" worksheet and then
sorts alphabetically.
3. Copies the sorted records and pastes into the "Stage 1 - Visit
diary" worksheet.
4. Copies the first 3 records from step 3 into the "Stage 1 - Visit
form" worksheet (3 records per page) and prints it.
5. Goes back to the "Stage 1 - Visit diary" sheet and repeats step 4
with the NEXT 3 records if they exist (and ends the macro if they
dont).

Step 5 is where it is all going wrong.......Currently it only prints
the first 3 records (1 page) then finishes the macro regardless if
there are further records or not.


This is my code..

' Cut and paste filtered customers and sort alphabetically

ActiveSheet.Columns("A:A").Select
Selection.Copy
Sheets("Formulas").Select
Range("P5").Select
ActiveSheet.Paste
Range("R5:R60").Select
Rows("5:60").Select
Range("F5").Activate
Selection.Sort Key1:=Range("R5"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

' Select, sort and print page 1

Range("P5:P7").Select
Selection.Copy
Sheets("Stage 1 - Visit diary").Select
Range("E4").Select
ActiveSheet.Paste
Sheets("Stage 1 - Visit form").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

' Select, sort and print page 2

Sheets("Formulas").Select
If ("P8" < 1) Then GoTo SKIP
Range("P8:P10").Select
Selection.Copy
Sheets("Stage 1 - Visit diary").Select
Range("E4").Select
ActiveSheet.Paste
Sheets("Stage 1 - Visit form").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

' Return to customer list

SKIP: Sheets("Customer List").Select

End Sub

Help..........:confused:


--
Alec H
------------------------------------------------------------------------
Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042
View this thread: http://www.excelforum.com/showthread...hreadid=515306

Repeat form printing with new data by macro
 

Hi,

Just an update, I resolved the problem by changing 1 line of code (isnt
that always the way)


My Select, sort and print page 2 section now reads


VBA:

' Select, sort and print page 2

Sheets("Formulas").Select
If Range ("P8") < 1 Then Goto SKIP
Range("P8:P10").Select
Selection.Copy
Sheets("Stage 1 - Visit diary").Select
Range("E4").Select
ActiveSheet.Paste
Sheets("Stage 1 - Visit form").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Line 2 added range and moved the bracket.........

Alec.


--
Alec H
------------------------------------------------------------------------
Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042
View this thread: http://www.excelforum.com/showthread...hreadid=515306