Copying data from one excel sheet and pasting into another sheet (Transpose)

[vbadvt]

Hello everyone,

I need to copy data present in a single column from one excel sheet and to paste the data along a single row in another excel sheet.

For example, I have data 'data(1), data(2), data(3).....data(10)' present in column B, in cells (B1, B2,B3,B4,B5,B6,B7,B8,B9,B10) of sheet 1. I need to copy and paste these 10 data into sheet 2, along row 1, say in cells 'A1, B1, C1, D1, E1, F1, G1, H1, I1, J1'. This is similar to transpose operation. I am looking for a 'for loop' to perform this operation. I need to program this in VB6.

Please help me. Thanks

[dglienna]

That IS a Transpose, if you ask me. Record a TRANSPOSE macro in Excel, and then use that. Why re-invent the wheel?

[vbadvt]

Thanks for the reply.

However, there is a small concern, which I tried to mention in my earlier post. I cannot copy all the 10 data, at once, from sheet 1 to sheet 2. This is because it is possible that some of the data may not be present. I need to write a small piece of code that needs to work with different files, which may have this condition.

data(1) should be pasted in A1, data(2) in B1, data(3) in C1, data(4) in D1, data(5) in E1, data(6) in F1, data(7) in G1, data(8) in H1, data(9) in I1 and data(10) in J1. For example, a file may have the following contents in the cells B1 to B9 as , data(1), data(2), data(4), data(5), data(6), data(7), data(8), data(9), data(10). This needs to be transposed and pasted in sheet 2 in the appropriate cells namely: A1, B1, D1, E1, F1, G1, H1, I1, J1. In this case, if I copy all the data, at once, then the data may not be pasted in the corresponding cells on sheet 2, as they may be pasted in A1, B1, C1, D1, E1, F1, G1, H1, I1, instead of what I had posted above (which is what I need). Basically, I would need to perform a search on each string namely data(1), data(2) etc. and if found, need to copy that onto the corresponding cells in Sheet 2.

This is a small example that I have given for understanding purposes. I have hundreds of such data to be transposed from one sheet to another. I believe a 'loop' may be useful but I am unable to move forward.

Looking for help. Thanks

[dglienna]

Wow, who designed EXCEL to be a DB? Not good...

I'd import everything and start again.

[vbadvt]

Import everything and start again?

Could you please explain what you meant? I did not understand.

Thanks

[dglienna]

Access can read Excel files, and import them. Your field names need work, and what you need isn't even clear.

If you can't get transpose to work, then how would you code it?

[vbadvt]

What I need to do is to check if the string 'data(1)' exists in sheet 1 and if it does, then I would need to copy it and transpose it to the other sheet (sheet 2). Similarly, I would need to do this for the rest of the data. So, I am thinking that we would need to use a loop to run the search for different strings 'data(1), data(2)..' where the count variable will be the index value of data. Looking for a code snippet which would perform this action.

In a nutshell, here is what I need:

In Sheet 1, search for string 'data(1)'. If found, then copy it and paste it in the cell 'A1' in Sheet 2.

Go back to sheet 1. Search for string 'data(2)'. If found, then copy it and paste it in the cell 'B1' in Sheet 2.

Go back to sheet 1. Search for string 'data(3)'. If found, then copy it and paste it in the cell 'C1' in Sheet 2.

I need to repeat this action for 100s of data. So, a sample code snippet will be really helpful.

Looking for help. Thanks

[dglienna]

Record a macro doing it once, and post that. Use an IF to change color or something when it's blank, maybe.

You want to look in cell x, and see if it's blank and if not, paste it.

I guess if it's blank, you don't want a blank in sheet 2?

If that's the case, use a formula to delete the blank ones after you transpose

[vbadvt]

Thanks dglienna for your replies. I have recorded a macro of what I exactly need. If I am not able to find a string in sheet 1, then I would need to have an empty cell on sheet 2, corresponding to that string. Here is the macro:

Cells.Find(What:="Student: 1", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Range("D11").Select
Selection.Copy

Sheets("Sheet2").Select
Range("M6").Select

ActiveSheet.Paste
Sheets("Sheet1").Select

Cells.Find(What:="Student: 2", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Range("D12").Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Sheet2").Select
Range("N6").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Cells.Find(What:="Student: 3", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Range("D13").Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Sheet2").Select
Range("O6").Select
ActiveSheet.Paste


Basically I am searching for strings: Student(1), Student(2), Student(3)... etc... These strings are found in Column 'A'. However, I want to copy their marks which is stored in column 'D'. So, I copy columns 'D11', 'D12', 'D13' and so on and paste them in sheet 2 on Row 6. In Sheet 2, Column M, corresponds to student 1, column N corresponds to student 2 and column O corresponds to student 3 and so on. If I do not find info for a particular student, say Student (2), then I should leave the Cell N6, in Sheet 2 blank. This cell corresponds to Student 2.

Hope I have been clear this time and looking forward for help.

Thank you.