Take data from one sheet and paste it in another

elopez · May 28th 2005

Hello,
I have a worksheet that has meeting dates and another that is a real simple calendar. I'm looking for a way for the VBA to cycle through the column of dates and search for that day on the calendar and then paste the meeting in that particular day. It's been awhile since I've done something this detailed so my mind is failing on how to approach this. I've tried recording the excel find feature but I don't remember how to have it cycle through a column. I've attached what I've done so far.

mhabib · May 28th 2005

Re: Take data from one sheet and paste it in another

Hi elopez,

The code below seems to work for me. You can incorporate it into your workbook by pasting it to Sheet1's code window

Code

Private Sub Worksheet_Calculate()
    Dim Sht1 As Worksheet
    Dim Sht2 As Worksheet
    Dim rngCal As Range
    Dim rngAppt As Range
    Dim i As Integer
    Dim msgTxt As String
    
    Set Sht1 = Sheets("Sheet1")
    Set Sht2 = Sheets("Sheet2")
    
    Sht1 _
        .Range("B15:AA20,B22:AA27,B29:AA34,B36:AA41,B43:AA48,B50:AA55") _
        .ClearContents
    
    For Each rngCal In _
        Sht1.Range("B14:AA14,B21:AA21,B28:AA28,B35:AA35,B42:AA42,B49:AA49")
        
        If rngCal <> "" Then
            
            i = 1
            
            For Each rngAppt In Sht2.Range("E2", Sht2.Range("E65536").End(xlUp))
                If rngAppt = rngCal Then
                    If i > 6 Then
                        msgTxt = "AARG - Too Many Appointments on " & _
                            Format(rngCal, "dd-mmm-yyyy") & vbCr & vbCr & _
                            "macro terminated"
                        MsgBox msgTxt
                        Exit Sub
                    End If
                    rngCal.Offset(i, 0).Value = _
                        Format(rngAppt.Offset(0, -2).Value, "hh:mm") & " ** " & _
                        rngAppt.Offset(0, -4).Value & " ** " & _
                        rngAppt.Offset(0, -3).Value
                        
                    i = i + 1
                End If
            Next rngAppt
        End If
    Next rngCal
End Sub

Display More

HTH

m

elopez · Jun 14th 2005

Re: Take data from one sheet and paste it in another

mhabib,
I'm sorry it has taken me so long to get back to you but this is a side project I'm doing. The code works great though, thank you very much.
elopez :rock: :thanx:

mhabib · Jun 16th 2005

Re: Take data from one sheet and paste it in another

elopez,

I'm the one who should be thanking you. I've incorporated the same code into my trip planner. At first I was puzzled that you were'nt using MS Outlook to schedule your meetings. But as I saw in my own case, some things are best left with Excel.

Regards,

m

Take data from one sheet and paste it in another

Participate now!

Share