Macro To Paste From Clipboard and delete / re-organise information

[SoulBlade]

Hi,

I am trying to paste information from an rtf file that is created from an external report (this is the best option for generating the report) to a spreed sheet and then to delete irrelevant information and sort the rest accordingly. The report can be any length as it is based on any staff that have 'clocked' on for the day (it is a finger scanner system).

I have already gone through the forum's and searched and worked out how to do the pasting which seems quite straight forward and I could always use the record macro function anyway to paste the information as unicode (because the rtf file generates the report sorting it into a table), but I need some kind of macro to also get rid of information that is not needed, and correctly format the required information. So basically you will click on a button and it will paste the information and re-organise accordingly.

When pasted, the spreed sheet looks like this (unforunately I am at work and can not use Jeanie or anything like that). I have tried to duplicate the rows and columns in red. The only information that I actually want is the name from column B, start work time from column F and end work time from Column F;

A B C D E F G H I J K L 1 API 2 Employee Clocking Report 3 Period from Tue 18 Sep 2012 to Tue 18 Sep 2012 4 5 Clocking Device Event Event Timestamp Successful Shift Date / Information 6 7 Fred Smith 8 9 10 zk07.Bundamba DC (187) Start Work 18/09/2012 4:55 T 18-Sep-12 11 12 13 zk07.Bundamba DC (187) Start Break 18/09/2012 7:05 T 18-Sep-12 14 15 16 zk07.Bundamba DC (187) End Break 18/09/2012 7:13 T 18-Sep-12 17 18 19 zk07.Bundamba DC (187) Start Break 18/09/2012 9:32 T 18-Sep-12 20 21 22 zk07.Bundamba DC (187) End Break 18/09/2012 9:42 T 18-Sep-12 23 24 25 zk07.Bundamba DC (187) Start Break 18/09/2012 11:34 T 18-Sep-12 26 27 28 zk09.Bundamba DC (189) End Work 18/09/2012 12:04 T 18-Sep-12 29 30 31 Joe Smith 32 33 34 zk09.Bundamba DC (189) End Work 18/09/2012 0:00 T 17-Sep-12 35 36 37 John Smith 38 39 40 zk09.Bundamba DC (189) Start Work 18/09/2012 4:54 T 18-Sep-12 41 42 43 zk09.Bundamba DC (189) Start Break 18/09/2012 7:40 T 18-Sep-12 44 45 46 zk09.Bundamba DC (189) End Break 18/09/2012 7:52 T 18-Sep-12 47 48 49 zk09.Bundamba DC (189) Start Break 18/09/2012 10:09 T 18-Sep-12 50 51 52 zk09.Bundamba DC (189) End Break 18/09/2012 10:37 T 18-Sep-12 53 54 55 zk09.Bundamba DC (189) Start Break 18/09/2012 12:01 T 18-Sep-12 56 57 58 zk09.Bundamba DC (189) End Work 18/09/2012 12:13 T 18-Sep-12 <TBODY> </TBODY>

<TBODY>
</TBODY>

But I need it to simply be 3 columns All information on the 1 row for each person;

• Column A - Name
• Column B - Start Time
• Column C - End Time

For example from above on row 1 would be 'Fred Smith'(Column A) / '04:55' (Column B - Start Time) / '12:04' (Column C - End Work)

Sorry this is a bit messy, but any help would be greatly appreciated.

Thanx, Chris.

 

[hatstand]

Is the data always in the same format. i.e. Name, blank two rows, data, blank two rows etc

 

[bertie]

Assuming the data is pasted into sheet1.
Assuming your data is as the sample data.

Get the last row based on column E

   [COLOR=darkblue]With[/COLOR] Sheets("Sheet1")
      [COLOR=green]'get the last row[/COLOR]
      lr = .Range("E" & .Rows.Count).End(xlUp).row

The code then loops through the data. Required values are output to column M, N and O on the same worksheet. You may have to edit this.

Note how when the End Work time is detected the output row counter is incremented

There is an option to delete the original data, again you may need to edit.
Try the code below:

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]


[COLOR=darkblue]Sub[/COLOR] Test()
   [COLOR=darkblue]Dim[/COLOR] lr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]          [COLOR=green]'last row[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]           'loop index
   [COLOR=darkblue]Dim[/COLOR] rowOutput [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]          [COLOR=green]'output row counter[/COLOR]
   
   [COLOR=green]'Paste From Clipboard[/COLOR]
   [COLOR=green]'Sheets("Sheet1").Range("A1").PasteSpecial[/COLOR]
   
   rowOutput = 2 [COLOR=green]'assume header in output row[/COLOR]
   
   [COLOR=darkblue]With[/COLOR] Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
      [COLOR=green]'get the last row[/COLOR]
      lr = .Range("E" & .Rows.Count).End(xlUp).row
   
      [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] lr
         [COLOR=green]'name[/COLOR]
         [COLOR=darkblue]If[/COLOR] .Range("B" & i).Value <> "" [COLOR=darkblue]Then[/COLOR]
            .Range("[COLOR=#ff0000]M[/COLOR]" & rowOutput).Value = .Range("B" & i).Value
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
         
         [COLOR=green]'start time[/COLOR]
         [COLOR=darkblue]If[/COLOR] UCase(.Range("E" & i).Value) = "START WORK" [COLOR=darkblue]Then[/COLOR]
            .Range("[COLOR=#ff0000]N"[/COLOR] & rowOutput).Value = Format(.Range("F" & i).Value, "hh:mm")
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
         
         [COLOR=green]'end time[/COLOR]
         [COLOR=darkblue]If[/COLOR] UCase(.Range("E" & i).Value) = "END WORK" [COLOR=darkblue]Then[/COLOR]
            .Range("[COLOR=#ff0000]O[/COLOR]" & rowOutput).Value = Format(.Range("F" & i).Value, "hh:mm")
            [COLOR=#ff0000]rowOutput = rowOutput + 1[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]Next[/COLOR] i
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
   
   [COLOR=green]'optional delete columns[/COLOR]
   [COLOR=green]'With Sheets("Sheet1")[/COLOR]
   [COLOR=green]'   Sheets("Sheet1").Columns("[/COLOR][COLOR=#ff0000]A:L[/COLOR][COLOR=green]").EntireColumn.Delete Shift:=xlToRight[/COLOR]
   [COLOR=green]'End With[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]