MsOf07 Macro to match cell value from two sheets and paste where the value is met-VBForums
Results 1 to 5 of 5

Thread: Macro to match cell value from two sheets and paste where the value is met

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    3

    Question Macro to match cell value from two sheets and paste where the value is met

    I'm new to VBA, I'm using Microsoft Office Excel 2007 and I read the forums but this seems impossible for me. I have the current code which copies a sheet and adds a day to the date and also copies a range of cells containing the important information from the sheet to be able to paste it in a calendar with realtime information and I need it to paste where the date is the same and one cell below the value which could be located in any place in a certain range.
    Code:
    Sub CopierPetete()
     ActiveWorkbook.ActiveSheet.Copy _
            After:=ActiveSheet
        'update date
            [J1].Value = [J1].Value + 1
    
       'THIS IS MY POOR ATTEMPT TO MAKE IT WORK
        If Sheets("Sheet5").Range("A1:K100").Value = ActiveSheet.Range("J1").Value Then _  
    
            ActiveSheet.Range("AA100:AC121").Select
                Selection.Copy
            Sheets("Sheet5").Select
                Sheets("Sheet5").Pictures.Paste Link:=True
    
        End If
    
    End Sub
    I need it to match the value in the activesheet cell "J1" with any cell on "Sheet5", and paste "ActiveSheet.Range("AA100:AC121")" as a picture link (Or if you have a better idea for a way to display real-time information) at the place where the value is met between ActiveSheet "J1" and any cell between the range "A1:K100" on "Sheet5", one cell below. PLEASE help me, I posted this on other forums and no one was able to help me with it.

    Here is a link to the project http://www.mediafire.com/?olsvdd22dxmslrz
    Thank you!
    Last edited by Gevelegian; Feb 3rd, 2012 at 02:41 AM.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,003

    Re: Macro to match cell value from two sheets and paste where the value is met

    i doubt if sheets("sheet5").pictures is a valid target for a paste operation (or indeed valid at all)
    (unless pictures is a single cell named range)

    i do not understand from your description where you want to paste to
    if you are pasting copied range, the target should be a single cell which will be the top left of the pasted range
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    3

    Re: Macro to match cell value from two sheets and paste where the value is met

    Quote Originally Posted by westconn1 View Post
    i doubt if sheets("sheet5").pictures is a valid target for a paste operation (or indeed valid at all)
    (unless pictures is a single cell named range)

    i do not understand from your description where you want to paste to
    if you are pasting copied range, the target should be a single cell which will be the top left of the pasted range
    it's not sheets5.pictures it's the range AA100:AC121 and it works perfectly that's not the problem, the problem is HOW to make it paste in the location of "Sheet5" where J1 in the active sheet meets the same value as any cell in sheet5 and pastes it at that location, you can download the project to see what i'm talking about. So to make things more simple, paste the "AA100:AC121" range from the active sheet in sheet5 where J1 from the active sheet is the same (it's a date) so make it paste where the date is the same in sheet5 with active sheet and paste the target below the date that matches so it puts like a review of the active sheet depending on it's date, one cell below (Offset 0, 1)

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,003

    Re: Macro to match cell value from two sheets and paste where the value is met

    from what i understand, you can try like
    vb Code:
    1. for each c in Sheets("Sheet5").Range("A1:K100")
    2. if c = ActiveSheet.Range("J1").Value Then
    3.   ActiveSheet.Range("AA100:AC121").copy c.offset(1)
    4.   exit for
    5. end if
    6. next
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    3

    Re: Macro to match cell value from two sheets and paste where the value is met

    Quote Originally Posted by westconn1 View Post
    from what i understand, you can try like
    vb Code:
    1. for each c in Sheets("Sheet5").Range("A1:K100")
    2. if c = ActiveSheet.Range("J1").Value Then
    3.   ActiveSheet.Range("AA100:AC121").copy c.offset(1)
    4.   exit for
    5. end if
    6. next
    The problem was fixed using the find function and activating the cell by using the rFind.address
    Thank you very much for your reply

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.