+ Reply to Thread
Results 1 to 9 of 9

Need macro to edit data - select, copy, transpose, repeat

  1. #1
    Registered User
    Join Date
    10-06-2012
    Location
    Belford, NJ
    MS-Off Ver
    Excel 2003
    Posts
    5

    Need macro to edit data - select, copy, transpose, repeat

    I have data in column A with 5 rows of info for each name stacked on top of each other that I need to separate into columns.

    A12 Joe
    A13 Smith
    A14 27
    A15 2/23/85
    A16 Carpenter
    A17 Pete
    A18 Jones
    and so on

    I want to copy the first 5 rows and move to column B and paste transpose the date to columns B to F.
    Then move to the next 5 rows and repeat each time I call the macro with a button or keyboard shortcut. The starting point has to be where I put the cursor for the first time which could be A17 or A84 or anywhere else. I've looked a little and can't find out to move the cursor. I used to do this in Quattro Pro.

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Need macro to edit data - select, copy, transpose, repeat

    See if this will work

    Please Login or Register  to view this content.
    Last edited by mike7952; 10-07-2012 at 01:25 PM.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    10-06-2012
    Location
    Belford, NJ
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Need macro to edit data - select, copy, transpose, repeat

    Mike7952,
    Thanks for your help

    I got a runtime error '13':

    Type mismatch

    It copied the data but also left the cursor at the starting point.
    Also what is the easiest way to run this macro from the spreadsheet?

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517
    What line had the error? Can u upload the workbook ur working with. Just replace confidential with somethin

  5. #5
    Registered User
    Join Date
    10-06-2012
    Location
    Belford, NJ
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Need macro to edit data - select, copy, transpose, repeat

    This line was highlighted in yellow when I tried to debug.
    I will upload workbook shortly.

    Cells(i, 2).Resize(, 5) = Application.Transpose(Range("a" & i, "a" & i + 4).Value)

  6. #6
    Registered User
    Join Date
    10-06-2012
    Location
    Belford, NJ
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Need macro to edit data - select, copy, transpose, repeat

    It works perfectly in the sample workbook. I think sometimes there is an extra line in the original workbook making 6 rows instead of 5. But why would that make and error? It should just copy the data in the wrong order because of the extra line.

  7. #7
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Need macro to edit data - select, copy, transpose, repeat

    Are any of the cells A thru F in your main workbook merged cells? I would need to see the workbook and debug it.

  8. #8
    Registered User
    Join Date
    10-06-2012
    Location
    Belford, NJ
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Need macro to edit data - select, copy, transpose, repeat

    No, none of the cells are merged. I don't know why it stopped the macro in the original but I got it finished. In the second test sheet, with the confidential info changed, it worked and just copied the extra row that through off the column info but didn't stop it.
    If I only wanted it to do one 5 row section at a time then move the cursor to the next section and stop how could I do that? Thanks so much for your help.

  9. #9
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Need macro to edit data - select, copy, transpose, repeat

    You can try this

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1