abdo meghari
Active Member
- Joined
- Aug 3, 2021
- Messages
- 469
- Office Version
- 2019
Hi guys
as in the picture I have multiple textboxes on userform when I specific rows on userform should copy start from row2 and column A: G
so based on the pic 1
and when copy to sheet should be
but what I got this
and this is the code
columns count on userform are 7,rows are 11
every row on userform start textboxes number consecutively :
66,67,68,69,70,71,72
73,74,75,76,77,78,79
........
how fix this chaos when copy to sheet, and how copy to sheet based on fill multiple rows on userform please?
as in the picture I have multiple textboxes on userform when I specific rows on userform should copy start from row2 and column A: G
so based on the pic 1
and when copy to sheet should be
MM with corrected CBs v03 a.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | ITEM | CODE | BRAND | TYPE | ORIGIN | COST | SELL | ||
2 | 1 | BSJG580120020 | BS 1200R20 | G580 | JAP | 200 | 250.00 | ||
3 | 2 | BSTG580120020 | BS 1200R20 | G580 | THI | 120.00 | 230.00 | ||
4 | 3 | BSJVSJ140020 | BS 1400R20 | VSJ | JAP | 300.00 | 350.00 | ||
5 | |||||||||
BRANDS |
but what I got this
MM with corrected CBs v03 a.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | ITEM | CODE | BRAND | TYPE | ORIGIN | COST | SELL | ||||
2 | |||||||||||
3 | |||||||||||
4 | |||||||||||
5 | |||||||||||
6 | |||||||||||
7 | |||||||||||
8 | BSJG580120020 | BS 1200R20 | G580 | JAP | 200 | 250.00 | 2.00 | BSTG580120020 | |||
9 | 120 | 230 | 3 | BSJVSJ140020 | BS 1400R20 | VSJ | JAP | 300 | |||
10 | |||||||||||
BRANDS |
and this is the code
VBA Code:
Private Sub CommandButton1_Click()
Dim i As Long, j As Long
Dim LstRw As Long, CurrCon As String, ws As Worksheet
Set ws = sheet2
'find last used row
LstRw = ws.Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To 11 'loop userform rows
For j = 1 To 7 'loop userform columns
'recreate control name/number
CurrCon = "TextBox" & j + (11 * (i - 1))
'transfer value to sheet
ws.Cells(LstRw + i, j) = Me.Controls(CurrCon).Value
Next j
Next i
MsgBox " ok"
End Sub
columns count on userform are 7,rows are 11
every row on userform start textboxes number consecutively :
66,67,68,69,70,71,72
73,74,75,76,77,78,79
........
how fix this chaos when copy to sheet, and how copy to sheet based on fill multiple rows on userform please?