I would like to create a macro that allows me to take data from one spreadsheet and populate various new sheets for billing purposes. For example, the data sheet includes fields for client name, billable account, billable value, etc. I would like to be able to fill in this data and then run a macro to create a new sheet (bill) for each client that plugs in values from the data sheet. I only know how to create a macro for one client at a time and then rename each sheet with the client short name (which is also a cell value on the sheet).
Here's the code, but I'm pretty certain it's a roundabout way of doing this:
Sheets("CLIENT TEMPLATE").Select
Sheets("CLIENT TEMPLATE").Copy
ActiveWorkbook.Colors = Workbooks("3rd Qtr 2006_1 - TEST.xls").Colors
Range("A2:H2").Select
ActiveCell.FormulaR1C1 = "='TEST DATA SHEET'!R[2]C[1]"
Range("F8").Select
ActiveCell.FormulaR1C1 = "='TEST DATA SHEET'!R[-4]C[-2]"
Range("F9").Select
ActiveCell.FormulaR1C1 = "='TEST DATA SHEET'!R[-5]C[-1]"
Range("C25").Select
ActiveCell.FormulaR1C1 = _
"=IF('TEST DATA SHEET'!R[-21]C[5]="""","""",'TEST DATA SHEET'!R[-21]C[5])"
Range("E25").Select
ActiveCell.FormulaR1C1 = _
"=IF('TEST DATA SHEET'!R[-21]C[4]="""","""",'TEST DATA SHEET'!R[-21]C[4])"
Range("F25").Select
ActiveCell.FormulaR1C1 = "='TEST DATA SHEET'!R[-21]C[1]"
Range("F29").Select
ActiveCell.FormulaR1C1 = "='TEST DATA SHEET'!R[-25]C[-3]"
Range("J1").Select
ActiveCell.FormulaR1C1 = "='TEST DATA SHEET'!R[3]C[-9]"
End Sub
Any help would be graetly appreciated. Thanks
Bookmarks