Complex VBA code

[booton]

Hello everyone!

I have a really complex (in my opinion) vba request if someone can please help me.

I have an Excel file with 4 sheets.

Input raw,
Input,
Discount Factors,
Upload.

From Input raw I need to take the data and put it into Input by year. To be more clear, for example I have data from 2020, 2019, 2018. first it needs to take the 2020 data and put it into input, after that delete it, then the 2019 and delete it and last 2018 and delete it.

Based on the rows that are copied from Input raw into Input there are some formulas in Discount Factors sheet. that is why i need the data copied year by year. When the data from one year only is in Input, in Discount Factors some cells needs to be copied into Upload sheet.

To summaries everything:
one year data only copied from Input raw into Input.
from Discount Factors some cells needs to be copied into Upload sheet
delete what is in Input sheet
copy next year data
and so on... until there are no more years...

Is it someone able to help me please? Is it possible to do this in VBA? I can share the excel :D

Thank you so much!!

 

[booton]

And where do you want the next years

if you can put them into a separate sheet. i don t need them from what i understood

 

[offthelip]

copy next year data

What do you mean by this??
Do you want the first year is
aa2 ab2 ac2 ad2
the second year in
aa3 ab3 ac3 ad3
etc ??

 

[booton]

What do you mean by this??
Do you want the first year is
aa2 ab2 ac2 ad2
the second year in
aa3 ab3 ac3 ad3
etc ??

aaa ok ok. i understand what you meant . the next years if it could be bellow. so 2020 is aa2 ab2 etc and 2019 aa3 ab3

 

[offthelip]

Here is the code, I will be surprised if it does exactly what you want, because your description of what you want is not very clear. The column names on the input raw and inptu sheet are different from column I onwards. the number of columns on the two sheets are different, . I am copying the data from A to S from Input raw to the input sheet.
Best of luck!!

Sub test()
With Worksheets("Input Raw")
 lastraw = .Cells(Rows.Count, "A").End(xlUp).Row
 rawdata = .Range(.Cells(1, 1), .Cells(lastraw, 19))     ' pick  all data from input raw workhseet
End With
With Worksheets("Input")
 Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
 .Range(.Cells(5, 1), .Cells(lastraw, 19)) = "" ' clear data from input sheet
 outarr = .Range(.Cells(1, 1), .Cells(lastraw, 19))     ' define an output array
indi = 5
thisyear = 0
  For i = 3 To lastraw
    tempyear = Year(rawdata(i, 5))
    If tempyear <> thisyear Or i = lastraw Then
       ' write output buffer to workhseet
       .Range(.Cells(1, 1), .Cells(lastraw, 19)) = outarr
      '  then copy to upload and clear data
       If thisyear > 0 Then
        ' copy data to upload
         lastup = Worksheets("Upload").Cells(Rows.Count, "AA").End(xlUp).Row + 1
         temp = Worksheets("Discount Factors").Range("E2:H2") ' copy vlaues
         Worksheets("Upload").Range(Cells(lastup, 27), Cells(lastup, 30)) = temp
       End If
       thisyear = tempyear
       .Range(.Cells(5, 1), .Cells(lastraw, 19)) = ""  ' clear data from input sheet
       outarr = .Range(.Cells(1, 1), .Cells(lastraw, 19))    ' reload the output array to clear it
       indi = 5
    Else
    ' copy a year to output sheet
      For j = 1 To 19
       outarr(indi, j) = rawdata(i, j)
      Next j
      indi = indi + 1
    End If
  Next i
End With
     
 End Sub