Userform to populate data in multiple sheets

[lplexe]

Hello all,

I'm looking for a solution to a problem of populating data from Userform across different sheets based on what is stated in Userform. I have 52 weeks sheets that look exactly the same (see the picture), Userform that looks like this (pic here ) and a sheet that could work as reference tables sheet (one more picture - red table is a reference of a sheet a macro would open (so if someone inserts date 5.01.2021 it would open sheet named "Week 2"), green table is a reference of row number of each day and person (so if Mike would like to insert his data on 5.01.2021, macro knows it's Tuesday and it's Mike, so it choses row no 72 to populate), and blue table is a reference of columns numbers or letters (so macro would choose proper column to put data in).

Userform elements are:
Name - cmbImieiNazwisko
Date - cmbData
NDS - txtNDS
Over - TxtOver
Less - txtLess
CMP - txtCMP
RTN - txtRTN
Support NDS - txtSupportNDS
Support Matching - txtSupportMatching
Support Hrs - txtSupportHRS

I would like to avoid having to put these info in table and then transferring data to proper week sheets. The best option is just to copy and paste data from user form to proper cell in week sheets, so if someone would like to make correction, data would just be overwritten.

Thanks in advance for replies

 

[lplexe]

I have written a code, but it doesn't work. It throws subscript out of range error. Maybe someone could help on this

Sub PopSheets()



Dim Name As String

Dim Data As String

Dim dRow As Long

Dim nRow As Long

Dim sht As Worksheet

Dim day As String

Dim rNo As String

Dim wkday As Long

Dim NDScell As Range

Dim Overcell As Range

Dim Lesscell As Range

Dim CMPcell As Range

Dim RTNcell As Range

Dim SupportNDScell As Range

Dim SupportMatchingcell As Range

Dim SupportHRScell As Range

Dim NDS As String

Dim Over As String

Dim Less As String

Dim CMP As String

Dim RTN As String

Dim SupportNDS As String

Dim SupportMatching As String

Dim SupportHRS As String

Dim datesheet As Range

Dim namesheet As Range

Dim weekday As Range



datesheet = Sheets("Weeksdates").Range("C2:C366")

namesheet = Sheets("Weeksdates").Range("G2:G31")

weekday = Sheets("Weeksdates").Range("H1:N1")

Data = UserForm1.cmbData

Name = UserForm1.cmbImieiNazwisko

sht = Cells(dRow, 1)

day = Cells(dRow, 2)

dRow = Application.WorksheetFunction.Match(Data, datesheet, 0)

nRow = Application.WorksheetFunction.Match(Name, namesheet, 0)

wkday = Application.WorksheetFunction.Match(day, weekday, 0)



If wkday = 1 Then

rNo = Cells(nRow, 8).Value

ElseIf wkday = 2 Then

rNo = Cells(nRow, 9).Value

ElseIf wkday = 3 Then

rNo = Cells(nRow, 10).Value

ElseIf wkday = 4 Then

rNo = Cells(nRow, 11).Value

ElseIf wkday = 5 Then

rNo = Cells(nRow, 12).Value

ElseIf wkday = 6 Then

rNo = Cells(nRow, 13).Value

ElseIf wkday = 7 Then

rNo = Cells(nRow, 14).Value

End If



NDScell = Cells(rNo, 5)

Overcell = Cells(rNo, 6)

Lesscell = Cells(rNo, 7)

CMPcell = Cells(rNo, 12)

RTNcell = Cells(rNo, 13)

SupportNDScell = Cells(rNo, 16)

SupportMatchingcell = Cells(rNo, 17)

SupportHRScell = Cells(rNo, 18)

NDS = UserForm1.txtNDS

Over = UserForm1.txtOver

Less = UserForm1.txtLess

CMP = UserForm1.txtCMP

RTN = UserForm1.txtRTN

SupportNDS = UserForm1.txtSupportNDS

SupportMatching = UserForm1.txtSupportMatching

SupportHRS = UserForm1.txtSupportHRS



If NDS > 0 Then

Sheets(sht).Select

NDScell.Value = NDS

Overcell.Value = Over

Lesscell.Value = Less

CMPcell.Value = CMP

RTNcell.Value = RTN

SupportNDScell.Value = SupportNDS

SupportMatchingcell.Value = SupportMatching

SupportHRScell.Value = SupportHRS

End If



End Sub