Copy Input sheet to Data sheet in VBA

[PayDirt]

On my website I have a form for clients to fill-in and submit. When I get it in my email I copy and paste it in Excel Sheet 1 ("Input" sheet). I want to copy it to Sheet 2 ("Data" sheet) like this:
Copy - Sheet 1 ("Input" sheet) cells F2:F19 (vertical)
Paste - Sheet 2 ("Data" sheet) cells B2:R2
the VB needs to advance to B3 ready for the next Input.
get it in my email I copy and paste it in Excel Sheet 1 ("Input" sheet) and continue...
your help is a PLUS <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 path="m@4@5l@4@11@9@11@9@5xe" stroked="f" filled="f" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 11.25pt; HEIGHT: 11.25pt" id=Picture_x0020_86 type="#_x0000_t75" alt="http://www.mrexcel.com/forum/images/smilies/icon_cool.gif" o:spid="_x0000_i1025"><v:imagedata o:href="cid:image001.gif@01CD34C5.E4024C90" src="file:///C:\Users\KEITHD~1\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape>I have 100 to 200 clients per day. BIG PROBLEM<o></o>
Thank you in advance for your help.<o></o>

 

[JLGWhiz]

On my website I have a form for clients to fill-in and submit. When I get it in my email I copy and paste it in Excel Sheet 1 ("Input" sheet). I want to copy it to Sheet 2 ("Data" sheet) like this:
Copy - Sheet 1 ("Input" sheet) cells F2:F19 (vertical)
Paste - Sheet 2 ("Data" sheet) cells B2:R2
the VB needs to advance to B3 ready for the next Input.
get it in my email I copy and paste it in Excel Sheet 1 ("Input" sheet) and continue...
your help is a PLUS <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 coordsize="21600,21600" o:spt="75" oreferrelative="t" filled="f" stroked="f" path="m@4@5l@4@11@9@11@9@5xe"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock v:ext="edit" aspectratio="t"></o:lock></v:shapetype><v:shape style="WIDTH: 11.25pt; HEIGHT: 11.25pt" id=Picture_x0020_86 o:spid="_x0000_i1025" alt="http://www.mrexcel.com/forum/images/smilies/icon_cool.gif" type="#_x0000_t75"><v:imagedata src="file:///C:\Users\KEITHD~1\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif" o:href="cid:image001.gif@01CD34C5.E4024C90"></v:imagedata></v:shape>I have 100 to 200 clients per day. BIG PROBLEM<o></o>
Thank you in advance for your help.<o></o>

Give this a try.

Code:
Sub cpyNpst()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
Set sh1 = Sheets("Input")
Set sh2 = Sheets("Data")
lr = sh2.Cells(Rows.Count, 2).End(xlUp).Row
sh1.Range("F2:F19").Copy
sh2.Range("B" & lr + 1).PasteSpecial xlPasteAll, Transpose:=True
End Sub
Code:

 

[PayDirt]

Thank you very much for you help. it works great
the only thing is it doesn't advance to cell b3 (ready for new input)
it overwrites the original input in b2. Should i put "End If"

 

[JLGWhiz]

Thank you very much for you help. it works great
the only thing is it doesn't advance to cell b3 (ready for new input)
it overwrites the original input in b2. Should i put "End If"

It advanced when I ran it. The lr + 1 forces it to advance. I cannot imagine what would keep it from going to the next line unless the line of code:

lr = sh2.Cells(Rows.Count, 2).End(xlUp).Row

is somehow bypassed in the second and subsequent iterations. Or if there was no data in F2 when the copy and paste took place then B2 would be blank and it would cause an overwrite. You can fix that possibility by replacing the above line of code with:

lr = sh2.Cells.Find(What:="*", After:=sh2.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

But to use this code, there must be something in cell A1 of sheet 2.

 

[PayDirt]

that's the winning ticket
Thank you very much again
Have a great day