2

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

eli-k
  • 10,898
  • 11
  • 40
  • 44
AlbF
  • 51
  • 1
  • 3
  • 11

2 Answers2

5

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

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.

Alex Kirko
  • 446
  • 3
  • 8
  • This code is working properly, thank you ! I've also tried to record a macro but then i was not able to clean up and modify as you did. – AlbF Dec 04 '15 at 13:42
1

You could shorten it further and try:

Sub add()
    Range("Foglio1!A2:M4").Value2 = Application.WorksheetFunction.transpose(Range("Training Analysis!P1:R13").Value2)
End Sub

This is, of course, adapted to this specific case, so, for further use, you must ensure you update the sheet names and ranges(if they change). You also have to check by yourself that the areas are equivalent (e.g. 15x2 to 5x6 cells). These checks can be added in the procedure, but the code above should do the trick for the moment.

EDIT: I saw your specification a bit too late. :) Here is the adapted code, which should find the first available row on sheet "Foglio1", column A, and will paste the transposed values onto a 3x13 area. Give it a go.

Sub add2()
    With Sheets("Foglio1")
        .Cells(.Range("A" & .Rows.Count).End(xlUp).Row + 1, 1).Resize(3, 13).Value2 = _
        Application.WorksheetFunction.Transpose(Sheets("Training Analysis").Range("P1:R13").Value2)
    End With
End Sub

EDIT 2: updated add2 so that the source range would refer to sheet "Training Analysis" and prevent error# 1004.

VBA For Anything
  • 113
  • 8
  • Thank you for answering me i really appreciate that. I don't know if i'm wrong but i got a bug when i run this macro. Is seems something is wrong with the third line (.Cells.....). – AlbF Dec 04 '15 at 13:46
  • What message do you get for that line? – VBA For Anything Dec 04 '15 at 14:00
  • Just the entire cell highlighted in yellow. – AlbF Dec 04 '15 at 14:14
  • Odd, I don't get any error. A message should display on errors. What settings do you have for Error Trapping? I use "Break on All Errors". The setting is done via Menu: Tools » Options » Tab: General, Group: Error Trapping. With that setting, you can step into each line (pressing F8 from the first line to the last) and the error window should pop up. – VBA For Anything Dec 04 '15 at 14:26
  • ok i did it, when i run the code i got "range" of the object "_Global" failed – AlbF Dec 04 '15 at 15:43
  • You can follow the steps above for error trapping, then copy the whole 'add2' sub, place the cursor inside the sub and just press F5. Should there be an error, it should pop up. The message you mentioned shows up because you didn't end the subroutine with `End Sub`. – VBA For Anything Dec 04 '15 at 15:49
  • Ok i did the F5 check and it gives me the same error. I ended the subroutine with End Sub. – AlbF Dec 04 '15 at 16:04
  • I renamed my sheets to catch the error and, indeed, it throws error #1004, due to the source Range referring to an uninstantiated object. The second part of the assignment should read: `Application.WorksheetFunction.Transpose(Sheets("Training Analysis").Range("P1:R13").Value2)` This should work now. I have also edited my answer accordingly. – VBA For Anything Dec 04 '15 at 20:45