Copy and Paste (transpose)

[Daroh]

Hi,

I have sheets in t (he same workbook that I need the values of the cells to be copied and pasted from sheet2 to sheet1 depending on a name selected from a drop down list. However, the cells in sheet1 are spread over the sheet.

Here is an example of what I have done so far for the first two cells, with 19 more cells to be done.


Sub copy()




Dim sht1 As Worksheet
Dim sht2 As Worksheet


Set sht1 = Worksheets("Sheet1")
Set sht2 = Worksheets("Sheet2")

sht2.Range("D2").copy
sht1.Range("A8").PasteSpecial xlPasteValues, Transpose:=True
sht2.Range("F2").copy
sht1.Range("B8").PasteSpecial xlPasteValues, Transpose:=True
sht2.Range("E2").copy
sht1.Range("A16").PasteSpecial xlPasteValues, Transpose:=True
sht2.Range("G2").copy
sht1.Range("B16").PasteSpecial xlPasteValues, Transpose:=True



Is there a more efficient method to done this?

Any help would be appreciated.

Thanks,
Darryll

 

[footoo]

Sub Copy_Numerous_cells()
Dim f As Range, ray As Variant, i%
Set f = Sheets("Sheet3").[A7].Resize(, 57).Find("Sets")
If f Is Nothing Then Exit Sub
ray = Array(5, 9, 13, 17, 24, 28, 32, 36, 43, 47, 51, 55) 'Change column numbers as required
For i = 0 To UBound(ray)
Sheets("Sheet1").Cells(8, ray(i)).Resize(36) = Sheets("Sheet3").Cells(8, ray(i)).Resize(36).Value
Next
End Sub

 

[Daroh]

Thanks footoo, is it possible to transpose the data copied from Sheet3 to Sheet1 in order as per column?

From this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]sets(1)[/TD]
[TD] sets(2)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


To This: [TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Once again, thank you, for your help.

 

[footoo]

I don't understand what you want to do.

 

[Daroh]

Hi footoo, the data is in the columns on sheet1,and I want to put the data from sheet1 into sheet3 in a horizontal format. For example, the data from Sheet1 - (R8:43, C5) to be copied to the next blank row on sheet3 i.e. Starting in Row 15 extending horizontally. I hope this makes sense. Thanks.

 

[footoo]

I want to put the data from sheet1 into sheet3

In your earlier post you wrote that you want to put from Sheet3 to Sheet1. Which way is it?
Do you want to do only for columns with "Sets" and "Sets"+suffix in row 7 ?
Why "i.e. Starting in Row 15" ?
For ranges E8:E43, I8:I43, etc. on the source sheet, what are the destination ranges on the destination sheet?
What column on the destination sheet can be used to determine the next blank row?