Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro Recorder Please
The following is a portion of my code. In this portion, I am formattin "Bob Smith's" worksheet and then moving onto the next worksheet (API). I am utilizing the formatting from Bob Smith's worksheet to format th API worksheet. I continue this formatting for about 20 mor worksheets. One other thing I noticed is that I had to change the lines near th bottom of the subfunction code where the C,D, and E columns are delete from what was generated by the macro recorder. When I would run th recorder, it would delete columns A - E instead of just C, D, and E. changed the line to ".Columns("C:E").Delete Shift:=xlToLeft" and tha fixed the problem. I had to do this to all of the other worksheets a well. I appreciate any help. Sub Format3Bob() ' ' Format3Bob Macro ' Macro recorded 10/6/2004 by sholcomb ' ' Sheets("Bob Smith").Select Columns("G:G").Select Selection.Delete Shift:=xlToLeft Range("A6:F6").Select ActiveCell.FormulaR1C1 = "=Sheet1!R[1]C[7]" Columns("J:J").Select Selection.Delete Shift:=xlToLeft Range("K16").Select ActiveCell.FormulaR1C1 = "Subtotal" Range("K17").Select ActiveCell.FormulaR1C1 = "Adjustments" Range("K18").Select ActiveCell.FormulaR1C1 = "Grand Total" Range("K16:K18").Select Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("L16").Select Selection.Cut Range("N16").Select ActiveSheet.Paste Range("N16:N18").Select Selection.NumberFormat = "$#,##0_);[Red]($#,##0)" With Selection.Font .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 5 End With Range("N18").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)" Range("N16").Select Selection.Copy Range("N18").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone ActiveWindow.SmallScroll Down:=-32 End Sub Sub API() ' ' API Macro ' Macro recorded 10/7/2004 by sholcomb ' ' With Sheets("API") ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[7]" .Columns("C:E").Delete Shift:=xlToLeft .Range("I16:K16").ClearContents Range("L16").Select Selection.Cut Range("N16").Select ActiveSheet.Paste End With Sheets("Bob Smith").Select Range("K16:K18").Select Selection.Copy Sheets("API").Select Range("K16").Select ActiveSheet.Paste Sheets("Bob Smith").Select Range("N16:N18").Select Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy Sheets("API").Select Range("N16").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Range("N18").Select ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)" Range("A10:C101").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Range("A8:C8").Select ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[7]" Range("A9").Select ActiveWindow.ScrollColumn = 2 Range("O16").Select Range("N16").Select ActiveCell.FormulaR1C1 = "=SUM(R[-5]C[-11]:R[1610]C[-11])" Range("N17").Select ActiveWindow.SmallScroll Down:=-9 Range("L16").Select Selection.ClearContents ActiveWindow.SmallScroll ToRight:=-1 Range("B15").Select Selection.Copy Range("B17:B28").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Range("C15").Select Selection.Copy Range("C17:C28").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Range("C32").Select ActiveWindow.ScrollRow = 1 Columns("D:G").Select Selection.Delete Shift:=xlToLeft Range("G6").Select Sheets("Sheet1").Select Range("J4").Select Selection.ClearContents End Sub -- Hulk ------------------------------------------------------------------------ Hulk's Profile: http://www.excelforum.com/member.php...o&userid=14947 View this thread: http://www.excelforum.com/showthread...hreadid=268061 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Macro recorder | Excel Discussion (Misc queries) | |||
Help with Macro Recorder Please | Excel Programming | |||
Steps from Macro recorder for Pivot table will not run as a Macro | Excel Programming |