+ Reply to Thread
Results 1 to 18 of 18

Reference a date, then copy and transpose paste into a date specific location

  1. #1
    Registered User
    Join Date
    02-25-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    9

    Reference a date, then copy and transpose paste into a date specific location

    I want to copy from one sheet in a workbook to another, but reference a date in a cell from the copied sheet and lookup the same date and paste in the corresponding row in another sheet.

    Sheet1 has cell A1 which has the date in it, the subsequent cells A2:A4 have the information I require copying.

    Sheet2 has A1:A365 with every date this year in it (01-Jan - 31-Dec).

    I need it to look up the date from cell Sheet1!A1 in Sheet2!A1:A365, copy cells Sheet1!A2:A4 and transpose paste it in the row next to it so Sheet2!B#:D#

    I'm working on Mac Excel 2011, and I believe it's MS Excel 2000 at work. To be honest if it doesn't work on Mac, I'm not bothered, I'm only trying to test at home before taking it into work tomorrow, but for some reason this has completely thrown me... So any help would be greatly appreciated!

  2. #2
    Forum Contributor ragavan.sridar1's Avatar
    Join Date
    11-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010, Excel 2003
    Posts
    208

    Re: Reference a date, then copy and transpose paste into a date specific location

    Try this..


    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 02-25-2013 at 06:19 PM.
    Thanks!
    Raga.

    Please,mark your thread [SOLVED] if you received your answer.

    Click the little star * below, to give some Rep if you think an answer deserves it.

    I learnt so many things from these links.

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Reference a date, then copy and transpose paste into a date specific location

    Let's try that again without all that recorded "selecting", always need to remove that from recorded macros.

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 02-25-2013 at 06:19 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    02-25-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Reference a date, then copy and transpose paste into a date specific location

    Many thanks Jerry, although I've had a 'compile error: method or data member not found' in specific it's highlighted the .pasteall

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Reference a date, then copy and transpose paste into a date specific location

    Try
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    02-25-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Reference a date, then copy and transpose paste into a date specific location

    And many thanks Raga, although yours has come up with 'named argument not found', but it hasn't prompted the debugger... it had however selected and copied cells Sheet1!A2:A10 and selected Sheet2!B2, but then the error message occurred.

  7. #7
    Registered User
    Join Date
    02-25-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Reference a date, then copy and transpose paste into a date specific location

    AB33 I've just tried and nothings happened no errors, debugger etc

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 02-25-2013 at 07:27 PM. Reason: Added code tags, as per forum rules. Don't forget!

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Reference a date, then copy and transpose paste into a date specific location

    Nelm,
    Please wrap your code with code tags. Could you please edit the above code. Edit-Highlight the code,choose # from the quick reply.

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    02-25-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Reference a date, then copy and transpose paste into a date specific location

    AB33 apologies, it read as follows... still no errors or debugs etc... but nothing seems to be happening.

    Apologies, my Excel is fairly good, but my VB is a little new.

    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Reference a date, then copy and transpose paste into a date specific location

    Well, I have tested it and seems to work, just change the range Range("A2:A10").

    INTO

    Please Login or Register  to view this content.
    .

    I have put 1 in sheet 1 cell 1 and also put the same figure in sheet 2. It found it and copied the range in to sheet 2.
    Make sure that you have value in sheet 1 cell 1

  11. #11
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Reference a date, then copy and transpose paste into a date specific location

    Could you please attach your sample?

  12. #12
    Registered User
    Join Date
    02-25-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Reference a date, then copy and transpose paste into a date specific location

    I've checked all the locations and references just to make sure that everything matches and it does, case sensitive and all. All figures are entered into there correct locations, so there should be no issues in regards to that either, but it still doesn't seem to work.

    I'm guessing it could be my Mac excel as MS neglected a few components when they created this software, so I'll give it a try tomorrow at work and see if their MS version is more successful.

    If you want one last crack today then see attached

    Please Login or Register  to view this content.

  13. #13
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Reference a date, then copy and transpose paste into a date specific location

    You have not attached anything. I think you have a go on you MS and if you still have issues, I will send you my own sample as an attachment. For now, I am falling on my laptop.

  14. #14
    Registered User
    Join Date
    02-25-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Reference a date, then copy and transpose paste into a date specific location

    I'll try MS tomorrow and I'll get back to you... your patience and help is greatly appreciated!

  15. #15
    Registered User
    Join Date
    02-25-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Reference a date, then copy and transpose paste into a date specific location

    AB, I've found the issue! I tried exactly as you stated above and tried 1 in Sheet1 A1, and changed the lookup values in Sheet2 A1:A365 to 1-365 and it worked perfectly, my only issue is doesn't work with dates.

    Sheet1 cell A1 is a date in the dd-mmm format, and the range is likewise on Sheet2 A1 being 01-Jan to A365 being 31-Dec.

    Is there a fix for this?

  16. #16
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Reference a date, then copy and transpose paste into a date specific location

    I would recommend fixing your data so all date fields use the same display format.

  17. #17
    Registered User
    Join Date
    02-25-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Reference a date, then copy and transpose paste into a date specific location

    They all use the custom format of dd-mmm, I've gone behind the dates as well to make sure that the year is correct (as it's obviously hidden in that format) and everything's correct... I've tried it in other date formats as well, so not just a custom format but a 'true' date format i.e. dd/mm/yyyy and it still doesn't work, but the second I switch back to a general format and use numbers 1-365 and value 1 then it works again.

  18. #18
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Reference a date, then copy and transpose paste into a date specific location

    Very strange! Would be easier if you could attach a sample, if not I will do mine?

+ 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