Macro to Copy Data From 4 Main tabs to a Master tab

[shauste]

My Excel workbook has 4 main tabs, AM – Asset Mgmt, MM – Materials Mgmt, WM – Work Mgmt, and Add’l Req. Each tab has a header (Row 1) that is formatted the same across each sheet except for color. Each column is formatted with the same width across each sheet. Column C on each sheet is labelled with a Requirement ID that would start with RFP-AM-xxx on the AM – Asset Mgmt tab, RFP-MM-xxx on the MM – Materials Mgmt tab, RFP-WM-xxx on the WM – Work Mgmt tab and a combination of REQ-AM-xxx, REQ-MM-xxx, and REQ-WM-xxx on the Add’l Req tab.

I need a macro that will copy every populated row except Row 1 from each sheet to a tab called Master Req, sorted A – Z. Whenever the macro is executed again, the Master Req sheet must clear and repopulate with updated data from the 4 main tabs.

 

[shauste]

The first column with constant data is Column C 'Requirement ID'. Columns A or B may or may not have data. Column C will always have in the last used row on each sheet.

 

[Fluff]

How about

Sub CopyShts()
   Dim Ws As Worksheet, Mws As Worksheet
   
   Set Mws = Sheets("Master Req")
   Mws.UsedRange.Offset(1).ClearContents
   For Each Ws In Sheets(Array("AM – Asset Mgmt", "MM – Materials Mgmt", "WM – Work Mgmt", "Add’l Req"))
      Ws.Range("C1").CurrentRegion.EntireRow.Copy Mws.Range("C" & Rows.Count).End(xlUp).Offset(1, -2)
   Next Ws
   Mws.UsedRange.Sort key1:=Mws.Range("C1"), order1:=xlAscending, Header:=xlYes
End Sub

 

[shauste]

All data appears to have come over to the Master Req sheet but the formatting is all fouled up. Column widths are very narrow.

 

[Fluff]

The format of the Master sheet should not have changed.
That said I noticed that there was a bit missing from the code

      Ws.Range("C1").CurrentRegion[COLOR=#ff0000].Offset(1)[/COLOR].EntireRow.Copy Mws.Range("C" & Rows.Count).End(xlUp).Offset(1, -2)

Otherwise it will copy the headers for each sheet.

 

[shauste]

The formatting is still fouled up. Is there no way to copy over the header just once in Row 1 of the Master Req and have all remaining data fall below?

Here is the current code.

Sub CopyShts()
Dim Ws As Worksheet, Mws As Worksheet

Set Mws = Sheets("Master Req")
Mws.UsedRange.Offset(1).ClearContents
For Each Ws In Sheets(Array("AM Asset Mgmt", "MM Materials Mgmt", "WM Work Mgmt", "Additional Req"))
Ws.Range("C1").CurrentRegion.Offset(1).EntireRow.Copy Mws.Range("C" & Rows.Count).End(xlUp).Offset(1, -2)
Next Ws
Mws.UsedRange.Sort key1:=Mws.Range("C1"), order1:=xlAscending, Header:=xlYes
End Sub

<colgroup><col span="2"><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Fluff]

If you format the column widths on the Master sheet, then they should remain like that.
Also the code does not clear the header from row 1 of the master, so there is no need to copy it over again.

 

[shauste]

Ok, I've got that fixed. It seems to work as it should now. Thank you very much.

 

[Fluff]

Glad to help & thanks for the feedback

 

[shauste]

Another question. I have an embedded object (txt file) on a Row in a sheet. When I run the Macro, the embedded object does not stay connected to the proper row. Is there anyway to fix this?

 

[shauste]

Disregard, I figured out how to fix it.

I right clicked on the object > selected Format Object > selected Properties tab > then selected Move and size with cells.