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

 

[My Aswer Is This]

Normally when we use Transpose it's to copy values in a column and Paste them into a row or the other way around.

Like in this example I'm copying the value in Sheet(1) Range"A1:A10") To Sheet(2).Row(1)

Sub Transpose_Me()
'Modified  9/30/2018  3:37:51 AM  EDT
Sheets(1).Range("A1:A10").Copy
Sheets(2).Range("A1").PasteSpecial xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End Sub

Maybe this will help you.

 

[My Aswer Is This]

If you just want to copy values with no formatting from numerous different cells to another sheet you could use something like this.

Sub Copy_Numerous_Cells()
'Modified  9/30/2018  3:57:55 AM  EDT
With Sheets(2)
    .Cells(1, 1).Value = Sheets(1).Cells(2, 2).Value
    .Cells(1, 4).Value = Sheets(1).Cells(1, 2).Value
    .Cells(4, 1).Value = Sheets(1).Cells(2, 6).Value
    .Cells(5, 6).Value = Sheets(1).Cells(5, 2).Value
End With
End Sub

 

[Daroh]

Thanks, for the quick reply. I aim to copy and paste the values from sheet2 to sheet1 and if there is any data in the cells in sheet1, this will be replaced by the data from sheet 2.

 

[My Aswer Is This]

So can you read the script?

Learning Excel will be good for you.

I just gave you some examples.

Change the script. You should be able to do that.

 

[My Aswer Is This]

You said:

I aim to copy and paste the values from sheet2 to sheet1 and if there is any data in the cells in sheet1, this will be replaced by the data from sheet 2.

Are you saying you want to clear all the values in sheet(1)
And copy all the data in sheet(2) to sheet(1)

If that's the case the best thing to do would be to delete Sheet(1) completely and make a Copy of sheet(2)

Is that what you want?
​

 

[footoo]

Sub Copy_Numerous_cells()
Dim rng1 As Range, rng2 As Range, i%, cel As Range
Set rng1 = Sheets("Sheet1").[A8,B8,A16,B16] 'Change cell refs as required
Set rng2 = Sheets("Sheet2").[D2,F2,E2,G2]   'Change cell refs as required
i = 1
For Each cel In rng1
    cel = rng2.Areas(i).Value
    i = i + 1
Next
End Sub

 

[Daroh]

Thanks, My Aswer Is This and footoo for your help. It worked perfect.

My Aswer Is This, let me try explain clearer.

Sheet1 (A1:Q30) is a program that I print for customers. The rows in Sheet2 has the information for Sheet1. Based on a cell value, a name that is selected from drop-down list, the information will be transfered from Sheet2 to Sheet1 and overwrite the cell value.

 

[My Aswer Is This]

OK. Thanks.

Thanks, My Aswer Is This and footoo for your help. It worked perfect.

My Aswer Is This, let me try explain clearer.

Sheet1 (A1:Q30) is a program that I print for customers. The rows in Sheet2 has the information for Sheet1. Based on a cell value, a name that is selected from drop-down list, the information will be transfered from Sheet2 to Sheet1 and overwrite the cell value.

 

[Daroh]

Hi, My Aswer Is This and footoo, I have a follow up question.

I am trying to copy data from sheet3 based on a value "Sets" in R7 across that row up to column 57 .

If True I want to copy data from row 8 to 43 from columns: 5,9,13,17,24,28,32,36,43,47,51,55. Once copied, I want to paste the data in order (R8:43 C5, R8:43 C9 and so on) and transpose it to sheet1 in the next blank row.

I have tried many different ways to no avail. Any help or suggestions would be appreciated.

Thanks.