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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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"))
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Did it clear the "Master Req" sheet to begin with & did it then copy the correct info across?
 
Upvote 0
Ok, in that case where does your data start & will that column have values for every used row?
 
Upvote 0
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.
 
Upvote 0
Which is the 1st column with data?
& will that column always have data in the last used row?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top