This script copies the first 14 entires from column A to sheet 2, then prints
sheet 2 then it does the same again but with the next 14

What I want it to do is copy the first 14 into dfferntet cells rather than
the same

Sub Print14()

Dim PrintRng As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

Set PrintRng = ws2.Range("a2")

ws2.PageSetup.PrintArea = "a2:a15"

With ws1
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow Step 14
.Range("a" & i).Resize(14, 1).Copy PrintRng
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i
End With

End Sub


any ideas

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1

Do you mean the second 14 rather than the first?

Sub Print14()
Dim PrintRng As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim iTarget As Long

Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

iTarget = 1
With ws1
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow Step 14
Set PrintRng = ws2.Cells(2, iTarget)
ws2.PageSetup.PrintArea = PrintRng.Resize(14).Address
.Range("a" & i).Resize(14, 1).Copy PrintRng
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
iTarget = iTarget + 1
Next i
End With

End Sub





--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Crowbar via OfficeKB.com" <u15117@uwe wrote in message
news:5a0baa33ddf7d@uwe...
This script copies the first 14 entires from column A to sheet 2, then
prints
sheet 2 then it does the same again but with the next 14

What I want it to do is copy the first 14 into dfferntet cells rather than
the same

Sub Print14()

Dim PrintRng As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

Set PrintRng = ws2.Range("a2")

ws2.PageSetup.PrintArea = "a2:a15"

With ws1
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow Step 14
.Range("a" & i).Resize(14, 1).Copy PrintRng
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i
End With

End Sub


any ideas

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1

Bob Phillips wrote:
Do you mean the second 14 rather than the first?


Hi Bob

Sheet 2 is a template for warehouse location labels (14 per page), label 1
might be cell A2 and label 2 might be cell B3 etc

You enter the labels required in column A on sheet 1

The program loops through sheet 1's coulmn A pasting the first fourteen
entries into the require cells on sheet 2 and then prints

Then it does the next fourteen etc

At the moment the script I have doesn't do this

Do you know what I mean?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1

v = Array("A2","B3","A5","B6", . . . remaining addresses)
For i = 2 To lastrow Step 14
set rng1 = .Range("a" & i).Resize(14, 1)
j = lbound(v)
for each cell in rng1
ws2.Range(v(j)).Value = cell.Value
j = j + 1
next
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i

adjust your printarea command to accomodate the labels.

--
Regards,
Tom Ogilvy


"Crowbar via OfficeKB.com" <u15117@uwe wrote in message
news:5a0c832301135@uwe...
Bob Phillips wrote:
Do you mean the second 14 rather than the first?


Hi Bob

Sheet 2 is a template for warehouse location labels (14 per page), label 1
might be cell A2 and label 2 might be cell B3 etc

You enter the labels required in column A on sheet 1

The program loops through sheet 1's coulmn A pasting the first fourteen
entries into the require cells on sheet 2 and then prints

Then it does the next fourteen etc

At the moment the script I have doesn't do this

Do you know what I mean?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1