Help required to send data from one worksheet to another

[Alan T]

I'm trying to create a spreadsheet that will be used to log details of
calls coming into a helpdesk. The spreadsheet is going to be used to
provide management information about the nature and type of calls.

I'm trying to move all data from one sheet onto another sheet in the
same workbook (sheets CLT and CS). I'm looking to transfer data from
cells L8,L10,L12,L14 on sheet CLT to sheet CS when a button is clicked.
When the button is pressed for the first time the data should record on
Sheet CS in Cells A2,C2,E2,G2, the second time the button is pressed
the data should record in cells A3,C3,E3,G3, the next time A4,C4, E4,
G4 etc

I have some validation checks being run when the button is pressed at
the moment and these are required going forward. The button also resets
the fields providing validation is succesful.

Sub UpdateNow()
'
' UpdateNow Macro
' Macro recorded 29/10/2003 by Alan Towle
'
If Range("l8") = 1 Then response = MsgBox("Please record your
Initials", 0, "Error!")
If response = vbOK Then GoTo cancelled

If Range("l10") = 1 Then response = MsgBox("Please record the Branch
Details", 0, "Error!")
If response = vbOK Then GoTo cancelled

If Range("l12") = 1 Then response = MsgBox("Please record the
Application Details", 0, "Error!")
If response = vbOK Then GoTo cancelled

If Range("l14") = 1 Then response = MsgBox("Please record the nature
of the Query", 0, "Error!")
If response = vbOK Then GoTo cancelled

response = MsgBox("Are you sure you want to log your call?", 1, "Log
your call?")
If response = vbCancel Then GoTo cancelled
Range("l8,l10,l12,l14").Select
Selection.FormulaR1C1 = "1"

cancelled:
'
End Sub

Any suggestions?

 

[Paul B]

Alan, here is one way, if there is nothing in the columns below where the
data will be placed

Sub CopyRange()
Sheets("CTL").Range("L8").Copy
Sheets("CS").Range("A65536").End(xlUp).Offset(1, 0)
Sheets("CTL").Range("L10").Copy
Sheets("CS").Range("C65536").End(xlUp).Offset(1, 0)
Sheets("CTL").Range("L12").Copy
Sheets("CS").Range("E65536").End(xlUp).Offset(1, 0)
Sheets("CTL").Range("L14").Copy
Sheets("CS").Range("G65536").End(xlUp).Offset(1, 0)
End Sub


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Paul B]

Alan, I did not see your macro the first time, try this, also added where it
will select the cells that need to be filled in, if I understand what you
are doing.

Sub UpdateNow()
'
' UpdateNow Macro
' Macro recorded 29/10/2003 by Alan Towle
Application.ScreenUpdating = False
If Range("L8") = 1 Then response = _
MsgBox("Please record your Initials", 0, "Error!")
[L8].Select
If response = vbOK Then GoTo cancelled

If Range("L10") = 1 Then response = _
MsgBox("Please record the Branch Details", 0, "Error!")
[L10].Select
If response = vbOK Then GoTo cancelled

If Range("L12") = 1 Then response = _
MsgBox("Please record the Application Details", 0, "Error!")
[L12].Select
If response = vbOK Then GoTo cancelled

If Range("L14") = 1 Then response = _
MsgBox("Please record the nature of the Query", 0, "Error!")
[L14].Select
If response = vbOK Then GoTo cancelled

response = MsgBox("Are you sure you want to log your call?", 1, "Log your
call?")
If response = vbCancel Then GoTo cancelled

Sheets("CTL").Range("L8").Copy _
Sheets("CS").Range("A65536").End(xlUp).Offset(1, 0)
Sheets("CTL").Range("L10").Copy _
Sheets("CS").Range("C65536").End(xlUp).Offset(1, 0)
Sheets("CTL").Range("L12").Copy _
Sheets("CS").Range("E65536").End(xlUp).Offset(1, 0)
Sheets("CTL").Range("L14").Copy _
Sheets("CS").Range("G65536").End(xlUp).Offset(1, 0)

Range("L8,L10,L12,L14") = 1

cancelled:
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

Alan, here is one way, if there is nothing in the columns below where the
data will be placed

Sub CopyRange()
Sheets("CTL").Range("L8").Copy
Sheets("CS").Range("A65536").End(xlUp).Offset(1, 0)
Sheets("CTL").Range("L10").Copy
Sheets("CS").Range("C65536").End(xlUp).Offset(1, 0)
Sheets("CTL").Range("L12").Copy
Sheets("CS").Range("E65536").End(xlUp).Offset(1, 0)
Sheets("CTL").Range("L14").Copy
Sheets("CS").Range("G65536").End(xlUp).Offset(1, 0)
End Sub


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Alan T]

*Alan, I did not see your macro the first time, try this, also added
where it
will select the cells that need to be filled in, if I understand what
you
are doing.

Sub UpdateNow()
'
' UpdateNow Macro
' Macro recorded 29/10/2003 by Alan Towle
Application.ScreenUpdating = False
If Range("L8") = 1 Then response = _
MsgBox("Please record your Initials", 0, "Error!")
[L8].Select
If response = vbOK Then GoTo cancelled

If Range("L10") = 1 Then response = _
MsgBox("Please record the Branch Details", 0, "Error!")
[L10].Select
If response = vbOK Then GoTo cancelled

If Range("L12") = 1 Then response = _
MsgBox("Please record the Application Details", 0, "Error!")
[L12].Select
If response = vbOK Then GoTo cancelled

If Range("L14") = 1 Then response = _
MsgBox("Please record the nature of the Query", 0, "Error!")
[L14].Select
If response = vbOK Then GoTo cancelled

response = MsgBox("Are you sure you want to log your call?", 1, "Log
your
call?")
If response = vbCancel Then GoTo cancelled

Sheets("CTL").Range("L8").Copy _
Sheets("CS").Range("A65536").End(xlUp).Offset(1, 0)
Sheets("CTL").Range("L10").Copy _
Sheets("CS").Range("C65536").End(xlUp).Offset(1, 0)
Sheets("CTL").Range("L12").Copy _
Sheets("CS").Range("E65536").End(xlUp).Offset(1, 0)
Sheets("CTL").Range("L14").Copy _
Sheets("CS").Range("G65536").End(xlUp).Offset(1, 0)

Range("L8,L10,L12,L14") = 1

cancelled:
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from
it
** remove news from my email address to reply by email **

*

<snip>

Thanks Paul ... that's exactly what I was looking to achieve.