Help with Macro Recorder Please

H

Hulk

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
 
H

HTv

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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help needed with borders 2
Hlp! Adv Filter - Non-Contiguous rows don't copy 11
Help with macro 1
Macro to sort, format, sum, a dynamic set of data 2
Why is Excel 2007 macro creating extra blank rows? 1
Formatting stop when reach blank cell 6
Run Time error 1004 - Pastespecial method of range class failed. 3
Change code help 0

Top