Copy data from multiple identical sheets to a consolidation sheet by macro

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
1,023
Office Version
  1. 365
Platform
  1. Windows
I have a workbook consisting of 9 sheets. The layout of each sheet is identical. Sheet 1 (named Combined report) and sheets 2 to 9 will auto-name based on the employees name
which I shall enter into cell A3 as each new sheet is taken into use.

What I would like to do is to have a macro or command button that will copy the contents of each sheet (sheets 2-9) and paste them onto sheet 1 (Combined report) in sheet order, i.e. starting at
cell A4, copy sheet 2, however many lines that may be, and then sheet 3 and so on until all sheets (2 - 9) and copied and pasted.

The data on each sheet to be copied is within the range A4:L4 but the number of rows is variable. It might be one row or 20 rows.

Any help will be very much appreciated!

Mel
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi Logit,

Thanks for the re-direct. I've read through the posts there but wouldn't know where to start to edit it to 'fit' my workbook.

Mel
 
Upvote 0
How about
VBA Code:
Sub MelSmith()
   Dim Ws As Worksheet, wsCom As Worksheet
   
   Set wsCom = Sheets("Combined Report")
   For Each Ws In Worksheets
      If Ws.Name <> wsCom.Name Then
         Ws.Range("A4:L" & Ws.Range("A" & Rows.Count).End(xlUp).Row).Copy wsCom.Range("A" & Rows.Count).End(xlUp).Offset(1)
      End If
   Next Ws
End Sub
This assumes you will always have data in col A for every used row
 
Upvote 0
Hi Fluff,

Thanks for the code. How would I execute it?

Mel
 
Upvote 0
From a button/shape or by using Alt F8 & select the macro
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Hi Fluff,

The code works to a point, unless I've done something wrong.

If, say, sheet 2 has 4 rows and sheet 3 has 7 rows, the macro is only copying the first row of each.

Mel
 
Upvote 0
Does column A have data for every used row?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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