Copy the 14 cells and transfer them to another sheet loop

Hi Experts

On worksheet 1 i have in Column A about a 1000 entries

What I want to do is to transfer the first 14 onto sheet 2 and then print

Then I want it to transfer the next 14 and then print

I want it to loop through this process until it has printed all 1000

Can anyone help me?

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

Hi,

Something along these lines:

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

HTH

"Crowbar via OfficeKB.com" wrote:

> Hi Experts
>
> On worksheet 1 i have in Column A about a 1000 entries
>
> What I want to do is to transfer the first 14 onto sheet 2 and then print
>
> Then I want it to transfer the next 14 and then print
>
> I want it to loop through this process until it has printed all 1000
>
> Can anyone help me?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200601/1
>

Hi Toppers

thanks for the help, just looking at this script with confusion as Im new to
this

is this the part that copies the 14 selected items to page 2?

.Range("a" & i).Resize(14, 1).Copy PrintRng

How can this be changed so that the first number in the selection is pasted
into cell A1 on sheet 2 and second into B2 and so on




Toppers wrote:
>Hi,
>
> Something along these lines:
>
>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
>
>HTH
>
>> Hi Experts
>>
>[quoted text clipped - 7 lines]
>>
>> Can anyone help me?

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

Hi,
I read your reply as wanting to tranpose to columns: this will put
numbers 1-14 in row 1, columns 1 to 14, numbers 15-28 in row2 , columns 1 to
14 etc

(although you said A1, then B2 .... a typo?)

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"
ws1.Activate

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.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Set PrintRng = PrintRng.Offset(1, 0) ' move to next row
Next i
End With

End Sub


"Crowbar via OfficeKB.com" wrote:

> Hi Toppers
>
> thanks for the help, just looking at this script with confusion as Im new to
> this
>
> is this the part that copies the 14 selected items to page 2?
>
> .Range("a" & i).Resize(14, 1).Copy PrintRng
>
> How can this be changed so that the first number in the selection is pasted
> into cell A1 on sheet 2 and second into B2 and so on
>
>
>
>
> Toppers wrote:
> >Hi,
> >
> > Something along these lines:
> >
> >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
> >
> >HTH
> >
> >> Hi Experts
> >>
> >[quoted text clipped - 7 lines]
> >>
> >> Can anyone help me?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200601/1
>

Hi again.

remove

Set PrintRng = PrintRng.Offset(1, 0)

as you don't want to move down the rows on the output sheet.

"Toppers" wrote:

> Hi,
> I read your reply as wanting to tranpose to columns: this will put
> numbers 1-14 in row 1, columns 1 to 14, numbers 15-28 in row2 , columns 1 to
> 14 etc
>
> (although you said A1, then B2 .... a typo?)
>
> 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"
> ws1.Activate
>
> 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.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
> SkipBlanks:=False, Transpose:=True
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
> Set PrintRng = PrintRng.Offset(1, 0) ' move to next row
> Next i
> End With
>
> End Sub
>
>
> "Crowbar via OfficeKB.com" wrote:
>
> > Hi Toppers
> >
> > thanks for the help, just looking at this script with confusion as Im new to
> > this
> >
> > is this the part that copies the 14 selected items to page 2?
> >
> > .Range("a" & i).Resize(14, 1).Copy PrintRng
> >
> > How can this be changed so that the first number in the selection is pasted
> > into cell A1 on sheet 2 and second into B2 and so on
> >
> >
> >
> >
> > Toppers wrote:
> > >Hi,
> > >
> > > Something along these lines:
> > >
> > >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
> > >
> > >HTH
> > >
> > >> Hi Experts
> > >>
> > >[quoted text clipped - 7 lines]
> > >>
> > >> Can anyone help me?
> >
> > --
> > Message posted via OfficeKB.com
> > http://www.officekb.com/Uwe/Forums.a...mming/200601/1
> >

Hi Crewbar
The sub is doing what you want.

try it out and see what`s happend (Step by step) Fill columns "A" with
123.......

Regards
Yngve