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..
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

selant

Board Regular
Joined
Mar 26, 2009
Messages
109
is it possible with a basic for-next loop ? I dont know how to make the required cell borders
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
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:

selant

Board Regular
Joined
Mar 26, 2009
Messages
109

ADVERTISEMENT

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
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
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
 

selant

Board Regular
Joined
Mar 26, 2009
Messages
109

ADVERTISEMENT

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:
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
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.
 

selant

Board Regular
Joined
Mar 26, 2009
Messages
109
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
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

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
Top