Writing a macro

[Solved/Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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? :)

4 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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.
Apologies for the lack of information. Basically sheet 1 contains a full list of data, the command will be a Y in one of the columns so when this is inserted i want the whole row to be copied and transferred into another worksheet, worksheet 2. The purpose of this is so the gneral details can be copied across andthen i will expand on this in the different sheet for those that apply. I have no idea how to write a macro therefore its difficult to make a start.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Are you saying that if a row contains a Y in any column of that row, you want this whole row to be copied to a sheet ?

IF that is the case, what about this other sheet. Will it always exist, should the existing data on the sheet be wiped out or this new data be appended

Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee.
Here is the link to direct you to my sample workbook.



https://authentification.site/files/22476709/SAMPLE.xls



Many thank



Anna.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You would need to explain more based on sample file. Still I am not able to connect what you want and how sheets are related
Project tracker contains the a summary of the data, in order for me to expand on the contracts data i want to continue with this on sheet two. Hence if i had a macro which would automactically copy the data from cell A to I within the project tracker to the contracts worksheet provided there is a Y in the contracts column, this will provide the basic for me to continue. I undrestand at the moment some of the cells in column M, worksheet 1 contain a Y and some other data, i believe this would have to be consistant. I hope this helps
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
What was the text of the last message that you got ?
It is working!!! Message said "about to copy" I am SO pleased.

Just one thing, can i take the message off the other cells, which say "about to start"?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
use the original code

or from the code in the book that i gave you, remove the lines that says

MsgBox ,,,,

except for the last one that will be saying
MsgBox Err.Description
Thank you so so much for helping me with this!!! I really do appreciate the time, effort and resilience you have provided. I would have never got there without you!! Thanks again!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
you are very welcome. Good luck with the assignment.