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.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,979
Office Version
  1. 365
Platform
  1. Windows
Untested but try
Code:
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

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

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

New Member
Joined
Aug 6, 2018
Messages
25

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,979
Office Version
  1. 365
Platform
  1. Windows
Did it clear the "Master Req" sheet to begin with & did it then copy the correct info across?
 

shauste

New Member
Joined
Aug 6, 2018
Messages
25

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,979
Office Version
  1. 365
Platform
  1. Windows
Ok, in that case where does your data start & will that column have values for every used row?
 

shauste

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,979
Office Version
  1. 365
Platform
  1. Windows
Which is the 1st column with data?
& will that column always have data in the last used row?
 

Watch MrExcel Video

Forum statistics

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