Can anyone help with a Macro please

B

Butch-196

New Member
Joined
Sep 17, 2013
Messages
27
Hi <o:p></o:p>
<o:p> </o:p>
Please can anyone help?<o:p></o:p>
<o:p> </o:p>
I need a Macro writing that will open the two spread sheets (Books 1 & Book 2) to do the task explained below. I will run the macro from a spread sheet already open and as I have several spread sheets to copy information from (Book 2 Spread Sheet) and into the new (Book 1 Spread sheet), I will input the different file paths / names into cells within the open spread sheet with the marco. I would then like to be able to run the macro which will open the two spread sheets and complete the task below. <o:p></o:p>
I have two spread sheets Book 1 & 2. <o:p></o:p>
Book 1 has three columns filled in:- Number, Name and Street. <o:p></o:p>
Book 2 has all columns filled in:- Number, Name, street, wages 1, wages 2 etc.<o:p></o:p>
<o:p> </o:p>
I need the macro open both Book 1 & 2, confirm the information in the Number, Name and Street is the same and if so put the wages information into the relevant cells of spread sheet Book 1. If the Number, Name and Street are different then the wages columns should be left blank in Book 1.<o:p></o:p>
<o:p> </o:p>
See below.<o:p></o:p>
<o:p> </o:p>
Book 1<o:p></o:p>
<o:p> </o:p>[TABLE="width: 635"]
<TBODY>[TR]
[TD]House Number</SPAN>[/TD]
[TD]Name</SPAN>[/TD]
[TD]Street </SPAN>[/TD]
[TD]Wages 1</SPAN>[/TD]
[TD]Wages 2</SPAN>[/TD]
[TD]Wages 3 </SPAN>[/TD]
[/TR]
[TR]
[TD]12</SPAN>[/TD]
[TD]Smith</SPAN>[/TD]
[TD]Coronation Street </SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13</SPAN>[/TD]
[TD]Smith</SPAN>[/TD]
[TD]Coronation Street </SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14</SPAN>[/TD]
[TD]Roberts</SPAN>[/TD]
[TD]Coronation Street </SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL span=3></COLGROUP>[/TABLE]

<v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 450.75pt; HEIGHT: 47.25pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_4 type="#_x0000_t75" o:spid="_x0000_i1027"><v:imagedata o:title="" src="file:///C:\DOCUME~1\smithr\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.emz"></v:imagedata></v:shape><o:p></o:p>
<o:p> </o:p>
Book 2<o:p></o:p>
<o:p> [TABLE="width: 635"]
<TBODY>[TR]
[TD]House Number[/TD]
[TD]Name</SPAN>[/TD]
[TD]Street </SPAN>[/TD]
[TD]Wages 1</SPAN>[/TD]
[TD]Wages 2</SPAN>[/TD]
[TD]Wages 3 </SPAN>[/TD]
[/TR]
[TR]
[TD]12</SPAN>[/TD]
[TD]Smith</SPAN>[/TD]
[TD]Coronation Street </SPAN>[/TD]
[TD="align: right"]25</SPAN>[/TD]
[TD="align: right"]35</SPAN>[/TD]
[TD="align: right"]45</SPAN>[/TD]
[/TR]
[TR]
[TD]13</SPAN>[/TD]
[TD]Jones </SPAN>[/TD]
[TD]Coronation Street </SPAN>[/TD]
[TD="align: right"]35</SPAN>[/TD]
[TD="align: right"]45</SPAN>[/TD]
[TD="align: right"]46</SPAN>[/TD]
[/TR]
[TR]
[TD]14</SPAN>[/TD]
[TD]Roberts</SPAN>[/TD]
[TD]Coronation Street </SPAN>[/TD]
[TD="align: right"]52</SPAN>[/TD]
[TD="align: right"]53</SPAN>[/TD]
[TD="align: right"]54</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL span=3></COLGROUP>[/TABLE]
</o:p></SPAN>
<v:shape style="WIDTH: 450.75pt; HEIGHT: 47.25pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_5 type="#_x0000_t75" o:spid="_x0000_i1026"><v:imagedata o:title="" src="file:///C:\DOCUME~1\smithr\LOCALS~1\Temp\msohtmlclip1\01\clip_image003.emz"></v:imagedata></v:shape><o:p></o:p>
<o:p> </o:p>
Book 1 after running macro.<o:p></o:p>
<o:p> [TABLE="width: 635"]
<TBODY>[TR]
[TD]House Number[/TD]
[TD]Name</SPAN>[/TD]
[TD]Street </SPAN>[/TD]
[TD]Wages 1</SPAN>[/TD]
[TD]Wages 2</SPAN>[/TD]
[TD]Wages 3 </SPAN>[/TD]
[/TR]
[TR]
[TD]12</SPAN>[/TD]
[TD]Smith</SPAN>[/TD]
[TD]Coronation Street </SPAN>[/TD]
[TD="align: right"]25</SPAN>[/TD]
[TD="align: right"]35</SPAN>[/TD]
[TD="align: right"]45</SPAN>[/TD]
[/TR]
[TR]
[TD]13</SPAN>[/TD]
[TD]Smith</SPAN>[/TD]
[TD]Coronation Street </SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14</SPAN>[/TD]
[TD]Roberts</SPAN>[/TD]
[TD]Coronation Street </SPAN>[/TD]
[TD="align: right"]52</SPAN>[/TD]
[TD="align: right"]53</SPAN>[/TD]
[TD="align: right"]54</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL span=3></COLGROUP>[/TABLE]
</o:p></SPAN>
<v:shape style="WIDTH: 450.75pt; HEIGHT: 47.25pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_6 type="#_x0000_t75" o:spid="_x0000_i1025"><v:imagedata o:title="" src="file:///C:\DOCUME~1\smithr\LOCALS~1\Temp\msohtmlclip1\01\clip_image005.emz"></v:imagedata></v:shape><o:p></o:p>
<o:p> </o:p>
Thanks in expectation of some help!<o:p></o:p>
<o:p> </o:p>
Regards<o:p></o:p>
Richard<o:p></o:p>
 
Sort by date Sort by votes
Is there any specific need for this to be done via macro?

If you have excel 2007 or newer, you can use the =sumifs function.

Therefore In book1, cell D2, enter the following

=sumifs(book2!D:D,book2!$a:$A,book1!$A2,book2!$B:$B,book1!$B2,book2!$C:$C,Book1!$C2)
 
Upvote 0
Thanks
This works, however it put a zero in the wages column and I need it to remain blank if no wages are in Book 2 wages column.

I will record the macro once I have the formula, I have a lot of spread sheets to check and would like to have a macro so I can pass the work down!


Regards
Richard :)
 
Upvote 0
This should work:

=if(sumifs(book2!D:D,book2!$a:$A,book1!$A2,book2!$B:$B,book1!$B2,book2!$C:$C,Book1!$C2)=0,"",sumifs(book2!D:D,book2!$a:$A,book1!$A2,book2!$B:$B,book1!$B2,book2!$C:$C,Book1!$C2))

I would assume that there won't be any wages in Book 2 entered as 0? I.e. they will either have a number >0 or be blank?
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Find the row when a sum on a column with certain values on rows is >= of a value
Replies
7
Views
1K
XOR LX
XOR LX
H
  • Question Question
Vba to change Excel file Properties like Title, Description, Fiscal, Period, file
Replies
0
Views
988
hemant_sonawane
H
S
  • Question Question
Correct Formulas for Mathematical Equations.
Replies
3
Views
859
siash69
S
M
  • Question Question
Counting between 2 worksheets where 1 is filtered.
Replies
0
Views
613
M4rek
M
A
  • Question Question
VLOOKUP Formula and ranking
Replies
8
Views
18K
astrophys
A

Share this page

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