0

I have written this code, which only works, if I use Select on ws2. Else it only works if I am already on the sheet I want to paste the data to. I dimed and set both worksheets, but somehow it is not enough. Once I insert ws2.Select as seen in my code, it works just fine. Any ideas why? Here is the code:

Sub TranspNewLine()

Dim i As Long, j As Long, n As Long, m As Long, c As Long, p As Long, ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("Tabelle1")
Set ws2 = Worksheets("Tabelle2")

ws2.Rows.Clear

n = ws1.Range(ws1.Cells(1, 1), ws1.Cells(ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row, 1)).Rows.Count

For i = 1 To n
    If i = 1 Then
        m = ws1.Range(ws1.Cells(i, 2), ws1.Cells(i, ws1.Cells(i, ws1.Columns.Count).End(xlToLeft).Column)).Columns.Count
        ws1.Cells(i, 1).Copy
        ws2.Select
        ws2.Range(Cells(i, 1), Cells(m, 1)).PasteSpecial xlPasteValues
        c = i
        For j = 2 To m + 1
            ws1.Cells(i, j).Copy
            ws2.Cells(c, 2).PasteSpecial xlPasteValues
            c = c + 1
        Next j
    Else
        p = ws2.Range(ws2.Cells(1, 2), ws2.Cells(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row, 2)).Rows.Count
        m = ws1.Range(ws1.Cells(i, 2), ws1.Cells(i, ws1.Cells(i, ws1.Columns.Count).End(xlToLeft).Column)).Columns.Count
        ws1.Cells(i, 1).Copy
        ws2.Range(Cells(p + 1, 1), Cells(p + m, 1)).PasteSpecial xlPasteValues
        c = p + 1
        For j = 2 To m + 1
            ws1.Cells(i, j).Copy
            ws2.Cells(c, 2).PasteSpecial xlPasteValues
            c = c + 1
        Next j
    End If
Next i
Application.CutCopyMode = False
    
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250

0 Answers0