Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trouble pasting into Excel (Excel 2003)

    I am trying to copy and past a report that I get via E-mail into Excel. The report comes to me in a word file. I have no knowledge of how the report is created nor can I have it changed. My problem is that when I past the report into Excel every field on the report is put in a different ROW. I really only need one column out of the report. I have attached a small part of the report with the names changed. Any help would be appreciated.

    M

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble pasting into Excel (Excel 2003)

    If you use Paste Special/Text to past the data into Sheet1, then this macro should reformat the data onto Sheet2 the way you want it:

    <pre>Public Sub FormatData()
    Dim lLastRow As Long, I As Long, J As Long, K As Long
    Dim oSrc As Worksheet, oTgt As Worksheet
    Application.ScreenUpdating = False
    Set oSrc = Worksheets("Sheet1")
    Set oTgt = Worksheets("Sheet2")
    lLastRow = oSrc.Range("A65536").End(xlUp).Row - 1
    oTgt.Cells.Clear
    I = 0
    J = 0
    Do While I < lLastRow
    oTgt.Range("A1").Offset(J, 0).Value = oSrc.Range("A1").Offset(I, 0).Value
    I = I + 1
    J = J + 1
    Do While (oSrc.Range("A1").Offset(I + 1, 0).Value <> "Total Boxes Scanned:")
    oTgt.Range("B1").Offset(J, 0).Value = oSrc.Range("A1").Offset(I, 0).Value
    oTgt.Range("C1").Offset(J, 0).Value = oSrc.Range("A1").Offset(I + 1, 0).Value
    oTgt.Range("D1").Offset(J, 0).Value = oSrc.Range("A1").Offset(I + 4, 0).Value
    oTgt.Range("E1").Offset(J, 0).Value = oSrc.Range("A1").Offset(I + 2, 0).Value
    oTgt.Range("F1").Offset(J, 0).Value = oSrc.Range("A1").Offset(I + 3, 0).Value
    I = I + 5
    J = J + 1
    Loop
    oTgt.Range("E1").Offset(J, 0).Value = oSrc.Range("A1").Offset(I + 1, 0).Value
    oTgt.Range("F1").Offset(J, 0).Value = oSrc.Range("A1").Offset(I, 0).Value
    I = I + 2
    J = J + 2
    Loop
    oTgt.Range("A1:F1").EntireColumn.AutoFit
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble pasting into Excel (Excel 2003)

    Thanks Much. It works like a charm. I really appreciate the help.

    M

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •