Creating Macros in Excel X for Mac

[Frank van Westerloo]

Hi all,

For some time now I'm busy trying to create an Excel workbook with Macros.

When an order is made, a line of cells (row) is filled with details about
the customer. I want to create a Macro which generates a bill by just
selecting the row and than clicking some Macro icon which generates the bill
in another sheet in the same workbook.

I know it's a very "open" question but It's for a non-profit organization.

I hope you can point me to the right direction.

Any help is much appreciated.

TIA!


Greets,


d33ch

 

[Jim Gordon MVP]

Hi Frank,

If it were me, I would use Excel to have the data arranged in rows and
columns. Then I would use Microsoft Word's data merge manager to create the
invoices using Excel as the data source.

-Jim

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[Frank van Westerloo]

Hi Jim,

Thanks for your reply.

The solution you provided is actually the situation as it is right now. We
have an order workbook in Excel en we merge this Document with a template in
Word. The problem is that it takes forever to merge them. So my idea is to
do this within Excel.

The reason why the 2 documents merge so slowly is probably because we use a
Windows XP file server. Another problem is that when you get the DATA in the
Word Template from the Excel sheet it becomes static which means that when I
make a Change in the Excel sheet saving it doesn't do it. I have to get the
DATA again in the DATA merge manager in Word. This is all to much work
especially because It's not one person who is assigned to do this Job.

I'd still appreciate any advice. TIA!

Greets,

d33ch

 

[JE McGimpsey]

I'd still appreciate any advice. TIA!

One way:

Set up Sheet2 as an invoice.

Assume five columns of data going into 5 cells in Sheet2:

Sheet1 Column Sheet2 Cell
============= ===========
A J1
B K12
C B14
D C14
E L14


Then use this macro:

Public Sub PrintInvoice()
Dim vArr As Variant
Dim i As Long
vArr = Array("J1", "K12", "B14", "C14", "L14")
With Selection.Cells(1)
For i = 1 To 5
Sheets("Sheet2").Range(vArr(i - 1)).Value = _
Cells(.Row, i).Value
Next i
End With
Sheets("Sheet2").PrintOut preview:=True
End Sub

 

[Frank van Westerloo]

One way:

Set up Sheet2 as an invoice.

Assume five columns of data going into 5 cells in Sheet2:

Sheet1 Column Sheet2 Cell
============= ===========
A J1
B K12
C B14
D C14
E L14


Then use this macro:

Public Sub PrintInvoice()
Dim vArr As Variant
Dim i As Long
vArr = Array("J1", "K12", "B14", "C14", "L14")
With Selection.Cells(1)
For i = 1 To 5
Sheets("Sheet2").Range(vArr(i - 1)).Value = _
Cells(.Row, i).Value
Next i
End With
Sheets("Sheet2").PrintOut preview:=True
End Sub

Thanks a million JE!!

That helped allot!

I think I can do the rest on my own.

Great help Thank you!

Greets,

d33ch

 

[Richard Garber]

I wonder if you couldn't solve this with lookup formulas from your
report template that refer to specific client info on the data sheet.
How much client info is on the data sheet - is it the entire client
history or only the info for that one bill? More than one client info
on the data sheet? Perhaps I am not understanding accurately?

Richard