Trouble with PasteSpecial Transpose

Question

I have written a macro to create a variable number of worksheets based on a list in worksheet "ProjList". Each sheet is named at the time of creation. I am trying to copy the values in some of the cells (Columns A-D) from "ProjList" to the new sheets. I've successfully done it with with a paste command, but I want the data transposed.

I have written:

Sub AddWorkSheets()
    Dim RowNumb As Long
    Dim LastRow As Integer
    LastRow = Worksheets("ProjList").Cells(Worksheets("ProjList").Rows.Count, "D").End(xlUp).Row

    For RowNumb = 2 To LastRow
        Sheets("ProjList").Activate
        Worksheets("ProjList").Range("A" & RowNumb, "D" & RowNumb).Copy
        Sheets.Add
        ActiveSheet.Name = Worksheets("ProjList").Cells(RowNumb, 4).Value
        ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Range("D1").PasteSpecial Paste:=xlPasteValues, transpose:=True
    Next RowNumb
End Sub

I get a

RunTime Error 1004: PasteSpecial method of range class failed.

The first worksheet gets created, but the macro fails at the PasteSpecial line.

Any help is much appreciated.
Thank you!

Just a note: I recommend never to use `Integer` unless you know why you have to. Instead [always use `Long`](https://stackoverflow.com/a/26409520/3219613) especially for row counts. Excel has more rows than `Integer` can handle! — Pᴇʜ, Dec 07 '17 at 07:21

Atik · Answer 1 · 2017-12-08T15:18:25.483

0

Try This:

ActiveSheet.Range("D1").Resize(, 4).PasteSpecial Paste:=xlPasteValues, transpose:=True

try this code bellow:

Sub AddWorkSheets()
    Dim RowNumb As Long
    Dim LastRow As Integer
    LastRow = Worksheets("ProjList").Cells(Worksheets("ProjList").Rows.Count, "D").End(xlUp).Row

    For RowNumb = 2 To LastRow
        Sheets("ProjList").Activate
        Sheets.Add
        ActiveSheet.Name = Worksheets("ProjList").Cells(RowNumb, 4).Value
        ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Range("D1").Resize(, 4) = Application.WorksheetFunction.Transpose(Worksheets("ProjList").Range("A" & RowNumb, "D" & RowNumb))
    Next RowNumb
End Sub

edited Dec 08 '17 at 15:18

answered Dec 07 '17 at 05:33

Atik

57
7

Hi Atik,I tried that, but got the same error: PasteSpecial method of Range class failed. – smRed Dec 07 '17 at 17:28
It did create the worksheet and it did select a range of four cells (D1:G1) but then doesn't successfully paste anything. – smRed Dec 07 '17 at 17:30
Hi Atik, thans so much for working on this. I put in the edited code and it ran the macro. It did create the four spreadsheets and named them correctly, but on the first new worksheet it placed a "1" in cells D1:G1; a "2 in cells D1:G1 on the second worksheet, etc. Somehow the transpose part isn't working, nor is it putting the info from "ProjList" into the new worksheets.... – smRed Dec 09 '17 at 04:22
Please add images of your sheets, Form where you like to copy and where to paste? – Atik Dec 09 '17 at 07:43
Sorry - have looked all over for how to attach an image and haven't been able to figure out how to do it. I've got screenshots ready to go... – smRed Dec 10 '17 at 04:16

Trouble with PasteSpecial Transpose

1 Answers1

Linked