+ Reply to Thread
Results 1 to 14 of 14

Macro for copy & paste (transpose) from one workbook to another

  1. #1
    Registered User
    Join Date
    06-21-2010
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Macro for copy & paste (transpose) from one workbook to another

    Hi,

    I have just been trawling these forums for a macro similar to what I need but can't seem to find anything just right and was wondering if someone can help?

    I have one workbook (lets say workbook A) with lots of measurements taken from x,y,z coordinates for lots of subjects. The measurements I want to copy from each subject (which has its own tab) are in the column H2:H96, and I want to transfer them to another workbook (workbook B), all within the one tab. Instead of copying them in a column, I want to transfer them to rows - specifically the first row being D2:CT2.

    As each subject has its own tab (from FSCC_0001 to FSCC_0484) I am trying to get a macro that can copy H2:H96 from FSCC_0001 and transpose the data before copying it to Workbook B beginning at row D2. Then repeat this process from FSCC_0001 all the way to FSCC_0484.

    One added little drama - I am missing some data! Will this stuff up the macro if say FSCC_0012 is missing?

    Hope this makes sense and someone can help or at least lead me in a closer direction! Thanks in advance!

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    re: Macro for copy & paste (transpose) from one workbook to another

    Hello Drmirafbi,

    Welcome to the Forum!

    Do you need to copy the formatting to workbook "B" or just the data values?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    re: Macro for copy & paste (transpose) from one workbook to another

    Hello ,

    This macro will copy the data values from workbook "A" to workbook "B". You will need to change the name of workbook "A" to what you are using. Copy the macro into a VBA module in workbook "B".
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    06-21-2010
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    re: Macro for copy & paste (transpose) from one workbook to another

    Hi Leith,

    Thankyou so much for your reply. In response to your first msg, yes I just need values (not formulas). I am reasonably new to these macros, but have copied the script you have written with my document names replacing WkbA or WkbB - but when I went to run the macro, it came up with 'Subscript out of Range'.....?

    This is what my script looks like -
    Please Login or Register  to view this content.

    Is this correct? Or do I somehow have to give it the limits of the tabs I have in the workbook?

    Thanks again for your help!



    [QUOTE=Leith Ross;2328176]Hello ,

    This macro will copy the data values from workbook "A" to workbook "B". You will need to change the name of workbook "A" to what you are using. Copy the macro into a VBA module in workbook "B".
    Last edited by Leith Ross; 06-21-2010 at 03:00 PM. Reason: Added Code Tags

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    re: Macro for copy & paste (transpose) from one workbook to another

    Hello Drmirafbi,

    You missed changing one the WkbB variables. The correction is in bold. You will need to change "Data" in the line below to the name of the Adolescent workbook.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-21-2010
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    re: Macro for copy & paste (transpose) from one workbook to another

    Hi Leith,

    Thanks, I have changed WkbB now to FaceData. Despite trying a few names on the options of "data" it still comes up with the same error message. Basically, my original document is called Adolescent.xls and each worksheet within Adolescent is labelled FSCC_0001 onwards. I want to transfer the one column from each FSCC_#### worksheet into a workbook called FaceData, and the worksheet for that is called RawData.

    Currently my code looks as follows:
    Please Login or Register  to view this content.

    I have tried a few different options for the: Set Adolescent = Workbooks("FSCC_0001") but nothing seems to work.

    Sorry if this is something blindingly obvious to you!!!
    Last edited by Leith Ross; 06-21-2010 at 05:23 PM. Reason: Added Code Tags

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    re: Macro for copy & paste (transpose) from one workbook to another

    Hello Drmirafbi,

    Give this a try...
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    06-21-2010
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    re: Macro for copy & paste (transpose) from one workbook to another

    Hi Leith,

    That seems to have fixed the initial problem but now it tells me "Runtime Error '438': Object doesn't support this property or method" and gives me the following highlighted line...

    Please Login or Register  to view this content.


    I'm not sure if I have too many worksheets from the Adolescent.xls for it to work through (Over 400)?

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    re: Macro for copy & paste (transpose) from one workbook to another

    Hello Drmirafbi,

    Unless Excel 2007 no longer supports WorksheetFunction.Transpose, there is no reason you should get that error. It works fine in Excel 2003. Can you put together a smaller version of this workbook and post it?

  10. #10
    Registered User
    Join Date
    06-21-2010
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    re: Macro for copy & paste (transpose) from one workbook to another

    Hi Leith,

    I have attached a copy of both datasheets - the first three worksheets from Adolescent.xls to be copied and transposed into FaceData.xls on the first three rows. Good luck!!

    If it works on yours I have another laptop here running Excel 2003 so I will give that a go as well....

    Cheers!
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    06-21-2010
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    re: Macro for copy & paste (transpose) from one workbook to another

    Hi Leith,

    I have just checked the same macro (same files etc) on the older version of excel & still comes up with the same error message

    Not sure if you've had the chance to look at the above documents yet, but fingers crossed!

  12. #12
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    re: Macro for copy & paste (transpose) from one workbook to another

    Hello Drmirafbi,

    Okay, that was my fault. I forgot to add the worksheet name in for the Face workbook. Here is the corrected macro. I ran it and no errors in 2003. It should work in 2007 as well. I added the macro to the attached workbook.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    06-21-2010
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    [Solved] Macro for copy & paste (transpose) from one workbook to another

    Leith you are a champion!!!! Works perfectly!

    Thankyou soooo much

  14. #14
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    re: Macro for copy & paste (transpose) from one workbook to another

    Hello Drmirafbi,

    Sorry I didn't catch that sooner. If I get to Perth again, we'll have to have a pint or two. Great city.

+ 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