Copy and Paste/Transpose to another sheet

[Leelabird]

Greetings All,

I was just wondering if it is possible to copy a horizontal range (D6:S6) from one sheet (Sheet1), paste it to a cell (P10) on another sheet (Sheet2) but transpose it to the vertical. It is statistical data which is transferred to P10 and down which then has more calculations done in the following columns by formulas which are already in place. The values from P10 down align with their respective formulas in the following rows/columns. Column P is always empty. I've been trying this over a few days now and I can't work this out so I meekly ask for your assistance again
What I've tried so far seems to make the transferred data disappear through the top of the sheet with only one cell value (S6) showing in P1 on Sheet2
If it is possible, would it also be possible for the macro to take the User to Sheet2, P10 on completion of the transfer?

Thank you for any and all help.

Kind regards,
Leela.

[vcoolio]

Hello Leela,

I think that you are pretty familiar with how to implement codes, so try the following:-

Code: Select all

Sub Test()
    
    Sheet1.[D6:S6].Copy
    Sheet2.[P10].PasteSpecial xlValues, Transpose:=True
    
    Application.CutCopyMode = False
    Application.Goto Sheet2.[P10]
    
End Sub

I'm not sure if you are referencing sheet names or sheet codes, so you may have to change the sheet references to suit.

I hope that this helps.

Cheerio,
vcoolio.

[snb]

Code: Select all

Sub M_snb()
  [sheet2!P10:P26]=[transpose(sheet1!D6:S6)].Value
End Sub

[HansV]

@snb:

Shouldn't P10:B26 be P10:P25 ?

[snb]

My permission to adapt to local circumstances. ;)

[HansV]

[Leelabird]

Greetings,

Thank you SNB, HansV and Vcoolio for the replies.

@SNB:
Thank you for taking an interest in helping with my query. Your code returns an "Object Required" error. I used the adjustment that HansV suggested and it returned the same error. I then removed the .Value part at the end of your code and, still using HansV's adjustment, it worked.
The [sheet2!P10:B26] part confused me as, after removing the .Value part, the code returned 15 columns x 17 rows of the transposed data with the 17th row being a #N/A error. So I suppose that it was just a typo?

@HansV:
Thank you for your input. SNB's code worked with your adjustment. I really appreciate it!

@Vcoolio:
Your code worked really well without any errors straight up! Thank you for your help and input as well.

Once again, thank you all very,very much for all the help.

Kind regards,
Leela.

[snb]

Afterwards [sheet2!P10:B26] confuses me as well, since I intended to type [sheet2!P10:P26].
The eye-hand coordination appears to be rather fluffy. So let's start with new contactlenses today.

[vcoolio]

You're welcome Leela. I'm glad that we could assist and thanks for the feed back.

@snb:
Ha. I know how you feel. I just recently had both lenses replaced in my eyes. More or less back to normal now.

Cheerio,
vcoolio.