0

So, i have an problem with excel VBA. I have an input sheet and report sheet. In data sheet i have table, with names, dates, and counts. I need to transfer that to other sheet. But i need names, each in new row, and the same data, which is typed in input sheet at all names in columns. I have already made everything for one row, but im stuck there. Code so far : 

Sub Ievades_poga()

Dim NewRow As Integer

NewRow = Worksheets("Ievade").Range("D7").Value + 1

If Worksheets("Ievade").Range("C7").Value <> 0 Then

MsgBox "Kļūda!", vbOKOnly, "123"

Exit Sub
End If

Worksheets("Lentzāģis").Cells(NewRow, 1).Value = Worksheets("Ievade").Range("B3").Value
Worksheets("Lentzāģis").Cells(NewRow, 2).Value = Worksheets("Ievade").Range("B4").Value
Worksheets("Lentzāģis").Cells(NewRow, 3).Value = Worksheets("Ievade").Range("B5").Value
Worksheets("Lentzāģis").Cells(NewRow, 4).Value = Worksheets("Ievade").Range("B6").Value
Worksheets("Lentzāģis").Cells(NewRow, 5).Value = Worksheets("Ievade").Range("B7").Value
Worksheets("Lentzāģis").Cells(NewRow, 6).Value = Worksheets("Ievade").Range("B8").Value
Worksheets("Lentzāģis").Cells(NewRow, 8).Value = Worksheets("Ievade").Range("B9").Value
Worksheets("Lentzāģis").Cells(NewRow, 9).Value = Worksheets("Ievade").Range("B10").Value
Worksheets("Lentzāģis").Cells(NewRow, 10).Value = Worksheets("Ievade").Range("B11").Value
Worksheets("Lentzāģis").Cells(NewRow, 11).Value = Worksheets("Ievade").Range("B12").Value
Worksheets("Lentzāģis").Cells(NewRow, 12).Value = Worksheets("Ievade").Range("B13").Value
Worksheets("Lentzāģis").Cells(NewRow, 13).Value = Worksheets("Ievade").Range("B14").Value
Worksheets("Lentzāģis").Cells(NewRow, 14).Value = Worksheets("Ievade").Range("B15").Value
Worksheets("Lentzāģis").Cells(NewRow, 15).Value = Worksheets("Ievade").Range("B16").Value
Worksheets("Lentzāģis").Cells(NewRow, 16).Value = Worksheets("Ievade").Range("B17").Value
Worksheets("Lentzāģis").Cells(NewRow, 17).Value = Worksheets("Ievade").Range("B18").Value
Worksheets("Lentzāģis").Cells(NewRow, 18).Value = Worksheets("Ievade").Range("B19").Value
Worksheets("Lentzāģis").Cells(NewRow, 19).Value = Worksheets("Ievade").Range("B20").Value
Worksheets("Lentzāģis").Cells(NewRow, 20).Value = Worksheets("Ievade").Range("B21").Value
Worksheets("Lentzāģis").Cells(NewRow, 21).Value = Worksheets("Ievade").Range("B22").Value
Worksheets("Lentzāģis").Cells(NewRow, 22).Value = Worksheets("Ievade").Range("B23").Value
Worksheets("Lentzāģis").Cells(NewRow, 23).Value = Worksheets("Ievade").Range("B24").Value
Worksheets("Lentzāģis").Cells(NewRow, 24).Value = Worksheets("Ievade").Range("B25").Value
Worksheets("Lentzāģis").Cells(NewRow, 25).Value = Worksheets("Ievade").Range("B26").Value
Worksheets("Lentzāģis").Cells(NewRow, 26).Value = Worksheets("Ievade").Range("B27").Value
Worksheets("Lentzāģis").Cells(NewRow, 27).Value = Worksheets("Ievade").Range("B28").Value
Worksheets("Lentzāģis").Cells(NewRow, 28).Value = Worksheets("Ievade").Range("B29").Value
Worksheets("Lentzāģis").Cells(NewRow, 29).Value = Worksheets("Ievade").Range("B30").Value
Worksheets("Lentzāģis").Cells(NewRow, 30).Value = Worksheets("Ievade").Range("B31").Value
Worksheets("Lentzāģis").Cells(NewRow, 31).Value = Worksheets("Ievade").Range("B32").Value
Worksheets("Lentzāģis").Cells(NewRow, 32).Value = Worksheets("Ievade").Range("B33").Value
Worksheets("Lentzāģis").Cells(NewRow, 33).Value = Worksheets("Ievade").Range("B34").Value
Worksheets("Lentzāģis").Cells(NewRow, 34).Value = Worksheets("Ievade").Range("B35").Value
Worksheets("Lentzāģis").Cells(NewRow, 35).Value = Worksheets("Ievade").Range("B36").Value
Worksheets("Lentzāģis").Cells(NewRow, 36).Value = Worksheets("Ievade").Range("B37").Value
Worksheets("Lentzāģis").Cells(NewRow, 37).Value = Worksheets("Ievade").Range("B38").Value
Worksheets("Lentzāģis").Cells(NewRow, 38).Value = Worksheets("Ievade").Range("B39").Value
Worksheets("Lentzāģis").Cells(NewRow, 39).Value = Worksheets("Ievade").Range("B40").Value


MsgBox "Dati pievienoti!", vbOKOnly, "123"

Worksheets("Ievade").Range("B3").ClearContents

Worksheets("Ievade").Range("D7").Value = NewRow

Worksheets("Ievade").Range("B3").Select
End Sub

My input table is like this:

Name1   | Jhon
Name2   | Don
Name3   | Arthur
Name4   | Andrew
Date    | 19.06.2017
Number  | 435
Number  | 274
Number  | 814

And the report sheet should look like this:

Jhon    | 19.06.2017  | 435  | 274  | 814  |
Don     | 19.06.2017  | 435  | 274  | 814  |
Arthur  | 19.06.2017  | 435  | 274  | 814  |
Andrew  | 19.06.2017  | 435  | 274  | 814  |

Can anyone please help me?

Improve this question
4
  • 4
    You should read about loops and Transpose. Commented Jun 19, 2017 at 10:19
  • 3
    I see no example of your data or desired output. That makes it difficult to help you. Please edit your original question so as to provide a Minimal, Complete, and Verifiable example Commented Jun 19, 2017 at 10:24
  • Can you have more than one date per worksheet, and if yes, how are those supposed to be handled? Also, can you have multiple groups of names/dates/numbers; and if yes, how do you know which names are associated with which dates and which numbers? Commented Jun 19, 2017 at 11:25
  • Yes. I need to type every date there. So i need a function, that type in next data in new row, so the last data will be not overwrited. Each time i type in the names and data, i press confirm, and data will go to report sheet. Next time there will be other names, other dates and other numbers, that should go belove existing ones. Commented Jun 19, 2017 at 11:33

1 Answer 1

Reset to default
1

The code would to be like this

Sub Ievades_poga()

Dim NewRow As Integer

NewRow = Worksheets("Ievade").Range("D7").Value + 1

If Worksheets("Ievade").Range("C7").Value <> 0 Then

MsgBox "K??da!", vbOKOnly, "123"

Exit Sub
End If

    Dim vDB
   vDB = Worksheets("Ievade").Range("B3").Resize(38)
   Worksheets("Lentzāģis").Cells(NewRow, 1).Resize(1, 38) = WorksheetFunction.Transpose(vDB)

MsgBox "Dati pievienoti!", vbOKOnly, "123"

Worksheets("Ievade").Range("B3").ClearContents

Worksheets("Ievade").Range("D7").Value = NewRow

Worksheets("Ievade").Range("B3").Select
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.