Macro for copying data from different sheets [Solved/Closed]

Kishore - Nov 4, 2010 at 01:37 PM - Latest reply:  NK
- Jan 16, 2015 at 02:10 AM
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.

See more 

23 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Nov 5, 2010 at 10:10 PM
2
Thank you
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.

Thank you, venkat1926 2

Something to say? Add comment

CCM has helped 1900 users this month

Thanks a lot.

The macro is working properly in the test.xls file that was uploaded, but when I copied the same macro to my original file the following errors are coming.

1. The first sheet is correctly copied with 119 rows but while copieing the second
sheet, it is starting from row no: 120 (i.e after 120 row balance leftover rows
in the second sheet are copied.
2. From the third sheet only zeros are copied.
3. All other data in remaining sheets are not copied into the master sheet.

In addition I would like to tell you that my original workbook contains 12 months separate sheets and the original file is derived file from another file, hence blank will be there. To avoid this zero problem, we may insert one more clause that only value greater than 1 only to be copied i.e column "L" which is >1.
Kindly help me in this regard.
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Nov 6, 2010 at 07:06 AM
1
Thank you
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.
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Nov 4, 2010 at 09:12 PM
0
Thank you
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.
0
Thank you
Mr Venkat
Thank q for the reply. I am a uploaded sample data file by name test.xls in the below link. http://www.speedyshare.com/files/25033306/test.xls

In the worksheet "Master" sheet is the consolidated report of month wise sheets.

Kishore
0
Thank you
Mr Venkat
Thanks for the reply. I am uploading sample data file by name test.xls in the below link.
http://www.speedyshare.com/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
0
Thank you
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" http://www.speedyshare.com/files/25048216/ASFOA-RECEIPTS-SORT.xls"
&
http://www.speedyshare.com/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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Nov 6, 2010 at 11:35 AM
0
Thank you
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.
0
Thank you
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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Nov 8, 2010 at 05:48 AM
0
Thank you
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
0
Thank you
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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Nov 9, 2010 at 05:04 AM
0
Thank you
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 
0
Thank you
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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Nov 9, 2010 at 09:10 PM
0
Thank you
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
0
Thank you
Hi Venkat

The modification is working properly. My problem is solved.

Thanks a lot.

regards

Kishore
0
Thank you
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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Nov 10, 2010 at 08:58 PM
0
Thank you
You are absolutely free and welcome to ask doubts. If it is possible for me I shall solve them
0
Thank you
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 (http://www.speedyshare.com/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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Nov 11, 2010 at 04:11 AM
0
Thank you
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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Nov 11, 2010 at 04:34 AM
0
Thank you
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?
0
Thank you
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.
1 2 Next