VBA Issue help required please

stuart_sims

New member
Joined
Jul 22, 2015
Messages
9
Reaction score
0
Points
0
Hi All,

I have been asked by a colleague for some help with a task and I am finding it rather difficult and know someone will have the answer, I'd love for you to share it with me please.

The workbook contains the following setup

Sheet 1 which contains multiple rows of data (the amount of rows of data will vary), address, qty, phone number, special instructions etc

Sheet 2 is a pre created delivery note.

I want to be able to do the following:

Press the print button (print button has been added to sheet 2) and sheet 2 will populate various data from cells on sheet 1 to compile a delivery note and print 2 copies. Once printed I want the code to look at the next line to see if there is any data if so, populate the next row of information onto the delivery note and print 2 copies. I want this to repeat all the way down until no more data is found. The print button will print from the default printer.

Someone please help, its been about 8 years since I last used VBA and am seriously struggling?

I have an example of the file which I can either post to this thread or send directly for you to look at.

Thanks in advance.
 
Please post the workbook to the forum.

Below the quick reply pane, click go advanced, scroll down and click manage attachments, click add files to browse to your file.

Looks like somebody offered to help on another site but you didn't bother to post the file.
 
Last edited:
File uploaded, thanks in advance for help if you can provide it.
 

Attachments

  • Delivery Note Example.xlsm
    42.1 KB · Views: 7
First, you need to get rid of those merged cells, cosmetically they may look nice but they should be avoided like the plague.
Instead of Merge and Center, select the cells that would be merged, go to format cells and in Horizontal alignment use center across selection.

See if this helps you out.
View attachment Delivery Note Example.xlsm
 
Hi NoS

I understand your comments ref the merge and center function. I will test a little later on when I get to the office

thanks again for having a go at helping me out, I will let you know how I get on
 
Hi again all,

Just wanted to say thank you so much for your help and assistance it worked like a charm :clap2:

My colleague was overjoyed at the result and I promise I didn't take the credit, i told my colleague that this forum had helped me out

again thanks, I'm sure I'll be on here again after more help but in the meantime rest easy knowing you have made someone very happy
 
Hey, that's great, glad I could help.
Thanks for the feedback.
 
Hi Nos,

You helped me out with this a couple of weeks ago which was a great help and I am after some more advice if possible?

The file has been tweaked somewhat due to operational demands and now it doesn't appear to be working correctly.

When clicking the print button it now throws the data off all over the place or misses some data off completely, can you help me please?

I have reattached the file in hope you can check it out for me (I'm now on Version 5)

Here is hoping
 

Attachments

  • Delivery Note Test V5.xlsm
    28.2 KB · Views: 6
You've changed the headers and columns of your "Directs" sheet.
You need only adjust the column portion of .Offset(rows, columns) to get the values you want.

Assuming your new column A will have something in it on every row used, the For Each cel part of the macro can remain as is.
The values you want entered in the cells on "Delivery Note" are Offset from the A column.
The first number is the number of rows to offset. Zero is entered because we don't want any row offset, we want to use the same row.
So cel.Offset(0, 1) would be referring to same row, column B
cel.Offset(0, 2) would be same row, column C
cel.Offset(0, 3) would be same row, column D
etc., etc.
continuing this your Subbie would be cel.Offset(0, 16) which would be same row, column Q.
 

Attachments

  • Delivery Note Test V5_1.xlsm
    29.2 KB · Views: 7
Back
Top