Iterate Items & Print Appropriate Data

**hypnoticvampire** · 02-18-2011, 10:26 AM

I am trying to build macros for the attached file. I have no knowledge of creating macros thats why I am looking for little bit of help here.

The concept is to Print out one LOAD sheet for every TRUCK LOAD RUN in DISPATCH sheet:

In the attached excel file you will see two sheets-
sheet no. 1 - DISPATCH SHEET
sheet no. 2 - LOAD SHEEET

DISPATCH SHEET - This sheet has truck load runs with assosciated PROs and loading order

LOAD SHEET - This sheet is a load out manifest for the use of DOCK workers. This sheet will tell them how many loads are going into a particular truck.

The Goal is:

every TRUCK LOAD RUN ?( i.e. T/L#1 , T/L#2 , T/L#3 ..and so on) should create a seperate LOAD SHEET i.e.

T/L#1 SHOULD CREATE AND PRINT SEPARATE LOAD SHEET =LOAD SHEET 1

T/L#2 SHOULD CREATE AND PRINT SEPARATE LOAD SHEET = LOAD SHEET 2

and so on.

I hope this clears what I am trying to achieve. I would really appreciate your help on this matter.

**stnkynts** · 02-18-2011, 01:16 PM

here you go. For this to work YOU MUST UNMERGE the 3 cells in sheet "Load Sheet" where the T/L # is located. So for example T/L#1 is merged to be in cells A1:A3. Unmerge it so it is only in cell A1. This is a must.

Option Explicit

Sub test()
Dim ws1 As Worksheet:   Set ws1 = ThisWorkbook.Sheets("DISPATCH SHEET")
Dim ws2 As Worksheet:   Set ws2 = ThisWorkbook.Sheets("LOAD SHEET")
Dim icell As Long, lastrow1 As Long, lastrow2 As Long
Dim iFind As Range
Dim TLnumber As String

'sets the lastrow for the sheets
lastrow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
lastrow2 = ws2.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
On Error Resume Next
For icell = 4 To lastrow1
    If Not IsEmpty(ws1.Range("A" & icell)) Then
        TLnumber = ws1.Range("A" & icell).Value
        Set iFind = ws2.Range("A1", "A" & lastrow2).Find(TLnumber, LookIn:=xlValues, LookAt:=xlWhole)
        ws1.Range("B" & icell).Copy
        iFind.End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
        ws1.Range("E" & icell).Copy
        iFind.End(xlDown).Offset(0, 1).PasteSpecial xlPasteValues
        ws1.Range("I" & icell, "M" & icell).Copy
        iFind.End(xlDown).Offset(0, 2).PasteSpecial xlPasteValues
    Else
        'nothing
    End If
Next icell
Application.ScreenUpdating = True
Application.CutCopyMode = False

'Prints Pages
ws2.UsedRange.PrintOut

End Sub

I set it so that it will populate the loadout manifests accordingly. Each manifest occupies 1 page so the print out command should print one per page. Let me know.

Question to more experienced coders: Is there an easier way to identify a group of cells in a range when there is a variable involved? In my above code I had to do 3 copy/paste commands where i would have like to have done it in one command.

**hypnoticvampire** · 02-18-2011, 03:41 PM

Thank you so so much Bro, You have saved my life. I am really dull in programming. My background is business. My ahole boss has given me this project and he expect me to complete it by monday. Two more questions if you wont mind:

1. Did you try run this program? did it work for you bro?

2. As I have mentioned i have no knowledge of macros or excel a lot, would you tell me how to UNMERGE the 3 cells where the T/L#1 is located?

I thank you from the bottom of my heart for saving my job and i really appreciate your help Bro.

Best Regards

**DonkeyOte** · 02-18-2011, 03:55 PM

@hypnoticvampire, welcome to the board.

Please ensure your thread title accurately reflects the underlying problem.
Given first post etc I have modified for you on this occasion.

Originally Posted by stnkynts

Is there an easier way to identify a group of cells in a range when there is a variable involved? In my above code I had to do 3 copy/paste commands where i would have like to have done it in one command.

I've not looked at the code in depth but in relation to:

ws1.Range("B" & icell).Copy
iFind.End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
ws1.Range("E" & icell).Copy
iFind.End(xlDown).Offset(0, 1).PasteSpecial xlPasteValues
ws1.Range("I" & icell, "M" & icell).Copy
iFind.End(xlDown).Offset(0, 2).PasteSpecial xlPasteValues

Perhaps:

Intersect(ws1.Range("B:B,E:E,I:M"),ws1.Rows(iCell)).Copy
iFind.End(xlDown).Offset(1).PasteSpecial xlPasteValues

**hypnoticvampire** · 02-21-2011, 02:18 PM

Hi Bro,

Thank you so much for your help. I did a test run and its working absolutely fine. I thank you again for your help . I really appreciate it. I have one querry:- Lets say there are 10 T/L runs in the DISPATCH sheet:
T/L#1
T/L#2
T/L#3
T/L#4
T/L#5
T/L#6
T/L#7
T/L#8
T/L#9
T/L#10

right now what program is doing is printing out all the 10 LOAD sheets associate with the 10 T/L runs.

Lets say T/L#2 dont have the data to transfer to the load sheet. Is it possible that when we run the program , it will print only 9 LOAD sheets (because there is no data or value in T/L#2).

In short when there is an "EMPTY" T/L run, the program should skip that EMPTY T/L and jump to the next one and print the next one?

Thank you

Best Regards