|
こんなのでは?
Option Explicit
Private wksList As Worksheet
Private vntPos As Variant
Private lngRow As Long
Private Sub userform_Initialize()
'「"確認表"の元データーは色々なセルに散乱しています」
'のセル位置を順番に列挙
vntPos = Array("K5", "D7")
Set wksList = Worksheets("伝票一覧")
With wksList
'伝票一覧の最終行を取得
lngRow = .Range("A65536").End(xlUp).Row
'最終行が3未満なら
If lngRow < 3 Then
lngRow = 3
'最終行が3以上なら+1
Else
lngRow = lngRow + 1
End If
End With
End Sub
Private Sub UserForm_Terminate()
Set wksList = Nothing
End Sub
Private Sub OKBtn_Click()
Dim i As Long
With Worksheets("確認表")
'データ位置全てに就いて繰り返し
For i = 0 To UBound(vntPos)
'データ位置のデータを順番に転記
wksList.Cells(lngRow, i + 1).Value _
= .Range(vntPos(i)).Value
Next i
End With
'書き込み行を更新
lngRow = lngRow + 1
End Sub
|
|