Help with autoprint vba module

[plainhavoc]

Hi all,

I've been reading through the forums quite a lot lately searching for a way to do an "autoprint" for labels from a set of data. Hopefully someone here can point me in the right direction.

I am attempting to populate a discontinuous set of cells on sheet1 "T_STICKERS" with a range (A2:L2), on sheet2 "PRINT_LIST".

Then it would save the excel file and export as PDF to the same location, then print out the sticker from sheet1, clear sheet1 data (with merged cells), move on to the next row of sheet2 and do the process again until a blank row on sheet2 is encountered.

The save, export, and clear submodules are all working with manually entered data, but I am just having trouble with the auto-populate. Just trying to save a couple hours per week at work instead of manually typing out or copy/pasting 50-100 of these stickers. Oh, the data on sheet2 can be up to 500 rows, but no more at a time. Here are some screenshots to show what I am trying to do.

The above is sheet1. Each merged cell is individually named as seen in quotations.

Above shows Sheet2 with two rows of data. Typically, it would be much more than this.
Below, you can see what work has been done in VBA so far. Many things are commented out for testing of other submodules, but it still helps to show.

To save myself further embarrassment, I didn't include the "PropogateForm" sub-module, which is where I was attempting the auto-populate.

 

[Michael M]

I'm going to be out for most of the day, but if you don'e get help, can you upload Sheet 1 and 2 to DropBox or similar and post the link to that site back here, and I'll have a look when I get back in !

 

[plainhavoc]

Thank you for any help you could provide. Here is a link to the file.

http://www.filedropper.com/trailerstickerexcel-rp-8-18-15-2013

<a href=http://www.filedropper.com/trailerstickerexcel-rp-8-18-15-2013><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >online backup storage</a></div>

 

[Michael M]

Ok, how am I supposed to help you if you have locked the file as read only ???

 

[Michael M]

Try this

Sub PropagateForm()
Dim ws1 As Worksheet, ws2 As Worksheet, r As Long, lr As Long
Set ws1 = Worksheets("T_STICKERS")
Set ws2 = Worksheets("PRINT_LIST")
lr = ws2.Cells(Rows.Count, "A").End(xlUp).Row
With ws1
For r = 2 To lr
Range("F3").Value = ws2.Range("A" & r).Value
Range("B5").Value = ws2.Range("B" & r).Value
Range("C14").Value = ws2.Range("C" & r).Value
Range("C16").Value = ws2.Range("D" & r).Value
Range("I16").Value = ws2.Range("E" & r).Value
Range("N16").Value = ws2.Range("F" & r).Value
Range("I18").Value = ws2.Range("G" & r).Value
Range("N18").Value = ws2.Range("H" & r).Value
Range("J20").Value = ws2.Range("I" & r).Value
Range("N20").Value = ws2.Range("J" & r).Value
Range("L14").Value = ws2.Range("K" & r).Value
Range("O14").Value = ws2.Range("L" & r).Value
Call PrintSave
Next r
End With
End Sub

 

[plainhavoc]

@ Michael, my apologies! I thought I had taken protection off. The password is "chave" or I can upload it again without protection if you wish.

Thank you so much for the above. With a bit of fixing of my previous subs, this worked flawlessly! My version had it populate each cell individually, and print. Needless to say I've been using a lot of toner and paper to test this. Thank you!!!

 

[Michael M]

Glad it worked...