Please check my code & tell me where i'm doing it wrong. Cant make it work. Small code. Thanks

[pedie]

I dont know why my code does not leave a row before pasting....
please check where is it wrong.
Thanks ped

sub try1()
Selection.Copy
          Sheets("Data").Select
          Range("b2").Select
          Do Until ActiveCell.Value = ""
          ' i have place (1,0) to leave 1 row before pasting...
          ActiveCell.Offset(1, 0).Select
          Loop
activesheet.paste
end sub

 

[p45cal]

just add one more line:

Sub try1()
Selection.Copy
          Sheets("Data").Select
          Range("b2").Select
          Do Until ActiveCell.Value = ""
          ' i have place (1,0) to leave 1 row before pasting...
          ActiveCell.Offset(1, 0).Select
          Loop
[COLOR=Red]ActiveCell.Offset(1, 0).Select[/COLOR]
ActiveSheet.Paste
End Sub

 

[pedie]

Great thanks. I guess i'll try it out right now!

 

[pedie]

It does not work. I don't know why.

It leaves 1 row before pasting that is 1 row below b2. then when i run the code again does the same and overide preious data.
I want to find next empty one, then leave one skip 1 row and paste.

 

[lenze]

What are you trying to do?? Your code makes no sense.
All you need is one line

Selection.Copy Range("B"&Cells(Rows.Count,"B").End(xlUp).Row +1 )

lenze

 

[pedie]

Lenze, thank you so much!

 

[meldoc]

Hello

try.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> try1()<br>Selection.Copy<br>          Sheets("Data").Select<br>          Range("b2").Select<br>          <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> ActiveCell.Value = ""<br>          <SPAN style="color:#007F00">' i have place (1,0) to leave 1 row before pasting...</SPAN><br>          ActiveCell.Offset(2, 0).Select<br>          <SPAN style="color:#00007F">Loop</SPAN><br>ActiveSheet.Paste<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Norie]

pedie

You really should avoid using Selection/Select/ActiveCell etc.

Not only are they not usually needed but they can actually cause problems.

One other thing you could try is posting an explanation, in words, what the code is meant to do.

Obviously you are copying/pasting but it's not clear exactly what you are copying/pasting or where you are doing it.

 

[pedie]

Lenze, i hope you know that i have to copy and paste it in data sheet from sheet1.

Sorry for confusion.

 

[pedie]

Meldoc, Lenze, Norie...

I am still having trouble making it work...it does not leave any row..
maybe you can please redesign the whole code.

Thanks for helping

Current code.

Sub try1()
Sheets("Sheet1").Select
          Range("B2").Select
          Selection.CurrentRegion.Select
          Selection.Copy
          Sheets("Data").Select
          Range("b2").Select
          Do Until ActiveCell.Value = ""
          ' i have place (1,0) to leave 1 row before pasting...
          ActiveCell.Offset(2, 0).Select
          Loop
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
End Sub