Printout VBA

[baxius]

Hello,

I've been trying to create a decent inventory labeling VBA, but couldn't do it. The problem i have is , that i don't know how to code that an offset value would be inserted in another worksheet and printed out. To put it simply, i attached some screens shots and the Workbook

In the first one (Sheet1) is a made up label for a box. X is the box code, and Y is when it arrived.

And the second one (Sheet2) is the box registry sheet. Where when a box code is entered, a macro timestamps it, uses the scanned to determine the expiry and so on.

The thing i would like to achieve is when i enter a Code in T column the adjacent value in column H would appear in sheet1 location X+"Code" and adjacent value from column I is entered in location Y +"Reg.d." and then the E10:E14 cells are printed out creating a label.

I'd really appreciate your help or tips on how to achieve something like this. Thank you for any tips and help.
Printout macro.xlsm

Untitled2.jpgUntitled1.jpg

[paulked]

I would do it like the attached, with this code in Sheet2 code module:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 20 Then
        Sheet1.Range("E10") = Cells(Target.Row, 1) 'Registry
        Sheet1.Range("F11") = Cells(Target.Row, 20) 'Code
        Sheet1.Range("F12") = Cells(Target.Row, 9) 'Registered
        Sheet1.Range("I12") = Cells(Target.Row, 2) 'Opened
        Sheet1.Range("F13") = Cells(Target.Row, 3) 'Analysed
        Sheet1.Range("I13") = Cells(Target.Row, 4) 'Expiry
        Sheet1.Range("G14") = Cells(Target.Row, 10) 'Registered by
    End If
End Sub

Changing the red numbers to suit the columns the data is in.

Printout macro ked.xlsm

[baxius]

Didn't realize it was this easy. Works on multiple sheets as well. Now just have to adapt the Sheet1 Label to our Inventory standards . Also added a PrintOut line.

Private Sub Worksheet_Change(ByVal Target As Range)    
If Target.Column = 20 Then
        Sheet1.Range("F11") = Cells(Target.Row, 8) 'Code
        Sheet1.Range("F12") = Cells(Target.Row, 9) 'Registered
        Sheet1.Range("E10:J14").PrintOut Copies:=1, Collate:=True
    End If
End Sub

Cheers to you Paulked
qjY2GGs.jpg

[paulked]

Ha! You're welcome