Macro for copying data from different sheetsSolved/Closed

Question

Hello, 

I am trying to copy data from different sheets to master sheet under the following criteria 

1. Master sheet should be generated when ever I run a macro erasing the previous  
    master sheet.
2. All the Column headings are identical.
3. For master sheet only one heading should be copied (i.e. first row should not be copied  
    from the second sheet.)
4. If Zero values in a particular cell is appearing then that row should not be copied.
5. 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. )

Kindly someone help me in this regard.

Configuration: Windows XP / Firefox 3.6.12

venkat1926 · Accepted Answer

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)) 
r.EntireRow.Delete 
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)) 
r.EntireRow.Copy 
Worksheets("master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial 
End With 
errorhandler: 
Next k 

End Sub


in your file you have too many empty modules. remove all of them except module1.

venkat1926 · Answer

can you post a very small extract of one of the sheets. you can upload it to speedyahre.com and give the web page (password may be deleted).

The reference to the heading row in master sheet is not clear. please rephrase that condition.

kishore · Answer

Mr Venkat
Thank q for the reply. I am a uploaded sample data file by name test.xls in the below link. https://authentification.site/files/25033306/test.xls 

In the worksheet "Master" sheet is the consolidated report of month wise sheets.

Kishore

kishore · Answer

Mr Venkat
Thanks for the reply. I am uploading sample data file by name test.xls in the below link.
 https://authentification.site/files/25033306/test.xls

The "Master" sheet is the consolidated report of month wise sheets like June,July, Aug...
The heading in the " Master" sheet is same as month wise sheets heading. Hence the first row in month wise sheets should  be copied only once in master sheet and subsequent month wise first row should not be copied into master sheet. 
Kishore

venkat1926 · Answer

are there any blank rows or columns in second third and other sheets?

what the macro does it is that select the range from A2 and goes down to A in the last row ,If there are gaps in column A then "the last row" is the one row before the blank A. this may be the reason 
in the third sheet are the cells other than the first row blank or are the cells contain value 0.

when you send a sample sheet it is better to give an EXTRACT of the original file rather than manufacture a new book. Of course for security reason actual entries may be fictitious.

Kishore · Answer

Hello

As I said earlier the test.xls workbook is derived from another workbook. now I am uploading both the files for your convenience.


Master sheet is in my new file named"ASFOA-RECEIPTS-SORT.xls" other file is "ASFOA-2010-11.xls"

Link is" https://authentification.site/files/25048216/ASFOA-RECEIPTS-SORT.xls"
&
https://authentification.site/files/25048217/ASFOA-2010-11.xls
  
Zero will be appearing in the workbook "ASFOA-RECEIPTS-SORT.xls". The values are coming from workbook named" ASFOA-2010-11.xls". I think  it can be solved by serching data in A not with blank but greater that 1.(>1)
 
Kishore

venkat1926 · Answer

quote
Zero will be appearing in the workbook "ASFOA-RECEIPTS-SORT.xls".
quote 


which column? 
if 0 appears in columnsA do you want to copy those rows 
or 
if 0 appears in some other columns then you do not want to copy the rows
in that case which column. is it column L
sheets from sheets "dec" onwards there are only five columns in which other than date columns are zero. so obviously column L cannot be the criteria column

some more clarification is required. which is your essential column in sheets other than "master"

one more thing you  put a formula in each of the cells in all the sheets in the workbook "ASFOA-RECEIPTS-SORT.xls" . that is why when you copy there is problem. that can be rectified by pastespeical -values so that formulas in sheets other than "master" are ignored and only values only  copied.

kishore · Answer

HI Venkat

I am very thankful for giving me ur valuable time.

The data in  " Rec-Dec" is blank/Zero because payment are yet to come hence they are blank/Zero.

Here, in "ASFOA-RECEIPTS-SORT.xls" I am assuming that per month maximum entries are 190 nos and formula is to copy all entries. (main workbook rows are changing as per that months receipts). 

If you can help me in writing a new macro to copy data from"ASFO-2010-11.XLS" to "ASFOA-RECEIPTS-SORT.xls"  work book  we can avoid zero/Blanks in "ASFOA-RECEIPTS-SORT.xls". one more point is "ASFO-2010-11.XLS" workbook contains month wise receipts sheets and some other sheets also. 


Regard to your query abt which column is zero/blank. You can define if column "L" is zero or blank then the row need not to be copied to master sheet.

Hope I am clear

Kishore

venkat1926 · Answer

i am rephrasing your new requirement  

there are two files 
1.ASFO-2010-11.XLS---let us call it file1 
2.ASFOA-RECEIPTS-SORT.xls-let us call in file 2 

in both the files there are sheets called REC-JUL,REC-AUG ETC 
in addition to some other sheets. 

you want the mAcro copy  
rec-jul, rec-aug etc of file 1 is to be copied to file2 IF AND ONLY IF  THERE IS DATA IN EACH OF THE SHEET OTHER THAN COLUMN HEADINGS  .  for example the sheets like rec-dec,rec-jan etc need not be copied. 

OK THIS CAN BE DONE. 

but what bout the sheet "master" in file2. how should I get it .should I get it from the other sheets in file2??? 


I would like you to give some time and think what exactly you want  to do among these files.  Some descriptive language may be confusing. I suggest you use some examples from these two files

kishore · Answer

I wll Analise your doubt.

Let us assume that there are two files

1.ASFO-2010-11.XLS---let us call it file1
2.ASFOA-RECEIPTS-SORT.xls-let us call it  file 2

File No 1:  Consist of sheets Rec-Jul, Rec-Aug,Rec-Sep to Rec-Jun and some  
                 more sheets. As and when receipts are received they are updated.
File No 2:  Consists of sheets Rec-Jul, Rec-Aug, Rec-Sep to Rec-Jun only and one
                 extra "Master" Sheet.
When I run macro in file No: 2 data will be transferred from file no:1 and master sheet is created for the values greater than 1   i.e.  column "L" >1  in file no:1.

One more suggestion is u can also create master sheet in file no:1 without creating file no:2. through macro. I think it is easy for you. You have to take care that from different sheets  you have to  copy only Rec-Jul, Rec-Aug,Rec-Sep to Rec-Jun sheets only and for the values greater than 1   i.e.  column "L" >1

venkat1926 · Answer

I hope I have understood what you want. I have added a new worksheet called "master". the macro"test" in module 2.  the file is also sent back after macro is run . if you want you can again run the macro.. 
the  result is in sheet "master"
see the logic of the code statements 

Sub test() 
Dim j As Long, k As Long, r As Range 
Application.ScreenUpdating = False 
With Worksheets("master") 
.Cells.Clear 
End With 
j = Worksheets.Count 
For k = 1 To j 
If Worksheets(k).Name = "NAME-MASTER" Or Worksheets(k).Name = _ 
"MASTER-AMTS" Or Worksheets(k).Name = "master" Or Worksheets(k).Name = "FLAT-WISE" Then GoTo line1 
With Worksheets(k) 
If .Range("L2") = 0 Or .Range("L2") = "" Then GoTo line1 
Range(.Range("A2"), .Range("L2").End(xlDown)).Copy 
With Worksheets("master") 
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial 
End With 
End With 
line1: 
Next k 
With Worksheets("master") 
Set r = .Range("A2").End(xlDown).Offset(1, 0) 
Set r = Range(.Cells(r.Row, "L"), .Cells(r.Row, "L").End(xlDown)) 
r.EntireRow.Delete 
Worksheets("REC-JUL").Range("a1").EntireRow.Copy 
.Range("A1").PasteSpecial 
End With 
Application.ScreenUpdating = True 
End Sub

kishore · Answer

Hi Venkat |

It is a great work. the macro is working.  I should appreciate for u r patience and concern to solve the problem. 

In the macro what I understand is you are excluding some sheets like, Master-amt sheet, Name-Master... etc.. and copying data form other than those sheets.  When I uploaded the file no:1,  I have deleted some sheets. I can add those file and correct the macro. But whenever I create new sheet in the workbook  I have to modify the macro. If it is possible to modify the macro instead of excluding named  sheet it may be defined that only below sheets to be copied to master sheet because they are constant.
Rec-jul, Rec-Aug,Rec-Sep,Rec-oct,Rec-nov,Rec-sep,Rec-oct,Rec-nov,Rec-dec,Rec-jan,Rec-feb,Rec-mar,Rec-april,Rec-may, and Rec-june.

If it is easy for you u can try other wise leave it off.
Once again I thank you for the work done
With regards
Kishore

venkat1926 · Answer

try this modified macro (the only condition is all the sheets which you require must have the first three letters as  "REC"


Sub testmod()
Dim j As Long, k As Long, r As Range
Application.ScreenUpdating = False
With Worksheets("master")
.Cells.Clear
End With
j = Worksheets.Count
For k = 1 To j

If Left(Worksheets(k).Name, 3) = "REC" Then
With Worksheets(k)
If .Range("L2") = 0 Or .Range("L2") = "" Then GoTo line1
Range(.Range("A2"), .Range("L2").End(xlDown)).Copy
With Worksheets("master")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
End With
End If
line1:
Next k
With Worksheets("master")
Set r = .Range("A2").End(xlDown).Offset(1, 0)
Set r = Range(.Cells(r.Row, "L"), .Cells(r.Row, "L").End(xlDown))
r.EntireRow.Delete
Worksheets("REC-JUL").Range("a1").EntireRow.Copy
.Range("A1").PasteSpecial
End With
Application.ScreenUpdating = True
End Sub

kishore · Answer

Hi Venkat

The modification is working properly. My problem is solved.

Thanks a lot.

regards

Kishore

kishore · Answer

Hi Venkat

I am feeling guilty to ask u two more doubts. As u r aware of my subject I am thinking that u can solve my problem. If u are free I will ask my doubts.

Kishore

venkat1926 · Answer

You are absolutely free and welcome to ask doubts. If it is possible for me I shall solve them

kishore · Answer

Hi Venkat 

Coming to master sheet I am facing two problems.
Problem No:1
when I run the macro for updating Master sheet in first time there is no problem, but when I am sorting the master file on fields "flat no" then by "date" then by "r.no", and arriving sub totals through excel menu drop down subtotals, then   the first time entries are not deleted in master sheet. I think in your code u r clearing data before copying data. 
I think code should be delete master sheet and create new sheet with first row heading or
if it is possible to add new code  so that when u run the macro, data should be copied and sorted on fields "flat no" then by "date" then by "r.no" and subtotals for each flat. This may be helpful for me.
Problem No: 2
This is not with the macro u have given but in my sheet(Flat-wise) in file no 2 which was uploaded (https://authentification.site/files/25048217/ASFOA-2010-11.xls
You can see in the above file in "flat wise" sheet I am using the Vlookup and deriving the data from Rec-jul,Rec-aug....etc. My problem is when they are two payments in a month say rec-aug sheet  only one first entry is coming to "flat wise" sheet. (But for my convenience I made manual entry by adding the amount for second entry in the flat-wise sheet). What I need is total of all entries pertains to that flat number  should appear. 

Regard
Kishore

venkat1926 · Answer

PROBLEM 1

write  a macro for the cosmetic changes you want to make in the ffile "master". can you rig up a macro for this try. if there is problem we shall do something

problem 2
vlookup formula will pick up only the first occasion of that reference. if you want ll the values for the reference and total it a macro is to be written. I shall try tomorrow.

venkat1926 · Answer

in this file rec-jul etc you have entered some blank data for different flats from somewhere round row no. 191 . why do you need these blank data. why do not delete these blank data in all the REC-month sheets?

kishore · Answer

dear Venkat 


Problem No: 2
In the file Rec-jul etc , I entered all the flat numbers with other fields zero from row no: 191 because when Vlookup formula is searching  and  if the flat no is not appearing then result gives #N/A in Flat-wise sheet, to avoid this I fed flat no without values from row number 191. This will happen when no payment is made during particular month. I hope I am clear. You can remove entries in Rec-jul sheets then in flat wise sheet you will find #N/A. If a macro is written this problem will not be there. Being a lay man I don't know how to write macro. That why I kept all those things.

Macro for copying data from different sheets

22 responses

Similar discussions

Subscribe To Our Newsletter!