Need Help with a MACRO

[Obi-Wan Kenobi]

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

 

[Otto Moehrbach]

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"

 

[Guest]

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.

 

[Obi-Wan Kenobi]

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

 

[Guest]

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

 

[Obi-Wan Kenobi]

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