VisualBasic - Change vertical to horizontal Data

[garwain]

Hello,

i´ve got a problem (sorry for my bad English ?)

There is an “input”-mask and a “list”. Data is entered manually into the mask, which is then copied into the list by makro. There are several Areas that are filled out in the “input”-mask and then output in the list in one line, one after the other.

The problem is: I changed an area in the mask so that it is easier to enter data.
Instead of entering the data vertically, they are now displayed horizontally in one line.
In the Makro there is the command: "Application.Transpose", which outputs the data that are entered vertically horizontally and vice versa.
Therefore, the third input area, which is already horizontal, is unfortunately entered vertically in the list.
I've tried a lot on the line, but I can't get this command out without an error message.

So I need a change in the code for "Daten3"

Hope u understand what I mean. Have a look in the code and the example file ?


My next Problem:
After copying, I want the value from column A of the "List", which is in the row of the last record, to be copied into the first sheet "Inputs" in cell B10.
Example: I have already transmitted 5x data, i.e. 5 filled lines in the list. The next entry is made in line 7, where column A contains the number "RE0005".
This number should now be copied directly after the copying process, with the same macro (i.e. without having used another button, etc.) in "Inputs" in field B10.
In other words: The code should do the following: "Find the last entry in" List "column B and copy the cell to the left of it (i.e. from column A) and paste it into" Input "cell B10"
Since I only have a rudimentary knowledge of VBA, it would be nice if someone could directly tinker with the line of code that I just have to insert.

Thanks in advance

Sub transfer_werte()

    Dim rngDaten1    As Excel.Range
    Dim rngDaten2    As Excel.Range
    Dim rngDaten32    As Excel.Range
    
    Set rngDaten1 = Worksheets("Eingaben").Range("B4:B9")
    Set rngDaten2 = Worksheets("Eingaben").Range("E4:E6")
    Set rngDaten3 = Worksheets("Eingaben").Range("A13:C13")
    
    With Worksheets("Liste")
          With .Cells(.Rows.Count, 2).End(xlUp)
               .Offset(1, 0).Resize(rngDaten1.Columns.Count, rngDaten1.Rows.Count).Value = Application.Transpose(rngDaten1.Value)
        End With
    End With
          
    With Worksheets("Liste")
          With .Cells(.Rows.Count, 8).End(xlUp)
               .Offset(1, 0).Resize(rngDaten2.Columns.Count, rngDaten2.Rows.Count).Value = Application.Transpose(rngDaten2.Value)
        End With
    End With

    With Worksheets("Liste")
          With .Cells(.Rows.Count, 11).End(xlUp)
               .Offset(1, 0).Resize(rngDaten3.Columns.Count, rngDaten3.Rows.Count).Value = Application.Transpose(rngDaten3.Value)
        End With
    End With


End Sub

 

[GWteB]

Welcome to MrExcel!
See if this works for you ...

Sub transfer_werte()

    Dim shtEingaben  As Excel.Worksheet
    Dim rngDaten1    As Excel.Range
    Dim rngDaten2    As Excel.Range
    Dim rngDaten3    As Excel.Range

    Set shtEingaben = ThisWorkbook.Worksheets("Eingaben")
    Set rngDaten1 = shtEingaben.Range("B4:B9")
    Set rngDaten2 = shtEingaben.Range("E4:E6")
    Set rngDaten3 = shtEingaben.Range("A13:C13")

    With Worksheets("Liste")
        With .Cells(.Rows.Count, "B").End(xlUp)
            .Offset(1, 0).Resize(rngDaten1.Columns.Count, rngDaten1.Rows.Count).Value = Application.Transpose(rngDaten1.Value)
            .Offset(1, 6).Resize(rngDaten2.Columns.Count, rngDaten2.Rows.Count).Value = Application.Transpose(rngDaten2.Value)
            .Offset(1, 9).Resize(rngDaten3.Rows.Count, rngDaten3.Columns.Count).Value = rngDaten3.Value
            shtEingaben.Range("B10").Value = .Offset(1, -1).Value
        End With
    End With
End Sub

 

[garwain]

THANK YOU VERY MUCH !!!!
YOU ARE MY HERO !!!

For 2 weeks I've been trying to solve the problem and in German forums you only get stupid answers, insults or very complex, cumbersome and illogical codes that don't work: D

 

[GWteB]

You are welcome and thanks for the feedback