Transpose columns to rows from 1 sheet to another sheet with VBA with ongoing columns

Question

I have a constantly-updating spreadsheet I use to track projects, but I want to create a summary view for internal stakeholders. I'm looking to transpose my columns to rows from a sheet named "Tasks" to the sheet named "Assessment Changes".

I tried to record a macro and this is what I recorded:

Sub TransposeColToRow()
    ' TransposeColToRow Macro
    Range("B3:B14").Select
    Selection.Copy
    Range("B20").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub

The error message says: Run-time error '1004': This selection isn't valid. make sure the copy and paste areas don't overlap unless they are the same size and shape.

What I have currently (new projects get added to as a new column, so a new project would then go into Column I):

What I want is rows 2-10 being transposed to the "Assessment Changes" sheet like this, so the new columns get transposed to new rows:

So in my example above, when I add a new project in my "Tasks" sheet, the project gets added to Column I. But when I run the macro I had recorded, the error message pops up and doesn't copy any new columns to rows.

.

I was thinking of adding a button and assigning a VBA to it so that every time you click it, it will update with the new columns and transpose again. But I don't know how to do that. I'm fairly new to VBA so your help is greatly appreciated.

Hi and welcome to S.O.. What have you tried so far and where is the problem, exactly? Seeing the code you currently have will make it a lot easier to help you — cybernetic.nomad, Oct 25 '19 at 21:05
FYI, it's usually better to [edit your question](https://stackoverflow.com/posts/58565667/edit) instead of adding information in comments. — cybernetic.nomad, Oct 25 '19 at 21:10
In your question you state: "...the transpose stops at the last column I had set it to..." what does that mean, exactly? Where did you set it? etc... [this may help you clarify your question](https://stackoverflow.com/help/how-to-ask) — cybernetic.nomad, Oct 25 '19 at 21:11
Thanks, @cybernetic.nomad. I've updated the question and hopefully, that's clearer. Sorry, it's my first time posting a question, so I apologize if it's not completely clear. — dgirl88, Oct 25 '19 at 21:24
So, in general, it's a good idea to [avoid using select in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) (I know, the recorder did it, not you, but it causes a lot of beginner to think you _have_ to select). Once you know how to manipulate ranges without selecting, you can [get the last row & column](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row) or the [UsedRange](https://learn.microsoft.com/en-us/office/vba/api/Excel.Worksheet.UsedRange) and from there, copy and the paste. — cybernetic.nomad, Oct 25 '19 at 21:36

score 0 · Answer 1 · answered Oct 25 '19 at 22:00

Here's one way to locate the last column to copy:

Dim lastCol As Long

With Sheets("Tasks")
    'find the last used column on row 3
    lastCol = .Cells(.Columns.Count, 3).End(xlToLeft).Column
    .Range(.Range("B3"), .Cells(14, lastCol)).Copy
End With

'paste and translose
Sheets("Assessment Changes").Range("B3").PasteSpecial Paste:=xlPasteAll, _
              Operation:=xlNone, SkipBlanks:=False, Transpose:=True

score 0 · Answer 2 · answered Oct 26 '19 at 10:22

as per your screenshots, you could try this:

With Sheets("Tasks").UsedRange
    .Resize(.Rows.Count - 1, .Columns.Count - 2).Offset(1, 2).Copy
End With

Sheets("Assessment Changes").Range("A6").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

score 0 · Answer 3 · answered Oct 28 '19 at 18:33

I tried this based on the above feedback and some googling help and it worked well:

Sub Transpose_columns_to_rows()
  Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, j As Long
  Set sh1 = Sheets("Tasks")  'origin
  Set sh2 = Sheets("Assessment Changes")  'destiny
  sh2.Range("A6", sh2.Cells(Rows.Count, Columns.Count)).ClearContents
  lr = 6
  For j = 3 To sh1.Cells(2, Columns.Count).End(xlToLeft).Column
    sh2.Range("A" & lr).Resize(1, 10).Value = Application.Transpose(sh1.Cells(2, j).Resize(10).Value)
    lr = lr + 1
  Next
End Sub

Thanks so much for all your help!

Transpose columns to rows from 1 sheet to another sheet with VBA with ongoing columns

3 Answers3