Writing a macro Solved/Closed

Question

Hello, 

I am trying to get data whcih has been inoutted into one workksheet to copy into another worksheets when it meets a command in one of the columns. I beleieve i need to write a macro for this although i have no macro experience whatsoever.
Can you please help? :)

rizvisa1 · Answer

You question does not have too much detail to give you a good answer. How ever, you can get a good idea of macro if you use the macro recorder of excel. Start the macro, then do what you want to do and stop the macro.This would give you a starting template.

Anna · Answer

Here is the link to direct you to my sample workbook. 

 

https://authentification.site/files/22476709/SAMPLE.xls

 

Many thank

 

Anna.

Anna · Answer

sorry, please try this link

https://authentification.site/files/22617731/SAMPLE.xls

rizvisa1 · Answer

you can try below. This is based on assumption that 

a. if the Project Number does not exist on Sheet Contract, the data is to be copied on the row below the last used cell in column A.

b. if the Project Number does exist on Sheet Contract, the data is to be copied over the existing data.

1. Press ALT + F11 to start VBE
2. Press CTRL + R to show Project Explorer
3. Double Click on the Sheet Project Tracker
4. Paste this code


Private Sub Worksheet_Change(ByVal Target As Range)
Dim lAtRow As Long
Dim lThisRow As Long
Dim Cell As Object

    Application.EnableEvents = False
    
    On Error GoTo Error_Handle
    
        For Each Cell In Target
        
            lThisRow = Cell.Row
            
            If ((lThisRow > 10) And (Cell.Column = 13) And (UCase(Cell) = "Y")) Then
                            
                lAtRow = 0
                
                On Error Resume Next
                
                    lAtRow = Application.WorksheetFunction.Match(Cells(lThisRow, "A"), Sheets("Contracts").Range("A:A"), 0)
                   
                On Error GoTo Error_Handle
                
                If lAtRow < 1 Then lAtRow = Sheets("Contracts").Cells(Rows.Count, "A").End(xlUp).Row + 1
                
                Sheets("Contracts").Range("A" & lAtRow & ":I" & lAtRow) = Range("A" & lThisRow & ":I" & lThisRow).Value
                
            End If
        
        Next Cell
    
END_SUB:
    
    Application.EnableEvents = True
    Exit Sub
        
Error_Handle:
    MsgBox Err.Description
    GoTo END_SUB
    
End Sub

Writing a macro

4 responses

Similar discussions

Subscribe To Our Newsletter!