I need help with a macro. On my workbook I have two Worksheets.

Sheet1 is were I keep the form. Sheet2 is were the data is transferre
when I run the macro. The macro I have built works fine in transferrin
the data from the form (Sheet1) to the table on sheet2. I also have
clear button on sheet1 which when pressed the macro clears all data o
the form (Sheet1), so it is ready to input more data.

My problem starts from here, when I input new data on the form and ru
the macro in order to transfer data across to the table on sheet2. Th
previous data is overwritten and I can not build a list of customers
Is there a way of building a macro that will select the NEXT LINE o
the table every time I run the transfer button?:eek

--
Obi-Wan Kenob
-----------------------------------------------------------------------
Obi-Wan Kenobi's Profile: http://www.excelforum.com/member.php...fo&userid=3257
View this thread: http://www.excelforum.com/showthread.php?threadid=52447

You didn't include the code you are using so I have to give a generic
answer. In your code, you are copying and pasting. Your problem is
designating where to paste. I like to use a variable range that is the
first empty cell in some column, then offset from that cell for repeated
pasting.
You would use something like this line of code to set the destination cell.
Set Dest = Range("A" & Rows.Count).End(xlUp).Offset(1)
HTH Otto
"Obi-Wan Kenobi"
wrote in
message ...

I need help with a macro. On my workbook I have two Worksheets.

Sheet1 is were I keep the form. Sheet2 is were the data is transferred
when I run the macro. The macro I have built works fine in transferring
the data from the form (Sheet1) to the table on sheet2. I also have a
clear button on sheet1 which when pressed the macro clears all data on
the form (Sheet1), so it is ready to input more data.

My problem starts from here, when I input new data on the form and run
the macro in order to transfer data across to the table on sheet2. The
previous data is overwritten and I can not build a list of customers.
Is there a way of building a macro that will select the NEXT LINE on
the table every time I run the transfer button?


--
Obi-Wan Kenobi
------------------------------------------------------------------------
Obi-Wan Kenobi's Profile:
http://www.excelforum.com/member.php...o&userid=32578
View this thread: http://www.excelforum.com/showthread...hreadid=524474

with worksheets("sheet2")
set rng = .cells(rows.count,"A").end(up)(2)
End with

now use rng to determine where to write the data. It points to the next
empty cell in column 1 of sheet2. rng(1,2) is column B, rng(1,3) is
column C.

--
Regards,
Tom Ogilvy


"Obi-Wan Kenobi" wrote:


I need help with a macro. On my workbook I have two Worksheets.

Sheet1 is were I keep the form. Sheet2 is were the data is transferred
when I run the macro. The macro I have built works fine in transferring
the data from the form (Sheet1) to the table on sheet2. I also have a
clear button on sheet1 which when pressed the macro clears all data on
the form (Sheet1), so it is ready to input more data.

My problem starts from here, when I input new data on the form and run
the macro in order to transfer data across to the table on sheet2. The
previous data is overwritten and I can not build a list of customers.
Is there a way of building a macro that will select the NEXT LINE on
the table every time I run the transfer button?


--
Obi-Wan Kenobi
------------------------------------------------------------------------
Obi-Wan Kenobi's Profile: http://www.excelforum.com/member.php...o&userid=32578
View this thread: http://www.excelforum.com/showthread...hreadid=524474

Sorry for not including the macro, here it is can you help

Sub transfer1()
'
' transfer1 Macro
' Macro recorded 20/03/2006 by s
'

'
Range("A2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Su

--
Obi-Wan Kenob
-----------------------------------------------------------------------
Obi-Wan Kenobi's Profile: http://www.excelforum.com/member.php...fo&userid=3257
View this thread: http://www.excelforum.com/showthread.php?threadid=52447

Sub transfer1()
'
' transfer1 Macro
' Macro recorded 20/03/2006 by s
'

'
Range("A2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

should be replaced with
Sub transfer1()
Dim rng as Range
set rng = worksheets("Sheet2").Cells(rows.count,1).End(xlup) (2)
worksheets("Sheet1").Range("A2:C2").copy rng
End sub

--
Regards,
Tom Ogilvy


"Obi-Wan Kenobi" wrote:


Sorry for not including the macro, here it is can you help

Sub transfer1()
'
' transfer1 Macro
' Macro recorded 20/03/2006 by s
'

'
Range("A2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub


--
Obi-Wan Kenobi
------------------------------------------------------------------------
Obi-Wan Kenobi's Profile: http://www.excelforum.com/member.php...o&userid=32578
View this thread: http://www.excelforum.com/showthread...hreadid=524474

Thanks everyone looks like the problem, is sorted for now. That bein
said Im full of problems so no doubt we will be talking again

--
Obi-Wan Kenob
-----------------------------------------------------------------------
Obi-Wan Kenobi's Profile: http://www.excelforum.com/member.php...fo&userid=3257
View this thread: http://www.excelforum.com/showthread.php?threadid=52447