Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy the 14 cells and transfer them to another sheet loop
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy the 14 cells and transfer them to another sheet loop
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy the 14 cells and transfer them to another sheet loop
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy the 14 cells and transfer them to another sheet loop
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy the 14 cells and transfer them to another sheet loop
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transfer cell value from 1 sheet to another without copy/paste? | Excel Worksheet Functions | |||
compare cells, copy, loop | Excel Worksheet Functions | |||
How to transfer text to an sheet if the cells en range contains a | Excel Discussion (Misc queries) | |||
Transfer/Copy Sheet, Dynamically linked Sheet | Excel Discussion (Misc queries) | |||
Loop/copy rows variable times to new sheet | Excel Programming |