Heres the code I'm using. Very self explanatory. Only problem is that when
it has finished that first sheet, it doesnt move to the next sheet. not
sure why.


Sub Access()

For Each Sheet In Worksheets
Windows("Activebillings2004.xls").Activate

Dim RenamSheet As String

'here is where it copies assuming that workbook and sheet i have copied
is selected
Range("B26:M28").Select
Selection.Copy
Windows("Access.xls").Activate
'add the new sheet and rename it
Sheets.Add
RenamSheet = InputBox("Rename Sheet")
ActiveSheet.Name = RenamSheet
Range("C1").Select
'here is where it pastes that new info along with some other formatting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=True
Range("B1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "January"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
Range("B1:B12").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Annual Subscription Fees"
Range("A2").Select
Columns("A:A").EntireColumn.AutoFit
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
Range("A1:A12").Select
Range("A13").Select
ActiveCell.FormulaR1C1 = "Consultative Support"
Range("A13").Select
Selection.AutoFill Destination:=Range("A13:A24"), Type:=xlFillDefault
Range("A13:A24").Select
Range("A25").Select
ActiveCell.FormulaR1C1 = "Production"
Range("A25").Select
Selection.AutoFill Destination:=Range("A25:A36"), Type:=xlFillDefault
Range("A25:A36").Select
Range("B1:B12").Select
Selection.Copy
Range("B13").Select
ActiveSheet.Paste
Range("B25").Select
ActiveSheet.Paste
Range("D1:D12").Select
Application.CutCopyMode = False
Selection.Cut
Range("C13").Select
ActiveSheet.Paste
Range("E1:E12").Select
Selection.Cut
Range("C25").Select
ActiveSheet.Paste
Range("A1").Select
'then goes back to the original file
Windows("Activebillings2004.xls").Activate

Next Sheet

End Sub