VBA Or Macro: To format data in a column and delete columns and create new column

[aacod]

I get an excel sheet with data from Column A1:L200 daily.

I need a macro to perform the following on it.

1. Format columns I and K to "Date" e.g 03/12/13. Current format in columns I & K is date & time. 03/12/2013 10:43:00 AM
2. Delete columns B, C, D, F and G.
3. Adjust column A to width 15 and all other columns to fit data (wrap Text).
4. Create a new extra column before existing column A, width 8.
5. Put border from A1 to cells under Column G (Number of data in cells varies every day from G1:G200). e.g. data one day may be present from A1:G35, next day may be from A1:G80 etc.
6. Print SELECTION in landscape view.

Thanks.

aacod

[enrand22]

i can help you... but it is not easy task to create a whole macro without a workbook, and you should try the macro recorder too.

use the macro recorder, try to understand how it works and then post it here, we can reorder it to work better

[aacod]

Here it is:




Sub Macro2()
'
' Macro2 Macro
'
'
Range("I:I,K:K").Select
Range("K1").Activate
Selection.NumberFormat
=
"mm/dd/yy;@"
Range("B:B,C:C,D:D,F:F,G:G").Select
Range("G1").Activate
Selection.Delete
Shift:=xlToLeft
Columns("A:A").Select
Selection.Insert Shift:=xlToRight,
CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth =
7
Columns("B:B").ColumnWidth =
15
Columns("D:D").Select
Columns("C:C").ColumnWidth =
12.43
Selection.ColumnWidth = 6.14
Columns("E:E").ColumnWidth =
10.29
Columns("F:F").ColumnWidth = 10.71
Columns("G:G").ColumnWidth =
12
Columns("H:H").ColumnWidth = 28.29
Range("A2:H16").Select
With
Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment =
xlBottom
.WrapText = False
.Orientation = 0
.AddIndent =
False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder =
xlContext
.MergeCells = False
End
With
Selection.Borders(xlDiagonalDown).LineStyle =
xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With
Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex =
0
.TintAndShade = 0
.Weight = xlThin
End With
With
Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex =
0
.TintAndShade = 0
.Weight = xlThin
End With
With
Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex =
0
.TintAndShade = 0
.Weight = xlThin
End With
With
Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex =
0
.TintAndShade = 0
.Weight = xlThin
End With
With
Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex
= 0
.TintAndShade = 0
.Weight = xlThin
End With
With
Selection.Borders(xlInsideHorizontal)
.LineStyle =
xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End
With
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader =
""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter =
""
.RightFooter = ""
.LeftMargin =
Application.InchesToPoints(0.7)
.RightMargin =
Application.InchesToPoints(0.7)
.TopMargin =
Application.InchesToPoints(0.75)
.BottomMargin =
Application.InchesToPoints(0.75)
.HeaderMargin =
Application.InchesToPoints(0.3)
.FooterMargin =
Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines =
False
.PrintComments = xlPrintNoComments
.PrintQuality =
600
.CenterHorizontally = False
.CenterVertically = False
.Orientation
= xlLandscape
.Draft = False
.PaperSize =
xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order =
xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors =
xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter =
False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter =
True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text =
""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text =
""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text =
""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text =
""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text =
""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text =
""
.FirstPage.RightFooter.Text = ""
End
With
ActiveWindow.SelectedSheets.PrintPreview
Selection.PrintOut
Copies:=1
Sheets("SystemStatus").Select
End Sub

[SamT]

Aacod,

That is good, but since this is a learning experience for you, it would be great if you would record several macros, each doing only one thing. That will make it much easier for you to understand as we edit short macros for you.

You can name a macro before you record it. Try to give them descriptive names. For instance record a macro named "FormatIK" and record when you format Columns "I:K" the way you want, then Stop Recording.

Record a macro named "DeleteBG" and delete those columns. Stop Recording.

Record macros named

FormatIK
DeleteBG
InsertBeforeA
ColumnWidthsBH
FormatAH
printPageAnd just do what the macro is named for with each macro you record. I think you will find it much easier to understand what is going on this way.


Go ahead and delete the "macro2" you recorded and then just post all of the new ones in one post. We will take it from there.

[enrand22]

that is exactly the way almost 90% of us learn macros, the problem is that excel macro recorder creates lot of rubish, so the first spot is this one:

Range("I:I,K:K").Select
Range("K1").Activate
Selection.NumberFormat

it could be reordered this way:

range("I:I,K:K").numberformat

and that is the first tip.