Microsoft Excel - NEED macro for complie 3 sheets

Asked By anu anu on 09-Apr-14 12:13 AM

hi ,
i have one excel sheet one is main sheet where the base data and 3 sheet A,B,C in main sheet whole data unique value in column B in every sheet i need one macro to complie all data of column AA,AB,AD,AE from A,B,.C sheets against B column value in main sheet ,without any changes in main file only put data of column AA,AB,AD,AE of A.B.C sheets
right now i am useing v lookup but every time i use lookup on file after that if value is not able against unique value then for only i put lookup from other sheet .
sample file attached.
thanks
Anu

Harry Boughen replied to anu anu on 09-Apr-14 04:38 PM

Hello Anu,
No sample file.
Harry

anu anu replied to Harry Boughen on 14-Apr-14 12:03 AM

hi harry,
Sample file.zip.

i need one condition if in main file already fill value in aa, cell then. No need to vlookup fir that perticuler cell.as value already fill .only. for blank cell in main file collect value from branches sheets.

thanks Anu.

anu anu replied to Harry Boughen on 14-Apr-14 12:05 AM

Anu file.zip

Pls ignore sample file , pls concider anu file as a sample file.

thanks
Anu

Harry Boughen replied to anu anu on 14-Apr-14 04:06 AM

Hello Anu,
Try this. Just click on the Update button
usha_04_14.zip
Regards
Harry

anu anu replied to Harry Boughen on 14-Apr-14 05:27 AM

hi harry ,

it`s working fine . can we change unique value column , achully my unique value in A Column in all Sheets.

thanks
Anu

Harry Boughen replied to anu anu on 14-Apr-14 05:44 AM

Hello Anu
In four places in the macro change this "B2:B" to "A2:A"
Harry

anu anu replied to Harry Boughen on 14-Apr-14 06:06 AM

hi harry ,

when i changed B2:B in macro , AA column and AD Column value caputre but AB And AE colum value not capture in main sheet .

thanks
Anu.

Harry Boughen replied to anu anu on 14-Apr-14 06:19 AM

Sorry about that Anu,
I rushed in without thinking.
You also have to increase the numbers in the offsets by one. So 25 becomes 26, 26 becomes 27 etc.Regards
Harry

anu anu replied to Harry Boughen on 18-Apr-14 01:39 AM

hi harry ,

it`s working fine. you are the magic box.....

thanks
Anu

anu anu replied to Harry Boughen on 18-Apr-14 02:29 AM

hi harry ,

is this possible if AA column fill then in Af column calculate (AA-Z) and in AG (AF*M ). and if AA column blank then calculation not calculate in Af & AG .

thanks
Anu.

Harry Boughen replied to anu anu on 18-Apr-14 05:44 AM

Hello Anu
Add these two lines to each of the three for each loops just before the End If statement

mCell.Offset(0, 31).Value = mCell.Offset(0, 26).Value - mCell.Offset(0, 25).Value
mCell.Offset(0, 32).Value = mCell.Offset(0, 31).Value * mCell.Offset(0, 12).Value

This only puts the calculated values in the Main sheet when there are new values to add. It will not do anything if there are pre-existing values.
Regards
Harry

anu anu replied to Harry Boughen on 20-Apr-14 11:47 PM

hi harry,

it `s calculate value fine only thing i needed when AA column blanks than value not to be calculate .

thanks
anu

Harry Boughen replied to anu anu on 21-Apr-14 03:01 AM

Hello Anu,
I don't quite understand. Do you mean to say that it is possible that column AA on the branch pages can be empty even though there is a match on the unique identifier (column A)?
Harry

anu anu replied to Harry Boughen on 21-Apr-14 03:18 AM

hi harry ,,

i mean after complie the all 3 branch data in main sheet if AA column fill then calculation done other wise not .

like in main file after run the macro should be :

A

B

3

4

M

Z

AA

AF

AG

dsad-12

dsad

dsds

0.46

3818

4563

745

342.7

erewr-12

rtr

rt

0.38

323698

if AA fill with any value then calculate AA-Z in AF colomn and AG (AF*M)
other wise if AA blank then not calculated

right now when i run the macro it shows

A	B	3	4	M	Z	AA	AF	AG
dsad-12	dsad-12	dsad	dsds	0.46	3818	4563	745	342.7
erewr-12	erewr-12	rtr	rt	0.38	323698		-323698	-123005

thanks
Anu

Harry Boughen replied to anu anu on 21-Apr-14 03:22 AM

Hello Anu,
There must be a value in AB then.
Harry

Harry Boughen replied to anu anu on 21-Apr-14 03:32 AM

Hello again Anu,
Try replacing the last two assignments in the three for each loops with this code
        If mCell.Offset(0, 26).Value <> 0 Then
          mCell.Offset(0, 31).Value = mCell.Offset(0, 26).Value - mCell.Offset(0, 25).Value
          mCell.Offset(0, 32).Value = mCell.Offset(0, 31).Value * mCell.Offset(0, 12).Value
        End If
Haven't tested it but should work. Sorry about the confusion before.
Regards
Harry

anu anu replied to Harry Boughen on 21-Apr-14 05:05 AM

hi harri

error is coming,

error.zip

thanks Anu

Harry Boughen replied to anu anu on 21-Apr-14 05:13 AM

Hello Anu,
You seem to be missing the End If from the code that I asked you to put in. There should be two End If on consecutive lines in each location.
Harry

anu anu replied to Harry Boughen on 21-Apr-14 06:13 AM

thanks a lot!!!!! Harry....you are awesome ..

Regards
Anu

anu anu replied to Harry Boughen on 25-Apr-14 05:22 AM

hello harry ,

i use this macro for other file . now in this new file main file is same but in branch file .unique vale same A column and the some value in C and D & E , now i need in branch C column data fill in main file column AC And D column data in AD And E column data in AG

thanks
Anu

Harry Boughen replied to anu anu on 25-Apr-14 07:16 AM

Hello Anu,
You can modify the columns copied from and to by changing the offset values in the assignment statements. The offset is the column count to the right of the unique column so Column C would be offset (0,2) etc. If you don't require the calculation statements you can just comment them out or delete them.
Regards
Harry

anu anu replied to Harry Boughen on 30-Apr-14 05:46 AM

hi harry,

thanks a lot ..
harry if in other file in my main file unique value in B column and branch file unique value in A column . is any way out to edit this macro according this .

thanks
anu

Harry Boughen replied to anu anu on 30-Apr-14 08:08 AM

Hello Anu,

Change the Main sheet range assignment to this
Set bMain = Sheet1.Range("B2:B" & Lastrow)

Then every mCell Offset will have to be reduced by 1 ie mCell.Offset(0,25) will become mcell.Offset(0,24) etc

That should be just about it.
Regards
Harry

anu anu replied to Harry Boughen on 07-May-14 07:11 AM

hi harry ,

it`s Done,

now harry can you help on one other file.

I HAVE SOME DATA IN EXCEL SHEET ,sample file is aatache, in both the sheet one value is unique value in bases of unique value i need feching some data from base sheet .maCRO sHEET.zip

thanks
Anu

Harry Boughen replied to anu anu on 07-May-14 05:26 PM

Hello Anu,
Maybe this Pivot table does the sort of thing that you want.
anu_05_14.zip
Regards
Harry

anu anu replied to Harry Boughen on 07-May-14 11:53 PM

hi harry ,

i am useing this pviot table for this . but every time i create the table and copy data for futher . that`s way i need some other way out for this

anu

Harry Boughen replied to anu anu on 08-May-14 12:42 AM

Hello Anu,
Not exactly sure what you mean but with the data entry area set up as a Table (as I have done) when you add data to it the Pivot Table should expand to incorporate the new data.
If this is not what you mean, can you try to explain the problem in a bit more detail so that I can try to understand better what the problem is.
Harry

anu anu replied to Harry Boughen on 08-May-14 01:05 AM

hi harry ,

Actually Every time I create the pivot table for this report and compile issue is this I cant publish in in the pivot table format . I am publish this data like attached format so I need the way out when I put the base data in base file automatically convert data req format month on month .

anu

Harry Boughen replied to anu anu on 11-May-14 08:55 AM

Hello Anu,
This file contains your data sample set up as a table and a pivot table (Sheet2) that has a button to run the Summary macro.
anu_05_14_1.zip
If you add data to the bottom of the table it will autamatically be incorporated into the pivot table when the pivot table is refreshed. The macro does this to ensure that it uses the latest data. The summary will expand as new months are added. But it will break if there are changes to the basic data structure - for instance if there are more than two distinct types in the Counter (M/C) category.
In reality you would probably better off using a database system for this sort of data storage and analysis
Regards
Harry