Need Help! Moving Data from sheets to a master sheet

[coyotefest]

Hello All.

I am new to the form and will appreciate any help. A quick overview I have never coded in excel (macros, VBA) but have used MatLab and C so i do have a descent understanding of code.
The easiest way to explain it will be to add the table I'm working with

the first table is the master sheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]Machine #[/TD]
[TD]Serial #[/TD]
[TD]Model[/TD]
[TD]Date installed[/TD]
[TD]Failure date[/TD]
[TD]description[/TD]
[TD]work order[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123[/TD]
[TD]a[/TD]
[TD]5/15/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]456[/TD]
[TD]b[/TD]
[TD]5/15/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]789[/TD]
[TD]c[/TD]
[TD]5/15/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

the next table is and example of one of the machines (each is a separate sheet but follows this format)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Machine #[/TD]
[TD]Serial #[/TD]
[TD]Model[/TD]
[TD]Date installed[/TD]
[TD]Failure date[/TD]
[TD]description[/TD]
[TD]work order[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123[/TD]
[TD]a[/TD]
[TD]5/15/14[/TD]
[TD]8/19/14[/TD]
[TD]hjddjhf[/TD]
[TD]hfsh[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

this table is what I would like to achive


[TABLE="width: 500"]
<tbody>[TR]
[TD]Machine #[/TD]
[TD]Serial #[/TD]
[TD]Model[/TD]
[TD]Date installed[/TD]
[TD]Failure date[/TD]
[TD]description[/TD]
[TD]work order[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123[/TD]
[TD]a[/TD]
[TD]5/15/14[/TD]
[TD]8/19/14[/TD]
[TD]hjddjhf[/TD]
[TD]hfsh[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123[/TD]
[TD]a[/TD]
[TD]5/15/14[/TD]
[TD]9/15/14[/TD]
[TD]hdhs[/TD]
[TD]hjsk[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]345[/TD]
[TD]b[/TD]
[TD]5/15/14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]678[/TD]
[TD]c[/TD]
[TD]5/15/14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



The idea is that the user will type the information (failure date, description, work order) in the sheet assigned to a machine. the data will then be copied over to the new sheet. the machine # serial # model and date installed will not change but should still copy over as scene in the above example. Ideally if the user inputs data into machine 2 i should push the rows below machine 2 down. There are in total 8 machines but if 2 sheets(machines) working I can make the rest work.

I apologize for the lengthiness of the description but i think i have describe every thing.

again I have no background in VBA or macros but do understand code this is just out of my comfort zone.

Thank you all.

 

[WarPigl3t]

I just want to confirm that I understand before I work on this. You have 8 machines. Each machine is on a seperate sheet. You want to copy every row in each machine sheet and paste it to the master sheet. Sound about right?

If so then I'm going to write code that says, For Each worksheet, if it is not the master sheet, copy the range starting in A2 through G last row in table, and paste it to the master sheet.

 

[coyotefest]

First thank you for the quick response.

Yes except that on each machine sheet a2:d2 will only have that line of data. That is collumn a:d will not have any new data but a2:d2 needs to show up each time on the master sheet if data is entred into e:g

 

[WarPigl3t]

So on the machine sheets, every row has the same data in columns A through D right? Because if not, then this has gotten harder than just copy paste.

 

[coyotefest]

Yes that is correct. But each machine sheet has different data for those rows

 

[WarPigl3t]

I'm confused. Here is the code I wrote. Let me know if it's what you wanted.

Sub myMacro()
    master = "Master"  [COLOR=#008000]'The master sheet[/COLOR] 
    
[COLOR=#008000]    '### Erase all data from master sheet except headers###[/COLOR]
    lastRow = Sheets(master).Range("A" & Rows.Count).End(xlUp).Row
    If lastRow > 1 Then
        Sheets(master).Range("A2:G" & lastRow).ClearContents
    End If
[COLOR=#008000]    '######################################################
    
    '### Copy Paste code ###[/COLOR]
    masterNextRow = 2
    For Each WS In ActiveWorkbook.Worksheets
        If WS.Name <> master Then
            lastRow = Sheets(WS.Name).Range("A" & Rows.Count).End(xlUp).Row
            If lastRow > 1 Then
                Sheets(WS.Name).Range("A2:G" & lastRow).Copy
                ActiveSheet.Paste Destination:=Sheets(master).Range("A" & masterNextRow)
                Application.CutCopyMode = False
                masterNextRow = Sheets(master).Range("A" & Rows.Count).End(xlUp).Row + 1
            End If
        End If
    Next WS
  [COLOR=#008000]  '#######################[/COLOR]
End Sub

 

[coyotefest]

YES!! Thank you This is exactly what Im looking for.
The only thing is, and this cold be my lack of understand of how macros work. But it seems that if I add data to one of the machine sheets that the only way to up date the master is to delete all the data in the master then run the code again. This works but can do i have to delete the data from the master every time i update.

 

[WarPigl3t]

No you do not need to manually delete the data before you run the macro. The code I gave you already deletes the data in the master sheet before it begins the copy pasting. You see where I labeled it
'### Erase all data from master sheet except headers###</pre>

 

[coyotefest]

lol you are right my mistake..

Again thank you very much you saved the day.