在下一个空白行的另一张工作表中复制并粘贴(转置后的值) [英] Copy and Paste (transposed & values) a set range in a different sheet in the next empty row

问题描述

我在名为Training Analysis的工作表上的 P1:R13 范围内有一些数据.我想将这些数据复制并粘贴在另一个名为Foglio1的工作表上.我希望它只是 values .我需要将这些数据粘贴到 A2:M4 范围内，换句话说，我希望将其转置.我得到以下代码，并且可以正常工作.但是现在，当我获得新数据时，我需要将其粘贴到已经拥有的数据下.

I have some data in range P1:R13 on a sheet called Training Analysis. I want to copy and paste these data on a second sheet called Foglio1. I want it to be just values. I need these data to be pasted in a range A2:M4, in other words I want it to be transposed. I got the following code and it is working. But now, when I get new data I need to paste them under those I already have.

Sub add()
    Dim lastrow As Long
    lastrow = Sheets("Foglio1").Range("A65536").End(xlUp).Row    ' or + 1
    Range("P1:R13").Copy Destination:=Sheets("Foglio1").Range("A" & lastrow)
End Sub

它确实有空白，但我不知道如何更改它以使其转置数据并仅给我值.你能帮我改变吗?如果您有新选择，也可以.干杯

It does the empty space but I don't know how to change it to make it transpose the data and give me only values. Can you help me change it ? If you have new options its fine too. Cheers

推荐答案

遇到这样的问题时，您需要做的是记录一个宏，了解它的工作原理，然后清理代码.

What you need to do when you have a question like this is to record a macro, understand how it works and then clean up the code.

这是您在手动完成所需操作并记录下来后所得到的:

This is what you will get after doing what you need manually and recording it:

Range("P1:R13").Select
Selection.Copy
Sheets("Foglio1").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True

清理一下并添加确定的最后一行后，这就是您应该得到的:

After you clean it up a bit and add determining the last row this is what you should get:

Dim lastRow As Long
Sheets("Training Analysis").Range("P1:R13").Copy

lastRow = Sheets("Foglio1").Range("a65536").End(xlUp).Row

Sheets("Foglio1").Range("A" & lastRow + 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True

在这种特殊情况下，您不知道需要使用PasteSpecial方法，但这是可以的:您不必完全记住整个Excel对象模型.在这种情况下，您可以使用记录，清理和修改"方法.

In this particular case you didn't know that you need to use the PasteSpecial method but this is okay: you don't need to remember the entire Excel object model by heart. You can use the 'record, clean up and modify' method whenever you are in a situation like this.

这篇关于在下一个空白行的另一张工作表中复制并粘贴(转置后的值)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！