Add Transpose to line of code

[pwill32]

Hi can anyone help?

I want to add transpose to the Copy ToSht.Range, is there a way to do this?

Private Sub CommandButton3_Click()

    Set FromSht = Sheets("Sheet1")
    Set ToSht = Sheets("Sheet2")
    
    r = 2
    
    While FromSht.Cells(r, "A") <> "" And FromSht.Cells(r, "C") <> ""
        
    r = r + 1
    
    Wend
    
    If FromSht.Cells(r, "A") <> "" Then
        
    FromSht.Cells(r, "C") = 1
        
    FromSht.Range("A" & r & ":B" & r).Copy ToSht.Range("AK20:AL20")
    
    End If

End Sub

I would like the copy to transpose to AK20:AK21


Any help would be apprieciated

Regards

pwill32

[Leith Ross]

Hello pwill32,

You need to use PasteSpecial...

Private Sub CommandButton3_Click() 
     
    Set FromSht = Sheets("Sheet1") 
    Set ToSht = Sheets("Sheet2") 
     
    r = 2 
     
    While FromSht.Cells(r, "A") <> "" And FromSht.Cells(r, "C") <> "" 
         
        r = r + 1 
         
    Wend 
     
    If FromSht.Cells(r, "A") <> "" Then 
         
        FromSht.Cells(r, "C") = 1 
         
        FromSht.Range("A" & r & ":B" & r).Copy 
        ToSht.Range("AK20:AL20").PasteSpecial Paste:=xlPasteAll, Transpose:=True 
         
    End If 
     
End Sub

[pwill32]

Hello pwill32,

You need to use PasteSpecial...

Private Sub CommandButton3_Click() 
     
    Set FromSht = Sheets("Sheet1") 
    Set ToSht = Sheets("Sheet2") 
     
    r = 2 
     
    While FromSht.Cells(r, "A") <> "" And FromSht.Cells(r, "C") <> "" 
         
        r = r + 1 
         
    Wend 
     
    If FromSht.Cells(r, "A") <> "" Then 
         
        FromSht.Cells(r, "C") = 1 
         
        FromSht.Range("A" & r & ":B" & r).Copy 
        ToSht.Range("AK20:AL20").PasteSpecial Paste:=xlPasteAll, Transpose:=True 
         
    End If 
     
End Sub

Hi Leith Ross,

yes that works

thanks for your help,

much apprieciated

pwill32

[Leith Ross]

Hello pwill32,

You should let others know if you have posted your question in other forums. I see that you posted this same question at Mr Excel. I did not see the post until after I had posted my answer. The two answers are the same. However, there was no need for me to post an answer since your question had already been answered.

[Paul_Hossler]

@pwill32 --

For more info about the right way to multi-post ...


http://www.vbaexpress.com/forum/faq...._new_faq_item3

[pwill32]

Hi Leith Ross,

Sorry about that, no disrespect intended, I didn't know I could have links to show I have asked on other forums?
I will make sure I do this in future

Perhaps you could help me? I have just added a post to mrexcel and want to ask the same question here, do I just copy the address in the browser and paste it here?

Regards

pwill32

[pwill32]

Thanks Paul_Hossler

Regards

pwill32

[pwill32]

Hi,
sorry, didn't know about this rule, no disrespect intended.
I have asked this question on another forum and it has been answered.
https://www.mrexcel.com/forum/excel-...line-code.html

regards

pwill32

Hello pwill32,

You should let others know if you have posted your question in other forums. I see that you posted this same question at Mr Excel. I did not see the post until after I had posted my answer. The two answers are the same. However, there was no need for me to post an answer since your question had already been answered.