Vba code to copy data data from sh1 to sh 2

[a.ben]

Hi everyone ,

i am a first time visitor to this forum, i created a worksheet where i enter data using a userform . i managed to overcome many hurdles until now . the issue i have is how to copy the validated to sheet1 to sheet2 and save it for later reports.
for example when i validate this data to the sheet1
register number cell b27
part number cell b28
quantity cell c28
i want this data to be copied automatically to sheet2 without activating the sheet .
basically i want at the end of the validation process to see all these data stored in sheet 2
i tried different code but i went through some difficulties, either certain lines don't work or i don't get the result i want . and this is one of the codes i tried

Dim lastrowDB As Long, lastrow As Long
    Dim arr1, arr2, i As Integer
  
    With Sheets("DB")
        lastrowDB = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
  
    arr1 = Array("A1", "A2", "A3", "A4", "B10", "B12")
    arr2 = Array("A", "B", "C", "D", "E", "F")
  
    For i = LBound(arr1) To UBound(arr1)
        With Sheets("Data")
            lastrow = Application.Max(3, .Cells(.Rows.Count, Left(arr1(i), 1)).End(xlUp).Row)
           Sheets("DB").Range(arr2(i) & lastrowDB).Resize(lastrow - 2).Value = _
           .Range(Cells(3, arr1(i)), .Cells(lastrow, arr1(i))).Value
        End With
    Next
    Application.CutCopyMode = False
  
End Sub

i get the two bold lines highlighted in yellow

any help will be appreciated

thanks

 

[Marc L]

Hi !

Here is the Excel worksheet formulas forum !
Increase your chances by moving to the VBA Macros appropriate forum
and edit your post to use code tags !
​

 

[p45cal]

Dim lastrowDB As Long, lastrow As Long
    Dim arr1, arr2, i As Integer
 
    With Sheets("DB")
        lastrowDB = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
 
    arr1 = Array("A1", "A2", "A3", "A4", "B10", "B12")
    arr2 = Array("A", "B", "C", "D", "E", "F")
 
    For i = LBound(arr1) To UBound(arr1)
        With Sheets("Data")
            lastrow = Application.Max(3, .Cells(.Rows.Count, Left(arr1(i), 1)).End(xlUp).Row)
           Sheets("DB").Range(arr2(i) & lastrowDB).Resize(lastrow - 2).Value = _
           .Range(.cells(3, arr1(i)), .Cells(lastrow, arr1(i))).Value
        End With
    Next
    Application.CutCopyMode = False
 
End Sub

Could be the only thing wrong being the red full stop added above:
.Range(.cells(3, arr1(i)), .Cells(lastrow, arr1(i))).Value

 

[Deepak]

@a.ben
Marc have asked u to move this thred to appropriate place not to start a new one which have been deleted by me.
Pls take care of forum rules to help in a pleasant way...