Select.Range

[Esrei]

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

"Esrei" <[email protected]> wrote in message
news:[email protected]...
>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
>

[Esrei]

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" wrote:

> Worksheets("form").Range("A10:A40").Copy Worksheets("data").Cells(NewRow,
> 1)
>
> Mike F
>
> "Esrei" <[email protected]> wrote in message
> news:[email protected]...
> >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]

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

"Esrei" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> Worksheets("form").Range("A10:A40").Copy
>> Worksheets("data").Cells(NewRow,
>> 1)
>>
>> Mike F
>>
>> "Esrei" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>> >
>>
>>
>>