Select.Range

[Guest]

I am currently busy designing a form to populate a list, and have this macro.
My only porblem is that I do not enter one line at a time I can enter up to
40. The first part of the macro works fine only then I want to take a ragne
form the Form worksheet and place it on the data worksheet.

Thank you for all the help the site has motivated me to buy a book on VB and
start studding, mor I just need a push in the right direction.

Regards

ES

Sub Button2_click()
Dim NewRow As Integer
NewRow = Worksheets("Form").Range("O1").Value + 1
If Worksheets("Form").Range("O2").Value <> 0 Then
MsgBox "There are errors, please enter data into all the fields", vbOKOnly,
"MeadInKent"
Exit Sub
End If

Worksheets("data").Cells(NewRow, 1).Value =
Worksheets("form").Range("A10:A40").Value
Worksheets("form").Range("A1").Select
End Sub

 

[Mike Fogleman]

Worksheets("form").Range("A10:A40").Copy Worksheets("data").Cells(NewRow,
1)

Mike F

 

[Guest]

Thank you Mike, but I am still doing someting wrong.

I have a range in worksheet "form", a10:a40, that I want to copy to the next
empty row in Worksheet "Data", column A. If I replace the last 3 lines of my
macro with this line nothing happens. Like I said I am still learning.

 

[Mike Fogleman]

I am not sure I see the whole picture you are trying to develop or how the
variable NewRow gets its value. Is A10:A40 a set range or can it vary in
length? Are there other data in column A that you don't want copied? Does
either sheet have a header in column A?
Here is a new variable to determine where to paste, since I'm not sure about
your NewRow variable.

Dim DestLastRow as Long

DestLastRow = Worksheets("data").Cells(Rows.Count, "A") _
..End(xlUp).Row+1
'If there is no header in "data" column A and you want to start
'on row 1, then unremark the next line
'If DestLastRow = 2 Then DestLastRow = 1
Worksheets("form").Range("A10:A40").Copy Worksheets("data") _
..Range("A"& DestLastRow)

If A10:A40 is also a variable range to copy, you can create a varible for it
also. Something like:

Dim SrcLastRow as Long
SrcLastRow = Worksheets("form").Cells(Rows.Count, "A") _
..End(xlUp).Row

Then use this in the last line above:

Worksheets("form").Range("A10:A"& SrcLastRow).Copy Worksheets("data") _
..Range("A"& DestLastRow)

Mike F