| 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Macro Recorder Please
This was much code, .... but have you tried to use paste special, formats
from the first sheet to the nexts? Otherwise, if it is the same formatting on all sheets, you ca make a macro called formatting, and call it for each new sheet you go to. It's just to write the name of the callmacro after the sheet select sentence. HTv Hulk skrev: The following is a portion of my code. In this portion, I am formatting "Bob Smith's" worksheet and then moving onto the next worksheet (API). I am utilizing the formatting from Bob Smith's worksheet to format the API worksheet. I continue this formatting for about 20 more worksheets. One other thing I noticed is that I had to change the lines near the bottom of the subfunction code where the C,D, and E columns are deleted from what was generated by the macro recorder. When I would run the recorder, it would delete columns A - E instead of just C, D, and E. I changed the line to ".Columns("C:E").Delete Shift:=xlToLeft" and that fixed the problem. I had to do this to all of the other worksheets as 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 |
| Reply |
| 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 | |||
![Hulk[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
