unable to run Continuously

EDUCATED MONKEY · Jul 26, 2011

Well to restore a bit of confidance, I thought I work on another part of this project, that of putting the data back once all the formating and stuff has taken place, I have read through the site again to see if the problem i have as allready been cover, and I could not find anything apart from the copy from one worksheet to another which I have adapted to fit my needs.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

></o

>
Now the problem is that the data from the source gets transferred correctly and with no problems at all, so I can not understand why the process will not contine down the same column as more data is added, as far as I can see I have preserved the last posiotion and and moved the active cell to the end of the data just added. <o

></o

>
<o

></o

>
<o

></o

>
<o

></o

>
Sub TRANSFER2()<o

></o

>
'<o

></o

>
' TRANSFER2 Macro<o

></o

>
' Macro recorded <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com

ffice:smarttags" /><st1:date Month="7" Day="27" Year="2011">27/07/2011</st1:date> by Peter Hayward<o

></o

>
'<o

></o

>
' Keyboard Shortcut: Ctrl+Shift+E<o

></o

>
Application.ScreenUpdating = False<o

></o

>
<o

></o

>
Dim Q As Integer<o

></o

>
Dim rowdown As Integer<o

></o

>
Dim rowposition As Variant<o

></o

>
Dim TempLbel As Variant<o

></o

>
Dim lastposition As Variant<o

></o

>
<o

></o

>
lastposition = 0<o

></o

>
<o

></o

>
Worksheets("REMOVE UNWANTED ITEMS").Activate<o

></o

>
Q = Worksheets("REMOVE UNWANTED ITEMS").Range("H1").Value 'number of titles<o

></o

>
Q = Q - 1<o

></o

>
Worksheets("NEW SHEET").Select<o

></o

>
TempLbel = Range("B:B").Cells(2 + lastposition, 1).Value<o

></o

>
<o

></o

>
For rowdown = 1 + lastposition To Q + lastposition<o

></o

>
<o

></o

>
Worksheets("NEW SHEET").Cells(rowdown + 1 + lastposition, 4) = ThisWorkbook.Worksheets("REMOVE UNWANTED ITEMS").Cells(rowdown, 1)<o

></o

>
Worksheets("NEW SHEET").Cells(rowdown + 1 + lastposition, 2) = TempLbel <o

></o

>
Next rowdown<o

></o

>
Worksheets("NEW SHEET").Activate<o

></o

>
Worksheets("NEW SHEET").Range("A2").Value = Q<o

></o

>
Cells(Q + 2, 4).Select ' move to the end of the data<o

></o

>
lastposition = rowdown<o

></o

>
Worksheets("NEW SHEET").Range("A3").Value = lastposition ' test only<o

></o

>
Worksheets("NEW SHEET").Range("A4").Value = rowdown ' test only<o

></o

>
<o

></o

>
Application.ScreenUpdating = True<o

></o

>
<o

></o

>
End Sub<o

></o

>

This is the data that was transferred corectly to the destination sheet “NewSheet”<o

></o

>
<o

></o

>
<o

></o

>
<TABLE style="MARGIN: auto auto auto 5.4pt; WIDTH: 450.6pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=601><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 155.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=208 noWrap>CLAIRE ALLAN<o

></o

>

</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 294.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=393 noWrap>GAME OVER<o></o>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 155.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=208 noWrap>CLAIRE ALLAN<o

></o

>

</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 294.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=393 noWrap>LARGER THAN LIFE<o></o>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 155.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=208 noWrap>CLAIRE ALLAN<o

></o

>

</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 294.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=393 noWrap>HUSBANDS IS ONE EVER ENOUGH?<o></o>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 155.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=208 noWrap>CLAIRE ALLAN<o

></o

>

</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 294.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=393 noWrap>THE TRIBE THAT HIDES FROM MAN<o></o>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 155.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=208 noWrap>CLAIRE ALLAN<o

></o

>

</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 294.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=393 noWrap>WHEN YOU WALK<o></o>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 155.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=208 noWrap>CLAIRE ALLAN<o

></o

>

</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 294.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=393 noWrap>BIG STONE GAP (The first book in the Big Stone Gap series)<o></o>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 155.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=208 noWrap>CLAIRE ALLAN<o

></o

>

</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 294.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=393 noWrap>LUCIA LUCIA<o></o>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 155.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=208 noWrap>CLAIRE ALLAN<o

></o

>

</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 294.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=393 noWrap>STATUS ANXIETY<o></o>

</TD></TR></TBODY></TABLE>
<o

></o

>
<o

></o

>
This the data from the source sheet “REMOVE UNWANTED ITEMS”<o

></o

>
<o

></o

>
<TABLE style="MARGIN: auto auto auto 5.4pt; WIDTH: 368.8pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=492><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 368.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=492 noWrap>GAME OVER<o

></o

>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 368.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=492 noWrap>LARGER THAN LIFE<o

></o

>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 368.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=492 noWrap>HUSBANDS IS ONE EVER ENOUGH?<o

></o

>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 368.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=492 noWrap>THE TRIBE THAT HIDES FROM MAN<o

></o

>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 368.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=492 noWrap>WHEN YOU WALK<o

></o

>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 368.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=492 noWrap>BIG STONE GAP (The first book in the Big Stone Gap series)<o

></o

>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 368.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=492 noWrap>LUCIA LUCIA<o

></o

>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 368.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=492 noWrap>STATUS ANXIETY<o

></o

>

</TD></TR></TBODY></TABLE>
<o

></o

>

AlphaFrog · Jul 26, 2011

Code:

Sub TRANSFER3()
[COLOR="Green"]    '
    ' TRANSFER2 Macro
    ' Macro recorded 27/07/2011 by Peter Hayward
    '
    ' Keyboard Shortcut: Ctrl+Shift+E[/COLOR]
    
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim Q As Long, Nextrow As Long
    
    Set wsSource = Worksheets("REMOVE UNWANTED ITEMS")
    Set wsDest = Worksheets("NEW SHEET")
    
    Nextrow = wsDest.Range("B" & Rows.Count).End(xlUp).Row + 1  [COLOR="Green"]'Next empty row on wsDest[/COLOR]
    Q = wsSource.Range("H1").Value  [COLOR="Green"]'number of titles[/COLOR]
    
    Application.ScreenUpdating = False
        
    wsDest.Cells(Nextrow, "D").Resize(Q).Value = wsSource.Cells(1, "A").Resize(Q).Value
    wsDest.Cells(Nextrow, "B").Resize(Q).Value = wsDest.Range("B2").Value
    
    wsDest.Range("A2").Value = Q
    wsDest.Range("A3").Value = Nextrow     [COLOR="Green"]' test only[/COLOR]
    wsDest.Range("A4").Value = Nextrow + Q [COLOR="Green"]' test only[/COLOR]
    
    Application.ScreenUpdating = True
    
End Sub

EDUCATED MONKEY · Jul 27, 2011

Thank you for the code, sorry to say that I have been a bit remiss in that I forgot to mention the this is data needs to be place starting one row down from the active cell and push the other data down<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

> </o

>
To give an example <o

></o

>
<o

> </o

>
Author column title column<o

></o

>
<o

> </o

>
<TABLE style="MARGIN: auto auto auto 5.4pt; WIDTH: 450.6pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=601><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 155.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=208 noWrap>KATE ALLEN <o

></o

>
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 294.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=393 noWrap>BOOKS BY KATE ALLEN CHOOSE ONE<o></o>
</TD></TR></TBODY></TABLE><TABLE style="MARGIN: auto 6.75pt; WIDTH: 270.95pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-table-lspace: 9.0pt; mso-table-rspace: 9.0pt; mso-table-anchor-vertical: page; mso-table-anchor-horizontal: margin; mso-table-left: right; mso-table-top: 189.05pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=361 align=right><TBODY><TR style="HEIGHT: 13.9pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 270.95pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 13.9pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=361 noWrap>GAME OVER<o

></o

>
</TD></TR><TR style="HEIGHT: 13.9pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 270.95pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 13.9pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=361 noWrap>LARGER THAN LIFE<o

></o

>
</TD></TR><TR style="HEIGHT: 13.9pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 270.95pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 13.9pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=361 noWrap>HUSBANDS IS ONE EVER ENOUGH?<o

></o

>
</TD></TR><TR style="HEIGHT: 13.9pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 270.95pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 13.9pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=361 noWrap>THE TRIBE THAT HIDES FROM MAN<o

></o

>
</TD></TR><TR style="HEIGHT: 13.9pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 270.95pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 13.9pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=361 noWrap>WHEN YOU WALK<o

></o

>
</TD></TR><TR style="HEIGHT: 13.9pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 270.95pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 13.9pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=361 noWrap>BIG STONE GAP (The first book in the Big Stone Gap series)<o

></o

>
</TD></TR><TR style="HEIGHT: 13.9pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 270.95pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 13.9pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=361 noWrap>LUCIA LUCIA<o

></o

>
</TD></TR><TR style="HEIGHT: 13.9pt; mso-yfti-irow: 7; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 270.95pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 13.9pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=361 noWrap>STATUS ANXIETY<o

></o

>
</TD></TR></TBODY></TABLE>
<o

> </o

>
<TABLE style="MARGIN: auto auto auto 5.4pt; WIDTH: 161.15pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=215><TBODY><TR style="HEIGHT: 13.25pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 161.15pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.25pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=top width=215 noWrap>KATE ALLEN
</TD></TR><TR style="HEIGHT: 13.25pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 161.15pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.25pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=top width=215 noWrap>KATE ALLEN
</TD></TR><TR style="HEIGHT: 13.25pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 161.15pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.25pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=top width=215 noWrap>KATE ALLEN
</TD></TR><TR style="HEIGHT: 13.25pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 161.15pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.25pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=top width=215 noWrap>KATE ALLEN
</TD></TR><TR style="HEIGHT: 13.25pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 161.15pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.25pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=top width=215 noWrap>KATE ALLEN
</TD></TR><TR style="HEIGHT: 13.25pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 161.15pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.25pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=top width=215 noWrap>KATE ALLEN
</TD></TR><TR style="HEIGHT: 13.25pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 161.15pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.25pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=top width=215 noWrap>KATE ALLEN
</TD></TR><TR style="HEIGHT: 13.25pt; mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 161.15pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.25pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=top width=215 noWrap>KATE ALLEN
</TD></TR><TR style="HEIGHT: 13.25pt; mso-yfti-irow: 8; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 161.15pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.25pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=top width=215 noWrap>KATE ALLEN
</TD></TR></TBODY></TABLE>
<o

> </o

>
<o

> </o

>
<TABLE style="MARGIN: auto 6.75pt; WIDTH: 450.6pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-table-lspace: 9.0pt; mso-table-rspace: 9.0pt; mso-table-anchor-vertical: paragraph; mso-table-anchor-horizontal: margin; mso-table-left: left; mso-table-top: -1.05pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=601 align=left><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 155.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=208 noWrap>ESRI ALLBRITTEN<o

></o

>
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 294.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=393 noWrap>BOOKS BY LIZ ALLEN CHOOSE ONE<o></o>
</TD></TR></TBODY></TABLE>
<o

> </o

>
<o

> </o

>
Firstly before I forget again to mention something that misleads you the data in this example is just example normally the author and title match the sample data will be replaced
<o

> </o

>
<TABLE style="MARGIN: auto auto auto 5.4pt; WIDTH: 450.6pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=601><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 155.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=208 noWrap>KATE ALLEN <o

></o

>
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 294.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=393 noWrap>BOOKS BY KATE ALLEN CHOOSE ONE<o></o>
</TD></TR></TBODY></TABLE>
<o

> </o

>
The rows need to move down and the data pasted here>>><o

></o

>
<o

> </o

>
<TABLE style="MARGIN: auto 6.75pt; WIDTH: 450.6pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-table-lspace: 9.0pt; mso-table-rspace: 9.0pt; mso-table-anchor-vertical: paragraph; mso-table-anchor-horizontal: margin; mso-table-left: left; mso-table-top: -1.05pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=601 align=left><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 155.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=208 noWrap>ESRI ALLBRITTEN<o

></o

>
</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 294.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=393 noWrap>BOOKS BY LIZ ALLEN CHOOSE ONE<o></o>
</TD></TR></TBODY></TABLE>
<o

> </o

>
<o

> </o

>
<TABLE style="MARGIN: auto auto auto 5.4pt; WIDTH: 161.15pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=215><TBODY><TR style="HEIGHT: 13.25pt; mso-yfti-irow: 0; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 161.15pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.25pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=top width=215 noWrap>KATE ALLEN <o

></o

>
</TD></TR></TBODY></TABLE>
<o

> </o

>
<o

> </o

>
When data is pasted in it need to take the label and past in along side the new data
<o

> </o

>
<o

> </o

>
Your code works perfectly if there was no other data on the sheet, one day I may be half as good as you ! as there is still a lot to learn about vba<o

></o

>

EDUCATED MONKEY · Jul 28, 2011

AlphaFrog said:

Code:

Sub TRANSFER3()
[COLOR="Green"]    '
    ' TRANSFER2 Macro
    ' Macro recorded 27/07/2011 by Peter Hayward
    '
    ' Keyboard Shortcut: Ctrl+Shift+E[/COLOR]
    
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim Q As Long, Nextrow As Long
    
    Set wsSource = Worksheets("REMOVE UNWANTED ITEMS")
    Set wsDest = Worksheets("NEW SHEET")
    
    Nextrow = wsDest.Range("B" & Rows.Count).End(xlUp).Row + 1  [COLOR="Green"]'Next empty row on wsDest[/COLOR]
    Q = wsSource.Range("H1").Value  [COLOR="Green"]'number of titles[/COLOR]
    
    Application.ScreenUpdating = False
        
    wsDest.Cells(Nextrow, "D").Resize(Q).Value = wsSource.Cells(1, "A").Resize(Q).Value
    wsDest.Cells(Nextrow, "B").Resize(Q).Value = wsDest.Range("B2").Value
    
    wsDest.Range("A2").Value = Q
    wsDest.Range("A3").Value = Nextrow     [COLOR="Green"]' test only[/COLOR]
    wsDest.Range("A4").Value = Nextrow + Q [COLOR="Green"]' test only[/COLOR]
    
    Application.ScreenUpdating = True
    
End Sub

Hi I need a bit of help modifying your code to suit my needs, I have change the point at which the label is retrieved from as the original reference point was static and it need to be the author in question so I pulled the from the source sheet problem solved. Now this is the confusing bit the active cell address remains at c2, and yet the last row/cell =c6 ? maybe you could enlighten me.

Now what I need is to tract the labels highlighted in green the reason for this is I am trying to use cell offset to place data in a column in line with the label 13 columns to the right

TITLE COLUMN (C ) row 1
BOOKS BY G K AALBORG CHOOSE ONE row 2
THE HORSE TAMER'S CHALLENGE row 3
BOOKS BY GORDON AALBORG CHOOSE ONE row 4
CAT TRACKS row 5
THE SPECIALIST row 6

So far I have this

wsSource.Select
Y = wsSource.Range("H1").Value <<<< title count from source sheet

wsDest.Select
ActiveCell.Select

ActiveCell.Offset(-Y, 13).Select

How ever since the active cell address = c2 the relevant position of the label with respect to the number of titles per author is all ways wrong unless there is away of working out the offset to get back to the label and across to column 13 each time data is added this being the primary question and if you have the time to explain why the active cell address = c2

Otherwise this code work better than perfect

unable to run Continuously

EDUCATED MONKEY

Board Regular

Excel Facts

AlphaFrog

MrExcel MVP

EDUCATED MONKEY

Board Regular

EDUCATED MONKEY

Board Regular

Similar threads

Forum statistics

Share this page

unable to run Continuously

EDUCATED MONKEY

Board Regular

Excel Facts

AlphaFrog

MrExcel MVP

EDUCATED MONKEY

Board Regular

EDUCATED MONKEY

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock