Userform to populate data in multiple sheets

lplexe

New Member
Joined
Nov 17, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

lplexe

New Member
Joined
Nov 17, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
I have written a code, but it doesn't work. It throws subscript out of range error. Maybe someone could help on this

VBA Code:
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
 

Forum statistics

Threads
1,141,772
Messages
5,708,447
Members
421,570
Latest member
BaileyJ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top