spreadsheet has outgrown the macro!

[Guest]

This macro has out grown it's self and I AM AGAIN STRUGGLING!
Firstly I have columns up to BN but when I go and insert another column
between columns I get an error message - any ideas

also This macro which has been copied to create many reports from the data,
which works great, but I can't get it to copy some of the end columns. I
can't see any reason why. I will paste a copy of one macro below.

Sub MAB_Rollout_Schedule()

Dim wbPRP As Workbook
Dim wbNew As Workbook
Dim wsCopy As Worksheet
Dim wsPaste As Worksheet

Set wbPRP = Workbooks("PRP Rollout Schedule.xls")
Set wsCopy = wbPRP.Worksheets("House List")
Set wbNew = Workbooks.Add
Set wsPaste = wbNew.Worksheets(1)

wbPRP.Save
With wsCopy
.AutoFilterMode = False
.Range("B6").CurrentRegion.Copy _
Destination:=wsPaste.Range("B6")
End With

wsPaste.Range("F:I,Q:S,V:W,Y:Z,AG:AH,AQ:AP,AW:BQ,BY:BZ,CE:CZ").Delete
ActiveWorkbook.SaveAs Filename:= _
"C:\MAB_SCHEDULE FOLDER\MAB SCHEDULE.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
Windows("PRP Rollout Schedule.xls").Activate
Range("A1:N5").Select
Selection.Copy
Windows("MAB SCHEDULE.xls").Activate
Range("B1").Select
ActiveSheet.Paste
Range("K2:K3").Select
Application.CutCopyMode = False
Selection.Cut Destination:=Range("F2:F3")
Range("J5").Select
ActiveWorkbook.Save
wbPRP.Activate
With wsCopy
.Activate
.Range("B6").Select
.Range("B6").AutoFilter
End With
Windows("MAB SCHEDULE.xls").Activate
ActiveWindow.DisplayGridlines = False
Range("B7:AF1927").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="Active"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("A1").Select
ActiveWorkbook.Save
End Sub

If you have any idea please let me know. I am also going to post this as a
new question incase you are not available to help

Sean...

 

[Guest]

- what's your error message?
- go to the last column you THINK is your last column and delete all columns
after that such as BO to IV
- go to the last row you THINK is your last row and delete all rows under that
- save your workbook
- exit your workbook
- open your workbook
- try your macros again

HTH,