Macro to copy data from multiple worksheets into 1 worksheet

Amstrad

New Member
Joined
Feb 17, 2011
Messages
23
I have a spreadsheet that has hundreds of different worksheets. Within each worksheet there is a single row of data from cell A2 to J2.

I want to copy this row from each worksheet into a single worksheet. I also want it to copy the name of the worksheet into column A and then paste the data next to it. That way, I will be able to see which worksheet the data is for.

Any help on how I can build a macro to loop through each worksheet and do this would be greatly appreciated.

Thanks for your help.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
This should work for you.

Just add a sheet in to your workbook called Master. Make sure you save the file first or the sheet names will not pull through

Code:
Sub CombineData()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Master" Then
Sht.Select
Range("A:A").Insert
Range("A2").Formula = "=Mid(Cell(""filename"",B1),Find(""]"",Cell(""filename""))+1,255)"
Range("A2").Copy
Range("A2").PasteSpecial Paste:=xlPasteValues
Range("A2:K2").Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sht.Select
Range("A:A").Delete
Else
End If
Next Sht
 
 
End Sub
 
Upvote 0
Hi Phanmore,

Your script works great!

I have a similar problem but there's a slight twist.
I have a workbook containing 5 worksheets labelled Monday through to Friday.

I want to consolidate the data from these 5 sheets into one Master sheet.
However, the number of records vary from day to day, it's not fixed.

At the end of each working day, when run, the macro should move all of that day's records and append to the Master sheet's last row while emptying the original sheet.

Your guidance will be greatly appreciated.

Thanks in advance
 
Upvote 0
Phanmore,

I'm showing you two sheets, Monday and Tuesday, then a third sheet Master, which is a consolidation of Monday, Tuesday, Wednesday, Thursday and Friday in the form i want it to appear.

Basically, the sheets for Monday up to Friday should, at the run of a macro each day, be moved and appended to the bottom of the last record on the Master sheet, and the original sheet should be cleared.

Thanks

Monday

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 107px"><COL style="WIDTH: 97px"><COL style="WIDTH: 91px"><COL style="WIDTH: 102px"><COL style="WIDTH: 99px"><COL style="WIDTH: 100px"><COL style="WIDTH: 125px"><COL style="WIDTH: 89px"><COL style="WIDTH: 111px"><COL style="WIDTH: 112px"><COL style="WIDTH: 139px"><COL style="WIDTH: 120px"><COL style="WIDTH: 106px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">Debit Acct Name</TD><TD style="FONT-WEIGHT: bold">Debit Currency</TD><TD style="FONT-WEIGHT: bold">Debit Amount</TD><TD style="FONT-WEIGHT: bold">Credit Their Ref</TD><TD style="FONT-WEIGHT: bold">Credit Acct No</TD><TD style="FONT-WEIGHT: bold">Credit Currency</TD><TD style="FONT-WEIGHT: bold">COMMISSION.AMT</TD><TD style="FONT-WEIGHT: bold">CHARGE.AMT</TD><TD style="FONT-WEIGHT: bold">Amount Debited</TD><TD style="FONT-WEIGHT: bold">Amount Credited</TD><TD style="FONT-WEIGHT: bold">Local Amount Debited</TD><TD style="FONT-WEIGHT: bold">Local Amt Credited</TD><TD style="FONT-WEIGHT: bold">Processing Date</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>SUNDRY FCY SWIFT PAY ACCT GBP BNK</TD><TD>GBP</TD><TD> </TD><TD>E02111104003</TD><TD style="TEXT-ALIGN: right">1035131029014</TD><TD>GBP</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD>GBP4675.00</TD><TD>GBP4675.00</TD><TD style="TEXT-ALIGN: right">11,263.48</TD><TD style="TEXT-ALIGN: right">11,263.48</TD><TD style="TEXT-ALIGN: right">20110404</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>CWTH SCH FOR LEG DFT PRG AFR</TD><TD>GBP</TD><TD> </TD><TD>E02091103013</TD><TD style="TEXT-ALIGN: right">1035131029014</TD><TD>GBP</TD><TD>GBP98.75</TD><TD>GBP1.03</TD><TD>GBP9974.46</TD><TD>GBP9874.68</TD><TD style="TEXT-ALIGN: right">24,138.69</TD><TD style="TEXT-ALIGN: right">23,897.22</TD><TD style="TEXT-ALIGN: right">20110404</TD></TR></TBODY></TABLE>

Tuesday

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 107px"><COL style="WIDTH: 97px"><COL style="WIDTH: 91px"><COL style="WIDTH: 100px"><COL style="WIDTH: 99px"><COL style="WIDTH: 100px"><COL style="WIDTH: 121px"><COL style="WIDTH: 89px"><COL style="WIDTH: 111px"><COL style="WIDTH: 115px"><COL style="WIDTH: 140px"><COL style="WIDTH: 121px"><COL style="WIDTH: 106px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">Debit Acct Name</TD><TD style="FONT-WEIGHT: bold">Debit Currency</TD><TD style="FONT-WEIGHT: bold">Debit Amount</TD><TD style="FONT-WEIGHT: bold">Credit Their Ref</TD><TD style="FONT-WEIGHT: bold">Credit Acct No</TD><TD style="FONT-WEIGHT: bold">Credit Currency</TD><TD style="FONT-WEIGHT: bold">COMMISSION.AMT</TD><TD style="FONT-WEIGHT: bold">CHARGE.AMT</TD><TD style="FONT-WEIGHT: bold">Amount Debited</TD><TD style="FONT-WEIGHT: bold">Amount Credited</TD><TD style="FONT-WEIGHT: bold">Local Amount Debited</TD><TD style="FONT-WEIGHT: bold">Local Amt Credited</TD><TD style="FONT-WEIGHT: bold">Processing Date</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>GOTOF GLOAN REPAYMENT INT</TD><TD>ABC</TD><TD> </TD><TD>E02161103016</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD>ABC2367.00</TD><TD>ABC2.50</TD><TD>ABC239069.54</TD><TD>EUR111126.78</TD><TD style="TEXT-ALIGN: right">239,069.54</TD><TD style="TEXT-ALIGN: right">237,005.64</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>GOTOF HLOAN REPAYMENT INT</TD><TD>ABC</TD><TD> </TD><TD>E02161103022</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD>ABC250.00</TD><TD>ABC2.50</TD><TD>ABC25252.25</TD><TD>EUR11736.97</TD><TD style="TEXT-ALIGN: right">25,252.25</TD><TD style="TEXT-ALIGN: right">25,032.02</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>GWCL URBAN WATER PRJCEDI AC</TD><TD>ABC</TD><TD> </TD><TD>E02021103032</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD>ABC231.21</TD><TD>ABC2.50</TD><TD>ABC23354.54</TD><TD>EUR10854.85</TD><TD style="TEXT-ALIGN: right">23,354.54</TD><TD style="TEXT-ALIGN: right">23,079.58</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>GWCL URBAN WATER PRJCEDI AC</TD><TD>ABC</TD><TD> </TD><TD>E02021103033</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD>ABC118.44</TD><TD>ABC2.50</TD><TD>ABC11964.95</TD><TD>EUR5560.57</TD><TD style="TEXT-ALIGN: right">11,964.95</TD><TD style="TEXT-ALIGN: right">11,822.88</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>NOGUC MEM INST NO1 USD</TD><TD>USD</TD><TD> </TD><TD>E02021103042</TD><TD style="TEXT-ALIGN: right">1025231029016</TD><TD>USD</TD><TD>USD37.64</TD><TD>USD1.67</TD><TD>USD3803.46</TD><TD>USD3764.15</TD><TD style="TEXT-ALIGN: right">5,701.96</TD><TD style="TEXT-ALIGN: right">5,643.03</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>NOGUI MEM INST NO1 USD</TD><TD>USD</TD><TD> </TD><TD>E02021103027</TD><TD style="TEXT-ALIGN: right">1025231029016</TD><TD>USD</TD><TD>USD22.28</TD><TD>USD1.67</TD><TD>USD2252.33</TD><TD>USD2228.38</TD><TD style="TEXT-ALIGN: right">3,376.58</TD><TD style="TEXT-ALIGN: right">3,340.68</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>FOOD AND DRUG BOARD</TD><TD>USD</TD><TD> </TD><TD>E02021103038</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD>USD192.00</TD><TD>USD1.67</TD><TD>USD19393.67</TD><TD>USD19200.00</TD><TD style="TEXT-ALIGN: right">29,074.02</TD><TD style="TEXT-ALIGN: right">28,783.68</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>SUNDRY FCY SWIFT PAY ACCT EUR BNK</TD><TD>EUR</TD><TD> </TD><TD>E02111103016</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD>EUR28574.25</TD><TD>EUR28574.25</TD><TD style="TEXT-ALIGN: right">60,754.57</TD><TD style="TEXT-ALIGN: right">60,754.57</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>INSTI DOLLAR</TD><TD>USD</TD><TD> </TD><TD>E02021103028</TD><TD style="TEXT-ALIGN: right">1025231029016</TD><TD>USD</TD><TD>USD17.84</TD><TD>USD1.67</TD><TD>USD1803.94</TD><TD>USD1784.43</TD><TD style="TEXT-ALIGN: right">2,704.38</TD><TD style="TEXT-ALIGN: right">2,675.13</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>PUBLIC UTI REULAT COMMI</TD><TD>ABC</TD><TD> </TD><TD>E02021103043</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD>ABC96.13</TD><TD>ABC2.50</TD><TD>ABC9712.07</TD><TD>USD6400.00</TD><TD style="TEXT-ALIGN: right">9,712.07</TD><TD style="TEXT-ALIGN: right">9,594.56</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>DEPT SUB CONSOLIDATED FUND</TD><TD>ABC</TD><TD> </TD><TD>E02081103053</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD>ABC213.00</TD><TD>ABC2.50</TD><TD>ABC21515.50</TD><TD>EUR10000.00</TD><TD style="TEXT-ALIGN: right">21,515.50</TD><TD style="TEXT-ALIGN: right">21,262.00</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>NATIONAL COMMUN AUTHNO1 USD</TD><TD>USD</TD><TD> </TD><TD>E02021103040</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD>USD18660.55</TD><TD>USD1.67</TD><TD>USD1866055.36</TD><TD>USD1847393.14</TD><TD style="TEXT-ALIGN: right">2,797,496.89</TD><TD style="TEXT-ALIGN: right">2,769,519.43</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>MOTIMSME PROJECT</TD><TD>ABC</TD><TD> </TD><TD>E02021103039</TD><TD style="TEXT-ALIGN: right">1025231029016</TD><TD>USD</TD><TD>ABC19.57</TD><TD>ABC2.50</TD><TD>ABC1979.31</TD><TD>USD1303.00</TD><TD style="TEXT-ALIGN: right">1,979.31</TD><TD style="TEXT-ALIGN: right">1,953.39</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>LADJER</TD><TD>ABC</TD><TD> </TD><TD>E02121103011</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD>ABC7.51</TD><TD>ABC2.50</TD><TD>ABC761.06</TD><TD>USD500.00</TD><TD style="TEXT-ALIGN: right">761.06</TD><TD style="TEXT-ALIGN: right">749.58</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>EIGN LAN LODGEMENT REPAY.</TD><TD>ABC</TD><TD> </TD><TD>E02161103021</TD><TD style="TEXT-ALIGN: right">1025131023018</TD><TD>USD</TD><TD>ABC53776.62</TD><TD>ABC2.50</TD><TD>ABC5431442.45</TD><TD>USD3580096.75</TD><TD style="TEXT-ALIGN: right">5,431,442.45</TD><TD style="TEXT-ALIGN: right">5,367,102.04</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>APAAH SDY S</TD><TD>ABC</TD><TD> </TD><TD>E02121103010</TD><TD style="TEXT-ALIGN: right">1035131029014</TD><TD>GBP</TD><TD>ABC4.89</TD><TD>ABC0.50</TD><TD>ABC497.12</TD><TD>GBP201.00</TD><TD style="TEXT-ALIGN: right">497.12</TD><TD style="TEXT-ALIGN: right">488.23</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>GTOF GLOAN REPAYMENT PPL</TD><TD>ABC</TD><TD> </TD><TD>E02051103009</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD>ABC6733.44</TD><TD>ABC2.50</TD><TD>ABC680080.19</TD><TD>USD448268.59</TD><TD style="TEXT-ALIGN: right">680,080.19</TD><TD style="TEXT-ALIGN: right">672,021.86</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR></TBODY></TABLE>

Master

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 108px"><COL style="WIDTH: 98px"><COL style="WIDTH: 94px"><COL style="WIDTH: 102px"><COL style="WIDTH: 96px"><COL style="WIDTH: 100px"><COL style="WIDTH: 124px"><COL style="WIDTH: 90px"><COL style="WIDTH: 107px"><COL style="WIDTH: 112px"><COL style="WIDTH: 142px"><COL style="WIDTH: 122px"><COL style="WIDTH: 106px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">Debit Acct Name</TD><TD style="FONT-WEIGHT: bold">Debit Currency</TD><TD style="FONT-WEIGHT: bold">Debit Amount</TD><TD style="FONT-WEIGHT: bold">Credit Their Ref</TD><TD style="FONT-WEIGHT: bold">Credit Acct No</TD><TD style="FONT-WEIGHT: bold">Credit Currency</TD><TD style="FONT-WEIGHT: bold">COMMISSION.AMT</TD><TD style="FONT-WEIGHT: bold">CHARGE.AMT</TD><TD style="FONT-WEIGHT: bold">Amount Debited</TD><TD style="FONT-WEIGHT: bold">Amount Credited</TD><TD style="FONT-WEIGHT: bold">Local Amount Debited</TD><TD style="FONT-WEIGHT: bold">Local Amt Credited</TD><TD style="FONT-WEIGHT: bold">Processing Date</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>SUNDRY FCY SWIFT PAY ACCT GBP BNK</TD><TD>GBP</TD><TD> </TD><TD>E02111104003</TD><TD style="TEXT-ALIGN: right">1035131029014</TD><TD>GBP</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD>GBP4675.00</TD><TD>GBP4675.00</TD><TD style="TEXT-ALIGN: right">11,263.48</TD><TD style="TEXT-ALIGN: right">11,263.48</TD><TD style="TEXT-ALIGN: right">20110404</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>CWTH SCH FOR LEG DFT PRG AFR</TD><TD>GBP</TD><TD> </TD><TD>E02091103013</TD><TD style="TEXT-ALIGN: right">1035131029014</TD><TD>GBP</TD><TD>GBP98.75</TD><TD>GBP1.03</TD><TD>GBP9974.46</TD><TD>GBP9874.68</TD><TD style="TEXT-ALIGN: right">24,138.69</TD><TD style="TEXT-ALIGN: right">23,897.22</TD><TD style="TEXT-ALIGN: right">20110404</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>GOTOF GLOAN REPAYMENT INT</TD><TD>ABC</TD><TD> </TD><TD>E02161103016</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD>ABC2367.00</TD><TD>ABC2.50</TD><TD>ABC239069.54</TD><TD>EUR111126.78</TD><TD style="TEXT-ALIGN: right">239,069.54</TD><TD style="TEXT-ALIGN: right">237,005.64</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>GOTOF HLOAN REPAYMENT INT</TD><TD>ABC</TD><TD> </TD><TD>E02161103022</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD>ABC250.00</TD><TD>ABC2.50</TD><TD>ABC25252.25</TD><TD>EUR11736.97</TD><TD style="TEXT-ALIGN: right">25,252.25</TD><TD style="TEXT-ALIGN: right">25,032.02</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>GWCL URBAN WATER PRJCEDI AC</TD><TD>ABC</TD><TD> </TD><TD>E02021103032</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD>ABC231.21</TD><TD>ABC2.50</TD><TD>ABC23354.54</TD><TD>EUR10854.85</TD><TD style="TEXT-ALIGN: right">23,354.54</TD><TD style="TEXT-ALIGN: right">23,079.58</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>GWCL URBAN WATER PRJCEDI AC</TD><TD>ABC</TD><TD> </TD><TD>E02021103033</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD>ABC118.44</TD><TD>ABC2.50</TD><TD>ABC11964.95</TD><TD>EUR5560.57</TD><TD style="TEXT-ALIGN: right">11,964.95</TD><TD style="TEXT-ALIGN: right">11,822.88</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>NOGUC MEM INST NO1 USD</TD><TD>USD</TD><TD> </TD><TD>E02021103042</TD><TD style="TEXT-ALIGN: right">1025231029016</TD><TD>USD</TD><TD>USD37.64</TD><TD>USD1.67</TD><TD>USD3803.46</TD><TD>USD3764.15</TD><TD style="TEXT-ALIGN: right">5,701.96</TD><TD style="TEXT-ALIGN: right">5,643.03</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>NOGUI MEM INST NO1 USD</TD><TD>USD</TD><TD> </TD><TD>E02021103027</TD><TD style="TEXT-ALIGN: right">1025231029016</TD><TD>USD</TD><TD>USD22.28</TD><TD>USD1.67</TD><TD>USD2252.33</TD><TD>USD2228.38</TD><TD style="TEXT-ALIGN: right">3,376.58</TD><TD style="TEXT-ALIGN: right">3,340.68</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>FOOD AND DRUG BOARD</TD><TD>USD</TD><TD> </TD><TD>E02021103038</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD>USD192.00</TD><TD>USD1.67</TD><TD>USD19393.67</TD><TD>USD19200.00</TD><TD style="TEXT-ALIGN: right">29,074.02</TD><TD style="TEXT-ALIGN: right">28,783.68</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>SUNDRY FCY SWIFT PAY ACCT EUR BNK</TD><TD>EUR</TD><TD> </TD><TD>E02111103016</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD>EUR28574.25</TD><TD>EUR28574.25</TD><TD style="TEXT-ALIGN: right">60,754.57</TD><TD style="TEXT-ALIGN: right">60,754.57</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>INSTI DOLLAR</TD><TD>USD</TD><TD> </TD><TD>E02021103028</TD><TD style="TEXT-ALIGN: right">1025231029016</TD><TD>USD</TD><TD>USD17.84</TD><TD>USD1.67</TD><TD>USD1803.94</TD><TD>USD1784.43</TD><TD style="TEXT-ALIGN: right">2,704.38</TD><TD style="TEXT-ALIGN: right">2,675.13</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>PUBLIC UTI REULAT COMMI</TD><TD>ABC</TD><TD> </TD><TD>E02021103043</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD>ABC96.13</TD><TD>ABC2.50</TD><TD>ABC9712.07</TD><TD>USD6400.00</TD><TD style="TEXT-ALIGN: right">9,712.07</TD><TD style="TEXT-ALIGN: right">9,594.56</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>DEPT SUB CONSOLIDATED FUND</TD><TD>ABC</TD><TD> </TD><TD>E02081103053</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD>ABC213.00</TD><TD>ABC2.50</TD><TD>ABC21515.50</TD><TD>EUR10000.00</TD><TD style="TEXT-ALIGN: right">21,515.50</TD><TD style="TEXT-ALIGN: right">21,262.00</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>NATIONAL COMMUN AUTHNO1 USD</TD><TD>USD</TD><TD> </TD><TD>E02021103040</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD>USD18660.55</TD><TD>USD1.67</TD><TD>USD1866055.36</TD><TD>USD1847393.14</TD><TD style="TEXT-ALIGN: right">2,797,496.89</TD><TD style="TEXT-ALIGN: right">2,769,519.43</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>MOTIMSME PROJECT</TD><TD>ABC</TD><TD> </TD><TD>E02021103039</TD><TD style="TEXT-ALIGN: right">1025231029016</TD><TD>USD</TD><TD>ABC19.57</TD><TD>ABC2.50</TD><TD>ABC1979.31</TD><TD>USD1303.00</TD><TD style="TEXT-ALIGN: right">1,979.31</TD><TD style="TEXT-ALIGN: right">1,953.39</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>LADJER</TD><TD>ABC</TD><TD> </TD><TD>E02121103011</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD>ABC7.51</TD><TD>ABC2.50</TD><TD>ABC761.06</TD><TD>USD500.00</TD><TD style="TEXT-ALIGN: right">761.06</TD><TD style="TEXT-ALIGN: right">749.58</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>EIGN LAN LODGEMENT REPAY.</TD><TD>ABC</TD><TD> </TD><TD>E02161103021</TD><TD style="TEXT-ALIGN: right">1025131023018</TD><TD>USD</TD><TD>ABC53776.62</TD><TD>ABC2.50</TD><TD>ABC5431442.45</TD><TD>USD3580096.75</TD><TD style="TEXT-ALIGN: right">5,431,442.45</TD><TD style="TEXT-ALIGN: right">5,367,102.04</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD>APAAH SDY S</TD><TD>ABC</TD><TD> </TD><TD>E02121103010</TD><TD style="TEXT-ALIGN: right">1035131029014</TD><TD>GBP</TD><TD>ABC4.89</TD><TD>ABC0.50</TD><TD>ABC497.12</TD><TD>GBP201.00</TD><TD style="TEXT-ALIGN: right">497.12</TD><TD style="TEXT-ALIGN: right">488.23</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD>GTOF GLOAN REPAYMENT PPL</TD><TD>ABC</TD><TD> </TD><TD>E02051103009</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD>ABC6733.44</TD><TD>ABC2.50</TD><TD>ABC680080.19</TD><TD>USD448268.59</TD><TD style="TEXT-ALIGN: right">680,080.19</TD><TD style="TEXT-ALIGN: right">672,021.86</TD><TD style="TEXT-ALIGN: right">20110405</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>SUNDRY FCY SWIFT PAY ACCT GBP BNK</TD><TD>GBP</TD><TD> </TD><TD>E02111104002</TD><TD style="TEXT-ALIGN: right">1035131029014</TD><TD>GBP</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD>GBP3000.00</TD><TD>GBP3000.00</TD><TD style="TEXT-ALIGN: right">7,356.60</TD><TD style="TEXT-ALIGN: right">7,356.60</TD><TD style="TEXT-ALIGN: right">20110406</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD>GH MUSEUMS MONUMENTS BOARD USD</TD><TD>USD</TD><TD> </TD><TD>E02021103047</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD>USD2.86</TD><TD>USD1.67</TD><TD>USD290.79</TD><TD>EUR200.00</TD><TD style="TEXT-ALIGN: right">436.25</TD><TD style="TEXT-ALIGN: right">428.99</TD><TD style="TEXT-ALIGN: right">20110406</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD>HEALTH SYSTEMS RESEARCH PROJEC</TD><TD>USD</TD><TD> </TD><TD>E02021103045</TD><TD style="TEXT-ALIGN: right">1025231029016</TD><TD>USD</TD><TD>USD1.58</TD><TD>USD1.67</TD><TD>USD161.02</TD><TD>USD157.77</TD><TD style="TEXT-ALIGN: right">241.57</TD><TD style="TEXT-ALIGN: right">236.69</TD><TD style="TEXT-ALIGN: right">20110406</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD>NOGU MEM INST MED RES FOREX</TD><TD>USD</TD><TD> </TD><TD>E02021104001</TD><TD style="TEXT-ALIGN: right">1025231029016</TD><TD>USD</TD><TD>USD246.00</TD><TD>USD1.67</TD><TD>USD24847.67</TD><TD>USD24600.00</TD><TD style="TEXT-ALIGN: right">37,277.72</TD><TD style="TEXT-ALIGN: right">36,906.15</TD><TD style="TEXT-ALIGN: right">20110406</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD>APPH LINDA P</TD><TD>ABC</TD><TD> </TD><TD>E02121104001</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD>ABC638.31</TD><TD>USD425.00</TD><TD style="TEXT-ALIGN: right">638.31</TD><TD style="TEXT-ALIGN: right">637.61</TD><TD style="TEXT-ALIGN: right">20110406</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD>FD AND DRG BARD</TD><TD>USD</TD><TD> </TD><TD>E02021104002</TD><TD style="TEXT-ALIGN: right">1035131029014</TD><TD>GBP</TD><TD>USD58.84</TD><TD>USD1.67</TD><TD>USD5951.29</TD><TD>GBP3600.00</TD><TD style="TEXT-ALIGN: right">8,928.41</TD><TD style="TEXT-ALIGN: right">8,827.92</TD><TD style="TEXT-ALIGN: right">20110406</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD>SUNDRY FCY DRAFT PAY ACCT BP BNK</TD><TD>GBP</TD><TD> </TD><TD>E02111103017</TD><TD style="TEXT-ALIGN: right">1035131029014</TD><TD>GBP</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD>GBP6273.00</TD><TD>GBP6273.00</TD><TD style="TEXT-ALIGN: right">15,382.65</TD><TD style="TEXT-ALIGN: right">15,382.65</TD><TD style="TEXT-ALIGN: right">20110406</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD>BANKNOTE PROCESSING MACHINE - DBMS</TD><TD>EUR</TD><TD> </TD><TD>E02111104001</TD><TD style="TEXT-ALIGN: right">1185131029016</TD><TD>EUR</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD>EUR182205.00</TD><TD>EUR182205.00</TD><TD style="TEXT-ALIGN: right">390,820.61</TD><TD style="TEXT-ALIGN: right">390,820.61</TD><TD style="TEXT-ALIGN: right">20110406</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD>SUNDRY FCY SWIFT PAY ACCT USD BNK</TD><TD>USD</TD><TD> </TD><TD>E02111104005</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD>USD1720.00</TD><TD>USD1720.00</TD><TD style="TEXT-ALIGN: right">2,580.43</TD><TD style="TEXT-ALIGN: right">2,580.43</TD><TD style="TEXT-ALIGN: right">20110406</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD>NAT SPS COUNCIL</TD><TD>USD</TD><TD> </TD><TD>E02021103016</TD><TD style="TEXT-ALIGN: right">1065131168018</TD><TD>CHF</TD><TD>USD136.23</TD><TD>USD1.67</TD><TD>USD13804.73</TD><TD>CHF12500.00</TD><TD style="TEXT-ALIGN: right">20,702.26</TD><TD style="TEXT-ALIGN: right">20,438.75</TD><TD style="TEXT-ALIGN: right">20110407</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD>SUNDRY FCY CASH PAY ACCT USD BNK</TD><TD>USD</TD><TD> </TD><TD>E02111104008</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD>USD2800.00</TD><TD>USD2800.00</TD><TD style="TEXT-ALIGN: right">4,199.02</TD><TD style="TEXT-ALIGN: right">4,199.02</TD><TD style="TEXT-ALIGN: right">20110407</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD>AD ALEXANDER</TD><TD>ABC</TD><TD> </TD><TD>E02121104002</TD><TD style="TEXT-ALIGN: right">1025131078017</TD><TD>USD</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD>ABC165.42</TD><TD>USD110.00</TD><TD style="TEXT-ALIGN: right">165.42</TD><TD style="TEXT-ALIGN: right">164.96</TD><TD style="TEXT-ALIGN: right">20110407</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</TD><TD>SUNDRY FCY CASH PAY ACCT GBP BNK</TD><TD>GBP</TD><TD> </TD><TD>E02111104009</TD><TD style="TEXT-ALIGN: right">1035131029014</TD><TD>GBP</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD>GBP546.00</TD><TD>GBP546.00</TD><TD style="TEXT-ALIGN: right">1,342.48</TD><TD style="TEXT-ALIGN: right">1,342.48</TD><TD style="TEXT-ALIGN: right">20110408</TD></TR></TBODY></TABLE>
 
Upvote 0
This willwork, let me know if there's any problems. And if anyone wants to clean up my code a little, feel free.

Sub CombineData()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Master" And Sht.Range("A2").Value <> "" Then
Sht.Select
LastRow = Range("A65536").End(xlUp).Row
Range("A2", Cells(LastRow, "M")).Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sht.Select
Range("A2", Cells(LastRow, "M")).ClearContents
Else
End If
Next Sht


End Sub
 
Upvote 0
Hi Phanmore,

I'm really grateful for your help.
With the solution you provided, how can i change it to read from different workbooks and consolidate into a single Master Workbook.

Let's say I have 5 different workbooks with names Monday up to Friday.
And i want to combine all the data into the Master Workbook.

The file formats remain the same.

Slimano
 
Upvote 0
Hello,

I have 9 worksheets in the workbook and I only want to copy 4 of them to the "Master" sheet , how can I do that.

Thank you.
 
Upvote 0
Hello,

I have tried to conform the first code into what I need but I cannot figure it out.
Here is where I am at,

Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Master" Then
Sht.Select
Range("B9, B11, B13, B15").Copy
Sheets("Master").Select
If (("A65536") > 0) Then .End(xlUp).Offset("0", "1").Select
ActiveSheet.Paste
Sht.Select


Else
End If
Next Sht


End Sub

I need it to select the certain B cells I have listed above but on the Master Page, I need it to paste the values in subsequent columns and not all in one column.
So I need it to look like this.
Sheet 1Sheet2Sheet3Sheet4
B9B9B9B9
B11B11B11B11
B13B13B13B13
B15B15B15B15

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Any help is greatly appreciated.

Wes
 
Upvote 0

Forum statistics

Threads
1,222,045
Messages
6,163,586
Members
451,846
Latest member
ajk99

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