difficult for me, easy for others .. !?

S

Staalkop

Hi excel wizards,

I cannot get this thing working :

I have client data on one sheet and make a bill for them every month on
another sheet.
In the bill-sheet XL looks for necessary data in the client sheet and
makes a personal bill, with a unique billnumber.
This number exists of : "2006" followed by the clientnumer and the
number of the month.

A simple macro runs from 1 until the number of the last client and
prints every single unique bill.

So far so good.

But now I want also to save every bill, between the moment that the
macro makes it and prints it ( or after making and printing one bill,
but before making the next bill ; that does not matter of course ).

Preferably saving under the unique billnumber.....
and automatically ( I do not want to type the numbers ).

What command can I use in the macro to do so??????????

I hope my question is clear enough, because my english is not that good
( as my XL )

Thanks in advance,

Hans Bock
Holland
 
D

Dave Peterson

After you populate that other sheet, you could copy the sheet to a new workbook,
then convert the formulas to values, save that workbook using the unique bill
number, then close that new workbook.

This kind of thing in the middle of your larger macro:

Dim Wks as worksheet
dim rptWks as worksheet

set rptwks = worksheets("billingsheet") 'whatever

rptwks.copy 'to a new workbook
set wks = activesheet
with wks
with .usedrange
.copy
.pastespecial paste:=xlpastevalues
end with
.parent.saveas filename:="C:\myfiles\" & .range("a1").value & ".xls", _
fileformat:=xlworkbooknormal
.parent.close savechanges:=false
end with


Adjust names--worksheets and folder. And addresses, too. I used A1 to contain
the unique billing number.
 
S

Staalkop

Thanks Dave,
I have to study your solution, tomorrow,
but thanks for taking the time to help me,
Hans

Dave Peterson schreef:
 
Top