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.
25594

2559525596

[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.

25597

[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 :D. 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 :bow::bow::bow:
25611

[paulked]

Ha! You're welcome :thumb