Copy, re-order, and Paste macro

[Dan Kay]

Hi,

I'm very new to VBA and am trying to do something that I'd think would be easy. I have a spreadsheet with information about employees calling absent for the day. I want to pull information from various columns in the active row, reorganize them into the clipboard to be pasted into another application.

The columns used are B, C, D, J, and K of the active row. Column K may be an empty cell on any given row.

Here is what I have so far...it doesn't like my activecell.row argument. I'm pasting the whole macro in case there are other errors in it:

Sub Copy()
'
'
'
Dim DOC As String ? Date of Call ? Column B
Dim PHN As String ? Phone Number ? Column C
Dim TOC As String ? Time of Call ? Column D
Dim EXN As String ? Exception ? Column J
Dim ACC As String ? Accommodations ? Column K
Dim RSN As String ? Reason ? derived from EXN
Dim PST As String ? Final data pasted toclipboard

? Assigning variables from the active row
DOC = (ActiveCell.Row): B
PHN = (ActiveCell.Row): C
TOC = (ActiveCell.Row): D
EXN = (ActiveCell.Row): J
ACC = (ActiveCell.Row): K

? Setting RSN based on EXN
If EXN = "L" Then
RSN = "Late"

ElseIf EXN = "E" Then
RSN = "Left Early"

ElseIf EXN = "M" Then
RSN = "Left and returned mid-shift"

ElseIf EXN = "F" Then
RSN = "Absent"

ElseIf EXN = "UPTO" Then
RSN = "Absent"

ElseIf EXN = "UNTU" Then
RSN = "Absent"

ElseIf EXN = "OTCNCL" Then
RSN = "Banker cancel OT"

End If
' Arranging final output to be pasted

PST = DOC & ", " & PHN & ", " & TOC & ", " & RSN & " " & ACC & " - "

? Loading output to the clipboard

PST.PutInClipboard

End Sub


Thank you in advance for any assistance!

 

[isabelle]

hi Dan,

Sub Copy()
Dim DOC As String '? Date of Call ? Column B
Dim PHN As String '? Phone Number ? Column C
Dim TOC As String '? Time of Call ? Column D
Dim EXN As String '? Exception ? Column J
Dim ACC As String '? Accommodations ? Column K
Dim RSN As String '? Reason ? derived from EXN
Dim PST As String '? Final data pasted toclipboard

'Print Assigning; variables; from; the; active; Row
DOC = Range("B" & (ActiveCell.Row))
PHN = Range("C" & (ActiveCell.Row))
TOC = Range("D" & (ActiveCell.Row))
EXN = Range("J" & (ActiveCell.Row))
ACC = Range("K" & (ActiveCell.Row))

Select Case EXN
Case "L": RSN = "Late"
Case "E": RSN = "Left Early"
Case "M": RSN = "Left and returned mid-shift"
Case "F", "UPTO", "UNTU": RSN = "Absent"
Case "OTCNCL": RSN = "Banker cancel OT"
End Select

' Arranging final output to be pasted
PST = DOC & ", " & PHN & ", " & TOC & ", " & RSN & " " & ACC & " - "

Range("IV1") = PST
Range("IV1").Copy
End Sub


--
isabelle



Le 2012-01-29 19:19, Dan Kay a écrit :

Hi,

I'm very new to VBA and am trying to do something that I'd think would be easy.

I have a spreadsheet with information about employees calling absent for the day.

I want to pull information from various columns in the active row,

reorganize them into the clipboard to be pasted into another application.

The columns used are B, C, D, J, and K of the active row.

Column K may be an empty cell on any given row.

Here is what I have so far...it doesn't like my activecell.row argument.

I'm pasting the whole macro in case there are other errors in it:

 

[Hans Terkelsen]

Hi,

I'm very new to VBA and am trying to do something that I'd think would be easy. I have a spreadsheet with information about
employees calling absent for the day. I want to pull information from various columns in the active row, reorganize them into
the clipboard to be pasted into another application.

The columns used are B, C, D, J, and K of the active row. Column K may be an empty cell on any given row.

Here is what I have so far...it doesn't like my activecell.row argument. I'm pasting the whole macro in case there are other
errors in it:

Sub Copy()
'
'
'
Dim DOC As String ? Date of Call ? Column B
Dim PHN As String ? Phone Number ? Column C
Dim TOC As String ? Time of Call ? Column D
Dim EXN As String ? Exception ? Column J
Dim ACC As String ? Accommodations ? Column K
Dim RSN As String ? Reason ? derived from EXN
Dim PST As String ? Final data pasted toclipboard

? Assigning variables from the active row
DOC = (ActiveCell.Row): B
PHN = (ActiveCell.Row): C
TOC = (ActiveCell.Row): D
EXN = (ActiveCell.Row): J
ACC = (ActiveCell.Row): K

? Setting RSN based on EXN
If EXN = "L" Then
RSN = "Late"

ElseIf EXN = "E" Then
RSN = "Left Early"

ElseIf EXN = "M" Then
RSN = "Left and returned mid-shift"

ElseIf EXN = "F" Then
RSN = "Absent"

ElseIf EXN = "UPTO" Then
RSN = "Absent"

ElseIf EXN = "UNTU" Then
RSN = "Absent"

ElseIf EXN = "OTCNCL" Then
RSN = "Banker cancel OT"

End If
' Arranging final output to be pasted

PST = DOC & ", " & PHN & ", " & TOC & ", " & RSN & " " & ACC & " - "

? Loading output to the clipboard

PST.PutInClipboard

End Sub


Thank you in advance for any assistance!

Hi Dan.

Isabelle's solution is nice and easy to follow.

If you want to use the PutInClipboard command
then reference must be set to FM20.dll
or Microsoft Forms 2.0 Object Library.

Then the following lines

Set datobj = New DataObject
'xyz = datobj.GetFromClipboard
datobj.SetText PST 'or more
datobj.PutInClipboard


will put PST in the clipboard.

Or, by using GetFromClipboard, you have the chance
to hop around to different files and add PST or whatever
to the clipboard, before you paste the full result out.

Hans T.