Customer list to printable forms

selant

Board Regular
Joined
Mar 26, 2009
Messages
109
Dear friends,

I have a customer list (Sheet1).. I want this list to become in a printable shape of forms which will follow each other. The number of customers may vary each month.

Here is my customer list (Sheet1) :

list1v.jpg


Here is the automatic shape I want to have with a macro (Sheet2) :

list2.jpg



Thank you very much in advance for your helps..
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
is it possible with a basic for-next loop ? I dont know how to make the required cell borders
 
Upvote 0
It should be. Trying to think of something clever.

This would give you the borders, adding the names should be easy.
Code:
Sub printShapes()
Set sht = Worksheets("Sheet1")
lastRw = sht.Range("A" & Rows.Count).End(xlUp).Row
Set listRng = sht.Range("A2:A" & lastRw)

startRw = 2
For Each c In listRng

Worksheets("Sheet2").Range("B" & startRw).Resize(7, 5).BorderAround 1, 2, -4105, RGB(0, 0, 0)

startRw = startRw + 9
Next c
End Sub
 
Last edited:
Upvote 0
The border code works very nice! But i couldnt find out how to copy the required fields into the box within the for-next loop
 
Upvote 0
I assumed you did as you only mentioned borders.
If you want to further format the cells look at the example.
Something I'm curious about though, how come you sent me a PM?

Code:
Sub printShapes()
Set sht = Worksheets("Sheet1")
lastRw = sht.Range("A" & Rows.Count).End(xlUp).Row
Set listRng = sht.Range("A2:A" & lastRw)

startRw = 2
For Each c In listRng
With Worksheets("Sheet2").Range("B" & startRw)

.Offset(1, 1).Value = "Debt information"
.Offset(1, 1).Font.Bold = True

.Offset(3, 1).Value = "Customer"
.Offset(4, 1).Value = "Name"
.Offset(5, 1).Value = "DEBT"

.Offset(3, 2).Value = c
.Offset(4, 2).Value = c.Offset(0, 1)
.Offset(5, 2).Value = c.Offset(0, 2)

.Resize(7, 5).BorderAround 1, 2, -4105, RGB(0, 0, 0)

startRw = startRw + 9
End With
Next c
End Sub
 
Upvote 0
thank you very much.. it solved my problem.. i saw you online and click on your nick name then sent you a pm, i wish its not a problem for you :biggrin:
 
Upvote 0
thank you very much.. it solved my problem.. i saw you online and click on your nick name then sent you a pm, i wish its not a problem for you :biggrin:

I was thinking that it could have been that.
No it's not a problem, I'd just never thought of anyone doing that though.
I had to ask because I thought it was interesting. Never happened to me before. I'm studying how (to) use internet forums in the "best" way, so I was curious why you picked *me* from every other random person.
 
Upvote 0
is there a command in vba to put a "page cut" to the sheet when printing is required ? I dont want a form to be half at the end of the page
 
Upvote 0
I'm not sure. I can't find anything. I've seen someone do something like that based on how many lines they knew fitted their page size.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top