Macro to copy data from multiple worksheets into 1 worksheet

[Amstrad]

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.

 

[Phanmore]

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

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

 

[Amstrad]

Works a treat. Thanks very much

 

[slimano]

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

 

[slimano]

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>

 

[Phanmore]

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

 

[slimano]

Hi Phanmore,

It worked perfectly the way i described it.

Thanks a bunch!!!

Slimano

 

[slimano]

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

 

[2011everafter]

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.

 

[wwilli41]

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.
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Sheet 1[/TD]
[TD="width: 64"]Sheet2[/TD]
[TD="width: 64"]Sheet3[/TD]
[TD="width: 64"]Sheet4[/TD]
[/TR]
[TR]
[TD]B9[/TD]
[TD]B9[/TD]
[TD]B9[/TD]
[TD]B9[/TD]
[/TR]
[TR]
[TD]B11[/TD]
[TD]B11[/TD]
[TD]B11[/TD]
[TD]B11[/TD]
[/TR]
[TR]
[TD]B13[/TD]
[TD]B13[/TD]
[TD]B13[/TD]
[TD]B13[/TD]
[/TR]
[TR]
[TD]B15[/TD]
[TD]B15[/TD]
[TD]B15[/TD]
[TD]B15[/TD]
[/TR]
</tbody>[/TABLE]

Any help is greatly appreciated.

Wes