Hi DocAEstein,
Thank you for your solution. ..........
......Cheers,
Hi,
. Youre welcome.
Marks of the respective subjects are copied in appropriate cells, However, subject name at the top of the cell is not copied from sheet1 to sheet3. I hope I can fix that
Cheers,
. As a beginner I still tend to Type in things like Headings manually, as I did in the File at Filesnack that I did for you.
. But it is fairly straight forward.
. Here the code again which also writes in the headings:
<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Sharon1()<br><br><SPAN style="color:#00007F">Dim</SPAN> UsedRange1 <SPAN style="color:#00007F">As</SPAN> Range <SPAN style="color:#007F00">'Give your usedrange for table1 a name and allow it to have all properties and methods of a range object</SPAN><br><SPAN style="color:#00007F">Set</SPAN> UsedRange1 = Worksheets("Sheet1").Range("A1").CurrentRegion <SPAN style="color:#007F00">' Currentrange property returns the table area.</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> UsedRange1Row <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, UsedRange2Row <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' limit tables to 255 rows</SPAN><br><SPAN style="color:#00007F">Let</SPAN> UsedRange1Row = UsedRange1.Rows.Count <SPAN style="color:#007F00">'This gives you how many rows in table1 that you have</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> UsedRange2 <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Set</SPAN> UsedRange2 = Worksheets("Sheet2").Range("A1").CurrentRegion<br><SPAN style="color:#00007F">Let</SPAN> UsedRange2Row = UsedRange2.Rows.Count<br><SPAN style="color:#00007F">Dim</SPAN> UsedRange1Column <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Limit columns to 255</SPAN><br><SPAN style="color:#00007F">Let</SPAN> UsedRange1Column = UsedRange1.Columns.Count <SPAN style="color:#007F00">'This returns the used Columns in Table 1</SPAN><br><br><SPAN style="color:#00007F">For</SPAN> y = 2 <SPAN style="color:#00007F">To</SPAN> UsedRange2Row <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">'For every row in table 2...</SPAN><br> Worksheets("Sheet3").Cells(y, 1).Value = Worksheets("Sheet2").Cells(y, 1).Value <SPAN style="color:#007F00">' ...Put ID in column 1, ..</SPAN><br> Worksheets("Sheet3").Cells(y, 2).Value = Worksheets("Sheet2").Cells(y, 2).Value <SPAN style="color:#007F00">' ... Put #Unit test in column 2</SPAN><br><SPAN style="color:#00007F">Next</SPAN> y<br><br><SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#007F00">' For Subject Headings starting at column 1 in Sheet3....</SPAN><br> Worksheets("Sheet3").Cells(1, x).Value = Worksheets("Sheet2").Cells(1, x).Value <SPAN style="color:#007F00">'....bring thes in from Sheet 2</SPAN><br><SPAN style="color:#00007F">Next</SPAN> x<br><br><SPAN style="color:#00007F">For</SPAN> x = 3 <SPAN style="color:#00007F">To</SPAN> UsedRange1Column + 1 <SPAN style="color:#007F00">' For Id uand Unit test Headings starting at column 3 in Sheet3....</SPAN><br> Worksheets("Sheet3").Cells(1, x).Value = Worksheets("Sheet2").Cells(2, x).Value <SPAN style="color:#007F00">'....bring thes in from Sheet 2</SPAN><br><SPAN style="color:#00007F">Next</SPAN> x<br><br><SPAN style="color:#00007F">For</SPAN> j = 2 <SPAN style="color:#00007F">To</SPAN> UsedRange2Row <SPAN style="color:#007F00">'for each row in sheet 2...</SPAN><br> <SPAN style="color:#00007F">For</SPAN> i = 3 <SPAN style="color:#00007F">To</SPAN> 5 <SPAN style="color:#007F00">'...go through column 3,4 and 5....</SPAN><br> <SPAN style="color:#00007F">For</SPAN> y = 2 <SPAN style="color:#00007F">To</SPAN> UsedRange1Row <SPAN style="color:#007F00">'... and go through every row in Sheet 1...</SPAN><br> <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Worksheets("Sheet2").Cells(j, i).Value <SPAN style="color:#007F00">' ..then look for Maths, History, or Science</SPAN><br> <SPAN style="color:#00007F">Case</SPAN> "Maths"<br> <SPAN style="color:#00007F">If</SPAN> Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">' If maths then</SPAN><br> Worksheets("Sheet3").Cells(j, 3).Value = Worksheets("Sheet1").Cells(y, 2).Value <SPAN style="color:#007F00">' put appropriate value in column 3, sheet5...etc</SPAN><br> <SPAN style="color:#00007F">Else</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Case</SPAN> "History"<br> <SPAN style="color:#00007F">If</SPAN> Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value <SPAN style="color:#00007F">Then</SPAN><br> Worksheets("Sheet3").Cells(j, 4).Value = Worksheets("Sheet1").Cells(y, 3).Value<br> <SPAN style="color:#00007F">Else</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Case</SPAN> "Science"<br> <SPAN style="color:#00007F">If</SPAN> Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value <SPAN style="color:#00007F">Then</SPAN><br> Worksheets("Sheet3").Cells(j, 5).Value = Worksheets("Sheet1").Cells(y, 4).Value<br> <SPAN style="color:#00007F">Else</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> y<br> <SPAN style="color:#00007F">Next</SPAN> i<br> <SPAN style="color:#00007F">Next</SPAN> j<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'Sharon1()</SPAN></FONT>
The important extra lines are:
<font face=Calibri><SPAN style="color:#00007F">For</SPAN> y = 2 <SPAN style="color:#00007F">To</SPAN> UsedRange2Row <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">'For every row in table 2...</SPAN><br> Worksheets("Sheet3").Cells(y, 1).Value = Worksheets("Sheet2").Cells(y, 1).Value <SPAN style="color:#007F00">' ...Put ID in column 1, ..</SPAN><br> Worksheets("Sheet3").Cells(y, 2).Value = Worksheets("Sheet2").Cells(y, 2).Value <SPAN style="color:#007F00">' ... Put #Unit test in column 2</SPAN><br><SPAN style="color:#00007F">Next</SPAN> y<br><br><SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#007F00">' For Subject Headings starting at column 1 in Sheet3....</SPAN><br> Worksheets("Sheet3").Cells(1, x).Value = Worksheets("Sheet2").Cells(1, x).Value <SPAN style="color:#007F00">'....bring thes in from Sheet 2</SPAN><br><SPAN style="color:#00007F">Next</SPAN> x</FONT>
again without comments etc..
Code:
For x = 1 To 2 Worksheets("Sheet3").Cells(1, x).Value = Worksheets("Sheet2").Cells(1, x).Value
Next x
For x = 3 To UsedRange1Column + 1
Worksheets("Sheet3").Cells(1, x).Value = Worksheets("Sheet2").Cells(2, x).Value
Next x
Alan
P.s. In the practice I would tend to go for the hiker95 code - It is a professional solution and probably works more efficiently etc. Mine can possibly help a bit at the beginner stage to see a bit better wot is going on, and I tend to use the basics as I Don't know the clever stuff yet!