Greetings,

I have two sheets. One to check, verify or make new record with the
entries. The second is to store the data.

To verify is simply to look at each record until a mistake or omission
is discovered, then I must decide if a correction of this record is
required or a new record with slightly different data needs to be
made.

For a correction, I use the link from a Forms ComboBox, which gives me
the row number to modify.

For a new record to be made, from this record I just modified,
requires that I first make a copy of the last row in the data list,
copy it to the row just below the last row and then copy the data,
from the form that I modified, into this new last row, one cell at a
time.

I tried this sub to choose and copy to the correct row. Only the
modified chosen row works (because of the value in the ComboBox linked
cell):

Private Sub bCompleteRecordEdit_Click()
Dim lRowToEdit As Long
Dim lLastRow As Long

Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("CustList")
If MsgBox("Do want to modify this record or create a new record?
Click Yes to modify or No to create a new record.", vbYesNo, "What do
you want to do with this record?") = vbYes Then
lRowToEdit = Range("pfDisc").Value '<<<This choice works
Else
ws1.Activate 'This is the destination sheet

'<This is where debug kicked me out with
'Copy method of range class failed
ws1.Range("A65536").End(xlUp).Copy _
Destination:=Range("A65536").End(xlUp) + 1 '<<<

Application.CutCopyMode = False
lRowToEdit = Range("A65536").End(xlUp) + 1
End If

'PasteDown1 takes an argument which is
'the row that PasteDown1 is to work with.
PasteDown1 lRowToEdit
End Sub

Here is the PasteDown1 Sub:

Sub PasteDown1(lRowToModify As Long)
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("CustList")
Set ws2 = wb1.Worksheets("Print_Form")
With ws1.Range("A" & lRowToModify + 1)
For i = 4 To 79
.Offset(0, i).Value = _
ws2.Range("pfCell_" & i).Value
Next i
End With
End Sub

I think I need a different way to identify the last row.

Any ideas or pointers will be greatly appreciated.

-Minitman

Hi
Change
ws1.Range("A65536").End(xlUp).Copy _
Destination:=Range("A65536").End(xlUp) + 1 '<<<

to

ws1.Range("A65536").End(xlUp).Copy _
Destination:=Range("A65536").End(xlUp) (2)

HTH
Cordially
Pascal


"Minitman" a écrit dans le message de news:
...
Greetings,

I have two sheets. One to check, verify or make new record with the
entries. The second is to store the data.

To verify is simply to look at each record until a mistake or omission
is discovered, then I must decide if a correction of this record is
required or a new record with slightly different data needs to be
made.

For a correction, I use the link from a Forms ComboBox, which gives me
the row number to modify.

For a new record to be made, from this record I just modified,
requires that I first make a copy of the last row in the data list,
copy it to the row just below the last row and then copy the data,
from the form that I modified, into this new last row, one cell at a
time.

I tried this sub to choose and copy to the correct row. Only the
modified chosen row works (because of the value in the ComboBox linked
cell):

Private Sub bCompleteRecordEdit_Click()
Dim lRowToEdit As Long
Dim lLastRow As Long

Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("CustList")
If MsgBox("Do want to modify this record or create a new record?
Click Yes to modify or No to create a new record.", vbYesNo, "What do
you want to do with this record?") = vbYes Then
lRowToEdit = Range("pfDisc").Value '<<<This choice works
Else
ws1.Activate 'This is the destination sheet

'<This is where debug kicked me out with
'Copy method of range class failed
ws1.Range("A65536").End(xlUp).Copy _
Destination:=Range("A65536").End(xlUp) + 1 '<<<

Application.CutCopyMode = False
lRowToEdit = Range("A65536").End(xlUp) + 1
End If

'PasteDown1 takes an argument which is
'the row that PasteDown1 is to work with.
PasteDown1 lRowToEdit
End Sub

Here is the PasteDown1 Sub:

Sub PasteDown1(lRowToModify As Long)
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("CustList")
Set ws2 = wb1.Worksheets("Print_Form")
With ws1.Range("A" & lRowToModify + 1)
For i = 4 To 79
.Offset(0, i).Value = _
ws2.Range("pfCell_" & i).Value
Next i
End With
End Sub

I think I need a different way to identify the last row.

Any ideas or pointers will be greatly appreciated.

-Minitman

Hey Papou,

Thanks for the reply.

I could not get your modification to work properly.

I have no idea where this record has been copied!!

BTW What is the (2) for?

-Minitman



On Thu, 3 Apr 2008 08:48:51 +0200, "papou"
wrote:

Hi
Change
ws1.Range("A65536").End(xlUp).Copy _
Destination:=Range("A65536").End(xlUp) + 1 '<<<

to

ws1.Range("A65536").End(xlUp).Copy _
Destination:=Range("A65536").End(xlUp) (2)

HTH
Cordially
Pascal


"Minitman" a écrit dans le message de news:
...
Greetings,

I have two sheets. One to check, verify or make new record with the
entries. The second is to store the data.

To verify is simply to look at each record until a mistake or omission
is discovered, then I must decide if a correction of this record is
required or a new record with slightly different data needs to be
made.

For a correction, I use the link from a Forms ComboBox, which gives me
the row number to modify.

For a new record to be made, from this record I just modified,
requires that I first make a copy of the last row in the data list,
copy it to the row just below the last row and then copy the data,
from the form that I modified, into this new last row, one cell at a
time.

I tried this sub to choose and copy to the correct row. Only the
modified chosen row works (because of the value in the ComboBox linked
cell):

Private Sub bCompleteRecordEdit_Click()
Dim lRowToEdit As Long
Dim lLastRow As Long

Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("CustList")
If MsgBox("Do want to modify this record or create a new record?
Click Yes to modify or No to create a new record.", vbYesNo, "What do
you want to do with this record?") = vbYes Then
lRowToEdit = Range("pfDisc").Value '<<<This choice works
Else
ws1.Activate 'This is the destination sheet

'<This is where debug kicked me out with
'Copy method of range class failed
ws1.Range("A65536").End(xlUp).Copy _
Destination:=Range("A65536").End(xlUp) + 1 '<<<

Application.CutCopyMode = False
lRowToEdit = Range("A65536").End(xlUp) + 1
End If

'PasteDown1 takes an argument which is
'the row that PasteDown1 is to work with.
PasteDown1 lRowToEdit
End Sub

Here is the PasteDown1 Sub:

Sub PasteDown1(lRowToModify As Long)
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("CustList")
Set ws2 = wb1.Worksheets("Print_Form")
With ws1.Range("A" & lRowToModify + 1)
For i = 4 To 79
.Offset(0, i).Value = _
ws2.Range("pfCell_" & i).Value
Next i
End With
End Sub

I think I need a different way to identify the last row.

Any ideas or pointers will be greatly appreciated.

-Minitman

Hi
Which is the sheet you want the data copied to?
If it is not the active sheet then you must fully identify it:
Dim ws2 as WorkSheet
Set ws2 thisworkbook.Worksheets("YourSheetNameHere")

ws1.Range("A65536").End(xlUp).Copy _
Destination:=ws2.Range("A65536").End(xlUp) (2)

The Range("A65536").End(xlUp) (2) refers to the next empty row in column A
as per requested in your original message.

HTH
Cordially
Pascal

"Minitman" a écrit dans le message de news:
...
Hey Papou,

Thanks for the reply.

I could not get your modification to work properly.

I have no idea where this record has been copied!!

BTW What is the (2) for?

-Minitman



On Thu, 3 Apr 2008 08:48:51 +0200, "papou"
wrote:

Hi
Change
ws1.Range("A65536").End(xlUp).Copy _
Destination:=Range("A65536").End(xlUp) + 1 '<<<

to

ws1.Range("A65536").End(xlUp).Copy _
Destination:=Range("A65536").End(xlUp) (2)

HTH
Cordially
Pascal


"Minitman" a écrit dans le message de news:
...
Greetings,

I have two sheets. One to check, verify or make new record with the
entries. The second is to store the data.

To verify is simply to look at each record until a mistake or omission
is discovered, then I must decide if a correction of this record is
required or a new record with slightly different data needs to be
made.

For a correction, I use the link from a Forms ComboBox, which gives me
the row number to modify.

For a new record to be made, from this record I just modified,
requires that I first make a copy of the last row in the data list,
copy it to the row just below the last row and then copy the data,
from the form that I modified, into this new last row, one cell at a
time.

I tried this sub to choose and copy to the correct row. Only the
modified chosen row works (because of the value in the ComboBox linked
cell):

Private Sub bCompleteRecordEdit_Click()
Dim lRowToEdit As Long
Dim lLastRow As Long

Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("CustList")
If MsgBox("Do want to modify this record or create a new record?
Click Yes to modify or No to create a new record.", vbYesNo, "What do
you want to do with this record?") = vbYes Then
lRowToEdit = Range("pfDisc").Value '<<<This choice works
Else
ws1.Activate 'This is the destination sheet

'<This is where debug kicked me out with
'Copy method of range class failed
ws1.Range("A65536").End(xlUp).Copy _
Destination:=Range("A65536").End(xlUp) + 1 '<<<

Application.CutCopyMode = False
lRowToEdit = Range("A65536").End(xlUp) + 1
End If

'PasteDown1 takes an argument which is
'the row that PasteDown1 is to work with.
PasteDown1 lRowToEdit
End Sub

Here is the PasteDown1 Sub:

Sub PasteDown1(lRowToModify As Long)
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("CustList")
Set ws2 = wb1.Worksheets("Print_Form")
With ws1.Range("A" & lRowToModify + 1)
For i = 4 To 79
.Offset(0, i).Value = _
ws2.Range("pfCell_" & i).Value
Next i
End With
End Sub

I think I need a different way to identify the last row.

Any ideas or pointers will be greatly appreciated.

-Minitman

Hey Papou,

Thank you for the correction on the active sheet notation.
As for the rest, sorry for the confusion.

The row to be copied is the last row in the sheet named "CustList".
The row to be pasted into is the row below the row to be copied.

The reason for this copy and paste is that I want all of the
formatting that is in the last row to be copied into the new last row,
so that when I paste the contents of the 75 cells from a sheet called
"Print_Form", into the new last row of "CustList", I get the same
formatting as the rest of that sheet. I want this data to overwrite
the contents (but not the formatting) of each cell in the new last
row. This is done in a separate sub called "PasteDown1(lRowToModify
As Long)", which is listed below in an earlier posting in this thread.
PasteDown1 will paste the contents of the 75 cells in the chosen row
(which is the argument passed to "PasteDown1" as lRowToModify). I
have the lRowToModify value for the Modify choice in the MsgBox (it is
a named range called "pfDisc", which is the range linked to the
ComboBox). I am looking for the row number of this new last row in
"CustList" to enter as the argument of "PasteSown1" for the "Create"
choice in the MsgBox.

As for the (2), I figured as much, but what I was looking for is what
is it called and what are it's limitations? I was not aware that
there were ANY such modifiers available for that command and so I
wanted to look up what the parameters were for this modifier and see
if there was more to be learned about it like how to use it, what
other numbers will do. Also is it for the Range item or is it part of
the range modifier? {Range() or Range("A65536").End(xlUp) <I am not
sure what the objects after the "." are called).

Any additional information is appreciated

Please excuse the use of incorrect names for the objects in question,
I do not have a formal education in VBA, it is all self taught and as
such there are many gaps in my knowledge. Corrections are gratefully
accepted

-Minitman



On Fri, 4 Apr 2008 08:59:06 +0200, "papou"
wrote:

Hi
Which is the sheet you want the data copied to?
If it is not the active sheet then you must fully identify it:
Dim ws2 as WorkSheet
Set ws2 thisworkbook.Worksheets("YourSheetNameHere")

ws1.Range("A65536").End(xlUp).Copy _
Destination:=ws2.Range("A65536").End(xlUp) (2)

The Range("A65536").End(xlUp) (2) refers to the next empty row in column A
as per requested in your original message.

HTH
Cordially
Pascal

"Minitman" a écrit dans le message de news:
...
Hey Papou,

Thanks for the reply.

I could not get your modification to work properly.

I have no idea where this record has been copied!!

BTW What is the (2) for?

-Minitman



On Thu, 3 Apr 2008 08:48:51 +0200, "papou"
wrote:

Hi
Change
ws1.Range("A65536").End(xlUp).Copy _
Destination:=Range("A65536").End(xlUp) + 1 '<<<

to

ws1.Range("A65536").End(xlUp).Copy _
Destination:=Range("A65536").End(xlUp) (2)

HTH
Cordially
Pascal


"Minitman" a écrit dans le message de news:
...
Greetings,

I have two sheets. One to check, verify or make new record with the
entries. The second is to store the data.

To verify is simply to look at each record until a mistake or omission
is discovered, then I must decide if a correction of this record is
required or a new record with slightly different data needs to be
made.

For a correction, I use the link from a Forms ComboBox, which gives me
the row number to modify.

For a new record to be made, from this record I just modified,
requires that I first make a copy of the last row in the data list,
copy it to the row just below the last row and then copy the data,
from the form that I modified, into this new last row, one cell at a
time.

I tried this sub to choose and copy to the correct row. Only the
modified chosen row works (because of the value in the ComboBox linked
cell):

Private Sub bCompleteRecordEdit_Click()
Dim lRowToEdit As Long
Dim lLastRow As Long

Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("CustList")
If MsgBox("Do want to modify this record or create a new record?
Click Yes to modify or No to create a new record.", vbYesNo, "What do
you want to do with this record?") = vbYes Then
lRowToEdit = Range("pfDisc").Value '<<<This choice works
Else
ws1.Activate 'This is the destination sheet

'<This is where debug kicked me out with
'Copy method of range class failed
ws1.Range("A65536").End(xlUp).Copy _
Destination:=Range("A65536").End(xlUp) + 1 '<<<

Application.CutCopyMode = False
lRowToEdit = Range("A65536").End(xlUp) + 1
End If

'PasteDown1 takes an argument which is
'the row that PasteDown1 is to work with.
PasteDown1 lRowToEdit
End Sub

Here is the PasteDown1 Sub:

Sub PasteDown1(lRowToModify As Long)
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("CustList")
Set ws2 = wb1.Worksheets("Print_Form")
With ws1.Range("A" & lRowToModify + 1)
For i = 4 To 79
.Offset(0, i).Value = _
ws2.Range("pfCell_" & i).Value
Next i
End With
End Sub

I think I need a different way to identify the last row.

Any ideas or pointers will be greatly appreciated.

-Minitman