Results 1 to 7 of 7

20020329, 15:40 #1
 Join Date
 Feb 2002
 Location
 US
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
paste on nth row if empty (excel 97)
I need a code for pasting the data on column A, starting from A1, then A17, A33, .... A17th. if previous entry is long than 16, then paste onto next A17th.
any idea?
thank you very much.

20020329, 16:59 #2
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: paste on nth row if empty (excel 97)
On the basis that:<UL><LI>your data is contiguous (i.e. no blank cells<LI>you won't know how large your blocks of data are until runtime<LI>you've already acrried out the first paste into Column A[/list]try something like:
ActiveCell.End(xlDown).Offset(1,0).Activate
ActiveSheet.Paste
or would you like to be a bit more specific about the source and target formats? HTHGrüße

20020329, 18:52 #3
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: paste on nth row if empty (excel 97)
Assuming that the data you paste does not include blank cells, the following should select the next cell as I think you want :<pre> If ActiveCell.Row <> 1 Then
ActiveCell.Offset(17  ActiveCell.Row Mod 17, 0).Select
End If
ActiveSheet.Paste
ActiveCell.End(xlDown).Select</pre>
This means that each block of data will occupy 1 or more 16 cell blocks of cells.
Andrew C

20020329, 20:52 #4
 Join Date
 Feb 2002
 Location
 US
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: paste on nth row if empty (excel 97)
Maybe I did not explain clearly.
I want to copy data from 5 sheets into one sheet (sumu). on this sheet I only use column A. paste the first sheet data on A1, When paste the second sheet data, I need to find next empty n*17th cell like A17 or A33 or A49...etc, depends on how long the first set data is. please see the attachment.

20020329, 23:17 #5
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: paste on nth row if empty (excel 97)
Andrew understood you more clearly than I did! <img src=/S/smile.gif border=0 alt=smile width=15 height=15> The code (slightly modified from what Andrew posted) is as follows:<pre>Sub CopyOut()
Sheets("Sheet1").Activate
'this is in case Cell A1 in SUMU is not selected.
Range(("A1"), Range("A1").End(xlDown)).Copy
Sheets("SUMU").Activate
Range("A1").Activate
ActiveSheet.Paste
ActiveCell.End(xlDown).Activate
Sheets("Sheet2").Activate
PasteIn
Sheets("Sheet3").Activate
PasteIn
Sheets("Sheet4").Activate
PasteIn
Sheets("Sheet5").Activate
PasteIn
End Sub
Sub PasteIn()
Range(("A1"), Range("A1").End(xlDown)).Copy
Sheets("SUMU").Activate
If ActiveCell.Row <> 1 Then
ActiveCell.Offset(17  ActiveCell.Row Mod 16, 0).Activate
End If
ActiveSheet.Paste
ActiveCell.End(xlDown).Activate
End Sub
</pre>
You simply need to run CopyOut. The other Sub is simply to reduce repetition. Please post back if you have any queries.Grüße

20020329, 23:36 #6
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: paste on nth row if empty (excel 97)
Here is an adaption of teh code from above. It seems to work the way you want.<pre>Sub PasteToEveryNth()
Dim ws As Worksheet
Sheets(1).[A1].Select
For Each ws In ThisWorkbook.Sheets
If ws.Index <> 1 Then
ws.Range(ws.[A1], ws.[A1].End(xlDown)).Copy
If ActiveCell.Row <> 1 Then
ActiveCell.Offset(17  ActiveCell.Row Mod 16, 0).Select
End If
ActiveSheet.Paste
ActiveCell.End(xlDown).Select
End If
Next
End Sub</pre>
Andrew C

20020330, 05:13 #7
 Join Date
 Feb 2002
 Location
 US
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: paste on nth row if empty (excel 97)
They are wonderful solutions.
thank you all your help.