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.

 

[Fluff]

Untested but try

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

 

[shauste]

Thank you for that.

Received the following error, however.

Run-time error '9':

Subscript out of range.


Debug shows the issue with this line:

For Each Ws In Sheets(Array("AM – Asset Mgmt", "MM – Materials Mgmt", "WM – Work Mgmt", "Add’l Req"))

 

[Fluff]

That suggests that one of those sheet names is wrong.
If they look ok, check for leading/trailing spaces & the spaces either side of the hyphen.

 

[shauste]

I removed the dashes in each tab name and updated the macro accordingly. I get a new error of:

Run-time error '1004':
Sort method of Range class failed

Debug shows:

Mws.UsedRange.Sort key1:=Mws.Range("A1"), order1:=xlAscending, Header:=xlYes

 

[Fluff]

Did it clear the "Master Req" sheet to begin with & did it then copy the correct info across?

 

[shauste]

No, it didn't clear the sheet nor did any info come across.

 

[Fluff]

Ok, in that case where does your data start & will that column have values for every used row?

 

[shauste]

The 4 main tabs all have data starting on row 2. Row one needs to happen just once on the Master Req tab, following by the data in the other tabs. The rows filled will vary between the sheets but they will have have data starting on Row 2.

 

[Fluff]

Which is the 1st column with data?
& will that column always have data in the last used row?