Solved: merge and print with offset

[mperrah]

I have a form I'm merging the data to from a list to print a seperate page for each row of data.
The code you gave me before scans the target id, uses Vlookup to change the data in row 2 which is mapped to the cells of the form to print.
Rather then scanning the target id, can it scan for anything in the column?
I have another form that scans for the offset but I am not sure how to pull the parts I need together
I have an attachment with the code for both subs on the page called "code"
instead of workbook print in the right side code, I need the row merge and print from the left code
Lucas and Jimmy have helped with both these jobs
Let me know what you find.
Thank you : pray2:
Mark

[mperrah]

I'm very new to this but here is an attempt of combining two files that work seperately but not so well together

one file makes a userform to check worksheets you want to print
the second maps data from one sheet to another then prints

I'm getting debug errors
or the user form that should have a list of files to print is empty?

Does anyone have suggestions?:doh:

I appreciate the all awesome help I have found here.
Mark

[lucas]

I think this is a link to the original thread....correct me if I'm wrong mperrah.
http://www.vbaexpress.com/forum/showthread.php?t=10760

Looks like the reason the listbox isn't working is because you moved it to a different workbook or changed the layout. The rowsource property for the listbox is for sheet data(which doesn't exist) and column K:K.

In the vbe click on the listbox and then look at the properties for the listbox. If the properties box is not showing on the lower left side of the vbe then go to view- and click on properties window to show it. Then look down the list of properties for rowsource. I have changed it in your example to techs!C:C techs is the sheetname and C:C is the column. I don't know if you wanted to use C or not but I did this so you would understand.

There are probably other problems but thats a start for you. As you have more questions post them here. Try to figure it out if you can but if you get stumped post your question and be as specific as you can. Tell us exactly what the error is that you receive.

[mperrah]

Lucas,
I'm lost. I hope to put an X in column A of the "tech" page for each tech I need the route sheet form printed. Then click the command button "print" to take all the "x" marked rows and fill the target cells on the "template" mapping the tech name, tech number and radio number then print (no preview needed)

[mperrah]

That is print one page for each row of data marked with an "x" mapped into the form on "template" page.

[mperrah]

I'm trying to work in this code to no avail.

Private Sub CommandButton1_Click()
On Error Resume Next
Unload frmDelete
Application.DisplayAlerts = False
a = ListBox1.ListCount
For X = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(X) = True Then
shtname = ListBox1.List(X)
ActiveWorkbook.Sheets(shtname).PrintPreview ' out
ActiveWorkbook.Sheets(shtname).Range("D3").Value = Workbook.Sheets(List).Range("G8").Value
End If
Next
Application.DisplayAlerts = True
End Sub

the part in red is the date I need printed on the page and it sometimes gets changed before printing

This form makes a list box based on the seperate tabs, I hilight the tabs I want to porint then hit the command button on the form but I print a lot of the same pages each day and rather than checking each tab daily I am trying to make a target range and source range that changes one template that is blank with the info from a range that can have an x in column A of the row of data to be sourced into the target

This is the rest of the button codes:

Private Sub CommandButton2_Click()
Unload Me
End Sub


Private Sub UserForm_Initialize()
Dim wk As Worksheet
For Each wk In ActiveWorkbook.Worksheets
ListBox1.AddItem wk.Name
Next
End Sub

I have a file that prints from a checked list in a range but it is workbooks in a directory not a column of data in a range

This is that code:

Sub PrintFiles()
Dim cel As Range
Dim wb As Workbook
For Each cel In Range(Cells(3, 6), Cells(Rows.Count, 1).End(xlUp))
If cel.Offset(, 1) <> "" Then
Set wb = Workbooks.Open(cel)
wb.Sheets(1).Range("D3") = Now() + "1"
wb.Sheets(1).PrintPreview
wb.Close False
End If
Next
End Sub

Any way to out these together to print only checked rows of data as source and change the single target page and print looping through the checked rows until there is a seperate page printed for every checked row?

Any suggestions?:dunno

[lucas]

I'm very new to this but here is an attempt of combining two files that work seperately but not so well together

one file makes a userform to check worksheets you want to print
the second maps data from one sheet to another then prints

I'm getting debug errors
or the user form that should have a list of files to print is empty?

Does anyone have suggestions?:doh:

I appreciate the all awesome help I have found here.
Mark

This is where I am lost.....why are you combining two methods of basically doing the same thing?....
You should decide which method you wish to use and abandon the other....or you will have these kinds of problems in my opinion. I may be missing something and if I am please point it out but I just don't understand what you want to do. You have combined code and renamed sheets and it's no wonder it doesn't work....

start over with exactly what you wish to do...be detailed and we will try to help.

[mperrah]

okay,
I'd like to have one workbook with 2 sheets

sheet one is labeled "techs" it is a list of techs with name, id and radio #
with a blank column "A" to have a check entered if a tech is working tomorrow and needs a route sheet printed with their info on it

sheet 2 is a route sheet with the tech name, id and radio # blank and a space for the route date (which can be from the last column on sheet 1)

somewhere on sheet 1 can be a button to run the script that scans for a any data in column "A" on sheet 1 and if it has data the source info is sent to the tagret form on sheet 2 then printed, and loops through all the checked rows in column "A" till a route sheet has been printed for each tech.

I have a form that scans and prints and i have one that uses source and target
but as you can see I'm struggling

thanks in advance

Mark

[mperrah]

I was hoping to get away from the userform
I like putting an "x" in column "A" so if tomorrows techs are all still working
i can just print the same "x"'d rows with the new date
instead of re-checking all techs in the userform
also if I have one page with all the techs as the source
it is easier to update then making a new workbook or adding a new sheet

[lucas]

Ok mperrah,
I think I follow now....give me some time the next few days if no one comes along first....we'll see what we can do with this.

[mperrah]

awesome!
Mark

[mperrah]

Lucas
Here is an example attached with all the vb removed.
I'll let the expert work the magic
sheet 1 (Techs)
sheet 2 (Blank)
the format is pretty much what I'm trying to make work
the number of techs may grow or shrink and the date changes
but the route sheet form will stay the same

also the date on the form is usually the next day
i use =today() + "1" on sheet 1
when I had the tabs use a named reference on sheet 1 the other sheets never updated. I put the date in the vb to update on print and it worked but sometimes a tech calls in sick and I call another tech in to work today and need to use todays date. If I had a cell to put in the day I want printed I can change daily or as needed
would be great (I put this on sheet 1)
I look forward to seeing what you workout.

Thank you, thank you, thank you... :friends:
Mark
padawan coder

[mperrah]

I kept searching and found a link in contextures forum.
I tweaked the code and found a workable solution.
Another link added a cool title screen that solutes the maker.
Attached is what I came up with.

The file scans for an "X" in column "A" on the "Techs" tab.
It then maps the data in each "x"'d row to the blank "Routesheet" tab and prints it.

Any one looking to print data from a list and stick the data into a form then print, this will help.

Thanks to Lucas, sorry I got it before you.

Mark :whistle:

[lucas]

Hi Mark,
Really glad to hear you found a solution. Thanks for posting it for others.

[mperrah]

My user got tired of typing an "x" to select rows to print.
So I found a method using a checkbox value to place an x in the column
I made the font for the column white so the user doesnt see it,
just the check box.
I revised the print "if" code to skip blanks and print if value is "x"
instead of lokking for anything nonblank.
I have attached the final (is it ever final?) version
Mark

[lucas]

Hi Mark,
I looked it over and it looks great and works perfectly...nice job.

[lucas]

Hi Mark,
I changed your file a little...instead of using the checkboxes which add to the size of the file and are hard to maintain you might try this....it's sheet change code that fires when you click in the A column(where your checkboxes were) it puts a marlett font small letter a which is a checkmark...see what you think. Also had to change the code to look for a small letter a...

[mperrah]

Thank you Lucas,
This is far and above way better than I hoped for.
You are the man!

sub ThankYou()
dim ThanYou as alot
for i = 1 to infinty
ThankYou = Thankyou + i
next i

never end sub

Mark :laugh2:

[lucas]

Your welcome Mark. Glad this project worked out so nicely.