Excel - a Macro for copying data from different sheets

March 2017


I am trying to copy data from different sheets to master sheet under the following criteria
  • Master sheet should be generated whenever I run a macro erasing the previous master sheet.
  • All the Column headings are identical.
  • For master sheet only one heading should be copied (i.e. first row should not be copied from the second sheet.)
  • If zero values in a particular cell are appearing then that row should not be copied.
  • Master sheet should be sorted as per the column i mention in running the macro. (I.e. I will assign on the message box the field/column that should be sorted in the master sheet.)


I have one doubt you want to copy data for all the months to master sheet OR for month by month. I assumed all the months. In that case try this macro
Sub test()   
Dim j As Long, k As Long, r As Range   
j = Worksheets.Count   
With Worksheets("master")   
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))   
End With   

For k = 1 To j   
If Worksheets(k).Name = "master" Then GoTo errorhandler   
With Worksheets(k)   
If .Range("A2") = "" Then GoTo errorhandler   
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))   
Worksheets("master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial   
End With   
Next k   

End Sub

In your file you have too many empty modules. Remove all of them except module1.

Note that

Solved by venkat1926


Published by aakai1056.
This document, titled "Excel - a Macro for copying data from different sheets," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).