Hi All

I am extracting data from a sql database and this is working some wha
okay.

I then have to format and carry out certain functions on the data, suc
as making a certain column format have 2 decimal places.

The way I have done it is to record a macro while doing what I want
Every month I have to run a query against a sql database and dump th
data into excel , I then have to run the macro to carry out th
formating and function. It is sooooo hard because I constantly have t
update the macro to include the new month and the whole point of thi
exercise was to automate this process.

I was wondering if there was not a better way of doing it. This is th
code I got from the macro:



Code
-------------------
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2004/07/08 by ZEN6000
'

'
Range("C3:W373").Select
Selection.Replace What:="", Replacement:="0.00", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("C:C,F:F,I:I,L:L,O:O,R:R,U:U").Select
Selection.NumberFormat = "0"
Range("W:W,T:T,Q:Q,N:N,K:K,H:H,E:E").Select
Selection.NumberFormat = "#,##0.00"
Range("D2").Select
ActiveCell.FormulaR1C1 = "% Of Total"
Range("D2").Select
Selection.Copy
Range("G2,J2,M2,P2,S2,V2").Select
ActiveSheet.Paste
Range("D3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(SUMIF(C[-3],RC[-3],C[-1])0,RC[-1]/SUMIF(C[-3],RC[-3],C[-1]),0)"
Range("D3").Select
Selection.Copy
Range("G3,J3,M3,P3,S3,V3").Select
ActiveSheet.Paste
Range("D:D,G:G,J:J,M:M,P:P,S:S,V:V").Select
Application.CutCopyMode = False
Selection.NumberFormat = "0%"
Range("D3").Select
Selection.AutoFill Destination:=Range("D3:D373"), Type:=xlFillDefault
Range("G3").Select
ActiveCell.FormulaR1C1 = _
"=IF(SUMIF(C[-6],RC[-6],C[-1])0,RC[-1]/SUMIF(C[-6],RC[-6],C[-1]),0)"
Selection.AutoFill Destination:=Range("G3:G373"), Type:=xlFillDefault
Range("J3").Select
ActiveCell.FormulaR1C1 = _
"=IF(SUMIF(C[-9],RC[-9],C[-1])0,RC[-1]/SUMIF(C[-9],RC[-9],C[-1]),0)"
Selection.AutoFill Destination:=Range("J3:J373"), Type:=xlFillDefault
Range("M3").Select
ActiveCell.FormulaR1C1 = _
"=IF(SUMIF(C[-12],RC[-12],C[-1])0,RC[-1]/SUMIF(C[-12],RC[-12],C[-1]),0)"
Selection.AutoFill Destination:=Range("M3:M373"), Type:=xlFillDefault
Range("P3").Select
ActiveCell.FormulaR1C1 = _
"=IF(SUMIF(C[-15],RC[-15],C[-1])0,RC[-1]/SUMIF(C[-15],RC[-15],C[-1]),0)"
Selection.AutoFill Destination:=Range("P3:P373"), Type:=xlFillDefault
Range("S3").Select
ActiveCell.FormulaR1C1 = _
"=IF(SUMIF(C[-18],RC[-18],C[-1])0,RC[-1]/SUMIF(C[-18],RC[-18],C[-1]),0)"
Selection.AutoFill Destination:=Range("S3:S373"), Type:=xlFillDefault
Range("V3").Select
ActiveCell.FormulaR1C1 = _
"=IF(SUMIF(C[-21],RC[-21],C[-1])0,RC[-1]/SUMIF(C[-21],RC[-21],C[-1]),0)"
Selection.AutoFill Destination:=Range("V3:V373"), Type:=xlFillDefault
End Sub

Range("B2").Select
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 6
End With
Range("A2").Select

-------------------


This code is so long and tedious to work thru especially when I use i
with Visual Basic 6. I would really appreciate a better way of doin
this

Thanx

Kind Regard

--
Message posted from http://www.ExcelForum.com