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

shauste

New Member
Joined
Aug 6, 2018
Messages
25
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

New Member
Joined
Aug 6, 2018
Messages
25
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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,981
Office Version
  1. 365
Platform
  1. Windows
How about
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", "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

New Member
Joined
Aug 6, 2018
Messages
25
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,981
Office Version
  1. 365
Platform
  1. Windows
The format of the Master sheet should not have changed.
That said I noticed that there was a bit missing from the code
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

New Member
Joined
Aug 6, 2018
Messages
25

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,981
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 6, 2018
Messages
25

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,981
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

shauste

New Member
Joined
Aug 6, 2018
Messages
25
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

New Member
Joined
Aug 6, 2018
Messages
25
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,779
Messages
5,544,179
Members
410,596
Latest member
JoeyZ
Top