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

[nelmsd]

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!

[ragavan.sridar1]

Try this..

Sub try()


x = Sheets("Sheet1").Range("a1").Value
Sheets("Sheet1").Select

Sheets("Sheet1").Range("A2:A4").Select
Selection.Copy

Sheets("Sheet2").Select


    Selection.Find(What:=x, After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
Selection.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
Range("a1").Select





End Sub

[JBeaucaire]

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

Option Explicit

Sub try()
Dim x As String, xFIND As Range

    x = Sheets("Sheet1").Range("a1").Value
    On Error Resume Next
    Set xFIND = Sheets("Sheet2").Cells.Find(x, LookIn:=xlValue, LookAt:=xlPart) 'or xlwhole?
    If Not xFIND Is Nothing Then
        Sheets("Sheet1").Range("A2:A4").Copy
        xFIND.Offset(, 1).pasteall , Transpose:=True
    End If

End Sub

[nelmsd]

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

[nelmsd]

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.

[nelmsd]

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

Sub try()
Dim x As String, xFIND As Range

    x = Sheets("Sheet1").Range("a1").Value
    On Error Resume Next
    Set xFIND = Sheets("Sheet2").Cells.Find(x, LookIn:=xlValue, LookAt:=xlPart) 'or xlwhole?
    If Not xFIND Is Nothing Then
        Sheets("Sheet1").Range("A2:A10").Copy
        xFIND.Offset(, 1).PasteSpecial Transpose:=True
    End If

End Sub

[AB33]

Try

PasteSpecial Transpose:=True

[AB33]

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

Sub try()
 Dim x As String, xFIND As Range
 
x = Sheets("Sheet1").Range("a1").Value
 On Error Resume Next
 
Set xFIND = Sheets("Sheet2").Range("A1:A365").Find(x, , xlValues, xlWhole)
 If Not xFIND Is Nothing Then
 Sheets("Sheet1").Range("A2:A10").Copy
 xFIND.Offset(, 1).PasteSpecial Transpose:=True
 End If
 
End Sub

[nelmsd]

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.

Sub try()
 Dim x As String, xFIND As Range
 
x = Sheets("Sheet1").Range("a1").Value
 On Error Resume Next
 
Set xFIND = Sheets("Sheet2").Range("A1:A365").Find(x, , xlValues, xlWhole)
 If Not xFIND Is Nothing Then
 Sheets("Sheet1").Range("A2:A10").Copy
 xFIND.Offset(, 1).PasteSpecial Transpose:=True
 End If
 
End Sub

[AB33]

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

INTO

Range("A2:A4").

.

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

[AB33]

Could you please attach your sample?

[nelmsd]

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

Sub try()
 Dim x As String, xFIND As Range
 
x = Sheets("Sheet1").Range("A1").Value
 On Error Resume Next
 
Set xFIND = Sheets("Sheet2").Range("A1:A365").Find(x, , xlValues, xlWhole)
 If Not xFIND Is Nothing Then
 Sheets("Sheet1").Range("A2:A4").Copy
 xFIND.Offset(, 1).PasteSpecial Transpose:=True
 End If
 
End Sub

[AB33]

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.

[nelmsd]

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

[nelmsd]

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?

[JBeaucaire]

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

[nelmsd]

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.

[AB33]

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