Automating template printing with variables

[Kamesh63]

I have an excel template which pulls in data from another excel file range using index formulas. A key field in the template (say F23) is used as a variable in the other excel file to pull the corresponding matching data.

My problem is sometimes I have 100 such records to print. Every time I have to change the value in F23, take print, change the value for the next record and print and so on...

I am exploring a way or a VBA code wherein a predefined range of records can be automatically printed using the template. Once I define the range, the F23 cell value should be automatically updated with the first value in the range, print, take the second value in the range, print, and so on till 100 records.

Any help is framing such a code is highly appreciated. Thanks in advance.

I thought of using mail merge with excel & word combination, but since the template has array formulas, it may not possible to use the similar formulas in Word.

 

[evanlamarr88]

I have this same problem, only I don't have the spreadsheet pull the data. I thought of that, but then I would have the same problem as above, which isn't much better.

The document I would like to 'mail merge', so-to-speak, is a shipping form given to me that I must use per the shipping department. I have several mailings that need to be shipped, and I don't want to enter by hand the address for each, especially since there's multiple rows that need to be entered for each mailing.

You can see the form attached. Somehow, I can't edit the form; just the data in certain fields. I can easily pull an excel sheet with the addresses, and I would like to be able to print one Shipping Form for each row on the sheet with the address information.

Does anyone have any idea if this is possible?

 

[vletm]

evanlamarr88
Could You make a sample data (5-10 rows)
which You would like to use with that Form?

 

[evanlamarr88]

@vletm sure! Here you go:

 

[vletm]

evanlamarr88
I thought that You have data to fill all needed - not just five informations.

'Data'-sheet:
Places is those 5 informations; NO empty rows!
You select by mouse needed names (yellow selected).
'Form'-sheet:
> Right side -
>> ReSet-button clears form
>> Forms-button shows selected rows from 'Data'-sheet
> Form-itself:
You can fill needed informations.
Carrier preferences, click needed.
>> If really need 'normal printout to printer', just ask to add that option.

 

[evanlamarr88]

Thanks @vletm. I've actually just tried to use this for the first time, but I'm not sure how to. When I enter the data I need in the 'Data' tab and hit the "Forms" button on the 'Form' tab, the sheet just scrolls through the rows about every 5 seconds, putting the values in the correct places on the form, but I can't print b/c there's a scrolling pointer the whole time it's populating the 'Data' values into the 'Form'.

 

[vletm]

evanlamarr88
... from August 2017
Did You noticed the last line?
>> If really need 'normal printout to printer', just ask to add that option.
If You have made ANY changes to that file then send it for me.

 

[vletm]

evanlamarr88
... okay, You haven't make ANY (even minor) changes to that file.
I added TWO buttons to 'Form'-sheet; Printer & PDF.
Click one or both to GREEN to get needed output.
All other instructions still match as written about 6 months ago.
( ... and instructions are for use ... )

 

[vletm]

evanlamarr88
I noticed that 'Printer' had some challenges ...
Now, it would work better
and
PDFs needs folder which name is PDF to same folder where is that file.
Those PDFs would save there.