PDA

View Full Version : [SOLVED] Add Transpose to line of code



pwill32
06-30-2017, 12:29 PM
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
06-30-2017, 12:47 PM
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
06-30-2017, 12:52 PM
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
06-30-2017, 01:19 PM
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
06-30-2017, 04:38 PM
@pwill32 --

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


http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

pwill32
07-01-2017, 11:53 AM
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
07-01-2017, 11:54 AM
Thanks Paul_Hossler

Regards

pwill32

pwill32
07-01-2017, 01:04 PM
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 (https://www.mrexcel.com/forum/excel-questions/1012258-add-transpose-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.