Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post Need help developing a Macro!

    Hi,

    I have a workbook with 3 sheets, BaselineInfo, New Info, and Status. I have set up sheet "Status" so it pulls information from the baseline and New info sheets. I have the following column headers on my Status sheet, with corresponding cells listed above them.

    Screenshot.PNG

    I have conditional formatting so that when the status from the baseline sheet (Cell B2 above) does not match the status from the new info sheet (Cell F2 above) Column H2 (Time in Staus) Will change to say "Status Changed" as you can see on the last line from above. I would like to develop a macro that when you push a button it will change the status, if column column H2 has changed to "Status Changed", on the BaselineInfo sheet to reflect the new status in column F2 of the "Status" sheet. The idea would be to push the button and the macro will find all cells with "Status Changed" on the Status sheet and then use the corresponding ticket number in column A2 and match that ticket number to the Ticket number to the one on the BaselineInfo sheet, Also in Coulmn A, and change the status, which is in column B on the BaseLineInfo Sheet.

    I hope that is some what clear. I do not know if this is doable or not. Any assistance is greatly appreciated.
    Last edited by JH0131; 2014-12-03 at 12:06.

  2. #2
    New Lounger
    Join Date
    Dec 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Below is what I have so far. As it is now it searches all the ticket numbers and replace the status of the ones that match. I need it to only replace those that have changed. This is indicated on the TicketStatus sheet by column H. It will say Status changed.

    Sub NewStatus()
    Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
    Dim TicketNumber As String
    lastrow1 = Sheets("TicketStatus").Range("A" & Rows.Count).End(xlUp).Row

    For i = 3 To lastrow1
    TicketNumber = Sheets("TicketStatus").Cells(i, "A").Value

    Sheets("Baseline Information").Activate
    lastrow2 = Sheets("Baseline Information").Range("A" & Rows.Count).End(xlUp).Row

    For j = 2 To lastrow2
    If Sheets("Baseline Information").Cells(j, "C").Value = TicketNumber Then
    Sheets("TicketStatus").Activate
    Sheets("TicketStatus").Range(Cells(i, "F"), Cells(i, "F")).Copy
    Sheets("Baseline Information").Activate
    Sheets("Baseline Information").Range(Cells(j, "D"), Cells(j, "D")).Select
    ActiveSheet.PasteSpecial xlPasteValues
    End If

    Next j
    Application.CutCopyMode = False
    Next i
    Sheets("TicketInformation").Activate
    Sheets("TicketInformation").Range("A2").Select
    End Sub

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Is this thread replaced by your later thread or is it an unrelated issue?

    Maud

  4. #4
    New Lounger
    Join Date
    Dec 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It was related. It was the Macro I had already developed. I have completed it now. Below is what I came up with and it works. Would love to know if there is a faster way than the copy and paste.

    Sub NewStatus()
    Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
    Dim TicketNumber As String, Status As String
    lastrow1 = Sheets("TicketStatus").Range("A" & Rows.Count).End(xlUp).Row

    For i = 3 To lastrow1
    TicketNumber = Sheets("TicketStatus").Cells(i, "A").Value
    Status = Sheets("TicketStatus").Cells(i, "H").Value

    Sheets("Baseline Information").Activate
    lastrow2 = Sheets("Baseline Information").Range("A" & Rows.Count).End(xlUp).Row

    For j = 2 To lastrow2
    If Status = "Status Changed" Then
    If Sheets("Baseline Information").Cells(j, "C").Value = TicketNumber Then
    Sheets("TicketStatus").Activate
    Sheets("TicketStatus").Range(Cells(i, "F"), Cells(i, "F")).Copy
    Sheets("Baseline Information").Activate
    Sheets("Baseline Information").Range(Cells(j, "D"), Cells(j, "D")).Select
    Selection.PasteSpecial xlPasteValues
    End If
    End If

    Next j
    Application.CutCopyMode = False
    Next i
    Sheets("TicketStatus").Activate
    Sheets("TicketStatus").Range("A2").Select

    End Sub

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    JH0131,

    There is! Your code code be modified as below to avoid the copy/paste and alternately activating worksheets.

    HTH,
    Maud

    Code:
    Sub NewStatus()
    Application.ScreenUpdating = False
    '-----------------------------
    'DECLARE AN SET VARIABLES
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
    Dim TicketNumber As String
    Set ws1 = Sheets("Baseline Information")
    Set ws2 = Sheets("TicketStatus")
    lastrow1 = ws2.Range("A" & Rows.Count).End(xlUp).Row
    '-----------------------------
    'CYCLE THROUGH ROWS IN TICKETSTATUS-GET TICKET# AND STATUS
    For i = 3 To lastrow1
        TicketNumber = ws2.Cells(i, "A").Value
        Status = ws2.Cells(i, "H").Value
    '-----------------------------
    'CYCLE THROUGH ROWS IN BASELIN INFORMATION AN LOOK FOR MATCH
        lastrow2 = ws1.Range("A" & Rows.Count).End(xlUp).Row
        For j = 2 To lastrow2
            If Status = "Status Changed" Then
                If ws1.Cells(j, "C").Value = TicketNumber Then
                    ws1.Cells(j, "D").Value = ws2.Cells(i, "F")
                End If
            End If
        Next j
    Next i
    ws2.Activate
    ws2.Range("A2").Select
    '-----------------------------
    'CLEANUP
    Set ws1 = Nothing
    Set ws2 = Nothing
    Application.ScreenUpdating = True
    End Sub
    Last edited by Maudibe; 2014-12-03 at 21:08.

  6. #6
    New Lounger
    Join Date
    Dec 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maudibe,
    Thank you very much! That does work faster.

Posting Permissions

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